Unique Problem ... Plz Help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Unique Problem ... Plz Help

Author

Topic

zcwolf
Starting Member

2 Posts

Posted - 2014-10-15 : 06:14:24

I am trying to create a memory table and the definition of the table depends on extract of another query. so the number of columns required to be created in the new table depends on the select of a previous select.
Following is the Query :
Problem : the query executes fine but the end query "exec (@table_string)" dosent declare the table, hence I am unable to use it in the following queries ....

---
declare @dates table ([date] varchar(20))
declare @table_string as varchar(1000)

insert into @dates
SELECT DISTINCT cast(left(attempt_date, 11) as datetime) AS [Date]
FROM allocation where attempt_date is not null and Campaign_status = 'open'
ORDER BY cast(left(attempt_date, 11) as datetime) asc

DECLARE @cols varchar(1000)
SELECT @cols = COALESCE(@cols + ',[' + left([Date],11) +']','[' + left([Date],11) + ']') FROM @Dates

DECLARE @cols2 varchar(1000)
SELECT @cols2 = COALESCE(@cols2 + ',[' + left([Date],11) +'] varchar(50)','[' + left([Date],11) + '] varchar(50)') FROM @Dates

set @table_string = 'declare @resultant table (campaign varchar(500), team_leader varchar(100), username varchar(100),' + @cols2
set @table_string = @table_string +')'

print @table_string

exec (@table_string)
select * from @resultant table --- This query says "Must declare the table variable "@table_string"

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 08:55:55

Dynamic SQL executes in a separate scope, so you can't do what you want this way. The EXEC() begins a new scope, defines the table, exits and returns to the calling scope, but anything not persistent in the dynamic sql (including table variables and temporary tables) are disposed of when the scope ends.

This is easy to check:


declare @sql nvarchar(4000) = 'create table #t (a int)' 
exec (@sql)
select * from #t

-- try it with table variable

declare @sql nvarchar(4000) = 'declare @t table (a int)' 
exec (@sql)
select * from @t

should both fail but


declare @sql nvarchar(4000) = 'create table t (a int)'
exec (@sql)
select * from t

should work

zcwolf
Starting Member

2 Posts

Posted - 2014-10-15 : 09:21:36

Thanks for the help, I dident knew that EXEC() creates a new scope and then flushes every thing out. so in my case its declaring the table however the declaration is of no use for my purpose coze the scope ends with the EXEC(). the only thing I dident wanted to do was to create a permanent table.

what my actual goal is to create a view for workload done by a team, on a daily basis, I have a column worked_date which I want to be displayed horizontaly, as columns, and users vertically and in the middle the workload they did, with the pivort query. now the only problem is that the number of columns will keep on increasing on a daily basis. the other simple solution is that I create a permanent table, and add a new column on a daily basis and updated the user counts in it... any other solutions ??

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 10:31:40

You can still use dynamic sql. first you build up a variable containing the list of columns:

[code]
declare @collist nvarchar(4000)
set @collist = (select stuff(collist, 1, 1, '') from
(
select ',' + quotename([name of column containing column names for the dynamic query])
from [table containing the column]
for xml path('')) q(collist)
)

----e.g.
declare @collist nvarchar(4000)
set @collist = (select STUFF(collist,1,1,'') from (
select ','+ QUOTENAME(colname) from (values ('a'),('b'),('c')) v(colname)
for xml path('')) v(collist))
select @collist
]

Then, use that column list to construct the actual query, which can be a PIVOT if that's what you need.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-15 : 12:00:56

Use a temp table. A temp table created in the main code can be referenced by dynamic SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources