求一动态sql文返回值的写法
求一动态sql文返回值的写法
获取传进来表名里OPENDATE（日期型）列不为空的第一条数据
create PROCEDURE GetData
@tableName varchar(100)
BEGIN
declare @strSql nvarchar(1000),
declare @compareDate date
set @strSql='select @returnDate=top 1 OPENDATE from' + @tableName + ' where OPENDATE is not null'
exec sp_executesql @strSql N'@returnDate date output',@compareDate output
select @compareDate
END
GO
如果列名是动态的如何写啊
[解决办法]
create PROCEDURE GetData
@tableName varchar(100),
@columnName VARCHAR(100) --添加一个变量即可
as
BEGIN
declare @strSql nvarchar(1000)
declare @compareDate date
set @strSql='select @returnDate=top 1 '+@columnName+' from' + @tableName + ' where OPENDATE is not null' --注意这里的修改
exec sp_executesql @strSql N'@returnDate date output',@compareDate output
select @compareDate
END
GO
CREATE TABLE TEST(OPENDATE DATE)
INSERT TEST SELECT NULL UNION ALL SELECT '2013-08-08'
CREATE PROCEDURE GetData
@tableName varchar(100)
AS
BEGIN
declare @strSql nvarchar(1000)
declare @compareDate date
set @strSql=N'select top(1) @returnDate=OPENDATE from ' + @tableName + ' where OPENDATE is not null'
exec sp_executesql @strSql, N'@returnDate date output',@compareDate OUTPUT
select @compareDate
END
GO
