Using sp_executesql to obtain output parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using sp_executesql to obtain output parameter

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-14 : 07:47:39

I have the following code:

begin tran
create table test1 (id int identity, ref char(1) check (ref=1))
create table test2 (id int identity, ref char(1) check (ref=2))

insert into test1
select '1'
union all 
select '1'
go
create view test
as
select * from test1
union all 
select * from test2
go
--select max(id) from test

declare	  @id		int 
		, @sql		nvarchar(max)
		, @params	nvarchar(max)
		, @col		varchar(100)
		, @tbl		varchar(100)
		, @column	varchar(100)
		, @table	varchar(100)
		, @output	int

select @column = 'id'
	 , @table = 'test'

set @params = '@output INT output'

set @sql = 'select @output = max('+@column+') from '+@table

select @sql
exec sp_executesql @sql,@params,  @output = @id

rollback tran

I want to get the same results as the query 'select max(id) from test', but I have to build the sql dynamically, as I don't know the table or column name until run time.

So far, no success. Can anyone help??????

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 08:19:59

did you check books online? it has a good example

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-14 : 08:24:07

Thanks visakh16

I had a look, and realised that I left out the OUTPUT statement:

exec sp_executesql @sql,@params, @output = @id OUTPUT

Much obliged

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 08:26:07

And to avoid the possibility of SQL injection, enclose all user supplied values with proper termination.
In this case the QUOTENAME function.

DECLARE @SQL NVARCHAR(4000),
	@TableName SYSNAME,
	@ColumnName SYSNAME,
        @max INT

SELECT	@TableName = 'Test',
	@ColumnName = 'ID'

SET     @SQL = 'SET @a = MAX(' + QUOTENAME(@ColumnName)+ ') FROM ' + QUOTENAME(@TableName)

EXEC    sp_executesql @SQL, N'@a INT OUT', @a = @max OUT

SELECT	@max

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 08:26:09

welcome