求一动态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