Dynamic select into @variable *solved*

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)

Dynamic select into @variable *solved*

Author

Topic

fossie
Starting Member

4 Posts

Posted - 2008-08-01 : 05:16:17

Greetings.

I'm creating a procedure for a dynamical pivot query. I wouldn't be doing this at all if I could specify the pivot-table to create columns of all the distinct values in the pivot group column (following the FOR statement in the PIVOT query), called '@idvar' henceforth, so if this can be done directly stop reading right now and please post how to do it :)

If not, my plan was to create the sql statement dynamically in a procedure. I would do this by fetching the distinct values of @idvar, store them in a variable @id, and then parse them into @sql containing the full pivot query.

My problem is getting the distinct values of @idvar from a select statement on the table - which also needs to be dynamical (@table).

-set @id = ( ... )- seems to work fine if I hardcode the query, but does not work well if the query is dynamical. Using sp_executesql doesn't seem to help. The sql string is returned in both cases.

I would greatly appreciate it if you have any input on how to make this work. (See simplified example below)

Cheers,
Steinar

-- EXAMPLE START
drop table #temp
go
create table #temp (id varchar(50))
go
insert into #temp values('id3')
insert into #temp values('id2')
insert into #temp values('id4')
insert into #temp values('id1')
go

drop proc piv
go
create proc piv(@idvar varchar(50), @table varchar(50))
as
declare @id varchar(5000), @sql nvarchar(max)
set @sql = 'select top 1 ' + @idvar + ' from ' + @table
print 'sql-query: ' + @sql

-- Plain dynamic query does not work
set @id = (@sql)
print 'id (dynamic): ' + @id

-- sp_executesql doesn't work either
exec sp_executesql
@query = @sql,
@params = N'@id varchar(50) output',
@id = @id output
print 'id sp_executesql: ' + @id

--This works (query pasted from outputed print @sql above):
set @id = (select top 1 id from #temp)
print 'id (hardcoded): ' + @id
go

exec piv 'id', '#temp'
-- EXAMPLE END

fossie
Starting Member

4 Posts

Posted - 2008-08-01 : 05:31:49

...and as usual, once you post you see your own errors.

I failed to specify '@id=' in @sql, and thus sp_executesql didn't work. I changed it to

set @sql = 'select top 1 @id=' + @idvar + ' from ' + @table

... and voilá.

I hope this helps someone else some day :)

Cheers,
Steinar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-01 : 06:22:21

Also make sure you read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources