Help with Dynamic SQL Query....

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)

Help with Dynamic SQL Query....

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-02-18 : 12:34:15

I can't seem to fix this query, can anyone help me out?

' Select tsv2.CoSym, tsv2.CoName, tsv2.AcctMgr, tsv2.ChkHO, tsv2.GrpName, ' +
' tsv2.Service, tsv2.FirstYear, tsv2.RptLvl, ' +
-- ' ''YM10'' = sum(case when year(tsv2.Billdate) = ''1999'' then tsv2.volume else 0 end), ' +
' ''YM10'' = sum(case when year(tsv2.Billdate) = 1999 then tsv2.volume else 0 end), ' +
' ''YM9'' = sum(case when year(tsv2.Billdate) = 2000 then tsv2.volume else 0 end), ' +
' ''YM8'' = sum(case when year(tsv2.Billdate) = 2001 then tsv2.volume else 0 end), ' +
' ''YM7'' = sum(case when year(tsv2.Billdate) = 2002 then tsv2.volume else 0 end), ' +
' ''YM6'' = sum(case when year(tsv2.Billdate) = 2003 then tsv2.volume else 0 end), ' +
' ''YM5'' = sum(case when year(tsv2.Billdate) = 2004 then tsv2.volume else 0 end), ' +
' ''YM4'' = sum(case when year(tsv2.Billdate) = 2005 then tsv2.volume else 0 end), ' +
' ''YM3'' = sum(case when year(tsv2.Billdate) = 2006 then tsv2.volume else 0 end), ' +
' ''YM2'' = sum(case when year(tsv2.Billdate) = 2007 then tsv2.volume else 0 end), ' +
' ''YM1'' = sum(case when year(tsv2.Billdate) = 2008 then tsv2.volume else 0 end), ' +
-- ' ''MaxDate'' = ' + @MaxDate + ', ' +
' ''MaxDate'' = ''' + @MaxDate + ''' , ' +
' ''MaxYr'' = ' + @MaxYear + ' ' +
' from #tmpsv2 tsv2 ' +
' where tsv2.FirstYear <> '' '' ' +
' group by tsv2.CoSym, tsv2.CoName, tsv2.AcctMgr, tsv2.ChkHO, tsv2.GrpName, ' +
' tsv2.Service, tsv2.FirstYear, tsv2.RptLvl ' +
' order by tsv2.cosym '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:39:25

one problem you've is you're using temp table inside dynamic sql. it will be out of scope when you execute dynamic sql and will throw error. you can however use global temporary tables (##tables) inside dynamic sql, but you need to consider whether concurrent execution takes place before using it.

asgast
Posting Yak Master

149 Posts

Posted - 2009-02-19 : 03:22:01

I will not agree with you visakh16 here

try running the following code:
DECLARE @n nvarchar(1000)
CREATE TABLE #test(a varchar(4))

SET @n = 'INSERT #test(a)
SELECT ''TEST'''
EXEC sp_executesql @n

SELECT * FROM #test
DROP TABLE #test

usually you can insert/select with #tables that were created before running dynamic sql, however its if you create a temp table in dynamic sql, then after it fires you will not be able to use this table.

About the query in question, what error are you getting?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-19 : 06:12:28

Asgast is right.

If you create the temp table before executing the dynamic SQL you can reference it.

qman -- this is a horrible way to do this and I don't see any point to using dynamic sql. You haven't indicated what is wrong any error message. You don't need to concatenate lines like this you can put carriage returns inside strings.

This is a horrible unreadable mess to debug. For instance


DECLARE @foo NVARCHAR(MAX)
DECLARE @maxDate NVARCHAR(100)
DECLARE @maxYear NVARCHAR(100)

SET @maxDate = '01/01'
SET @maxyear = '2009'
SET @foo = 
' Select tsv2.CoSym, tsv2.CoName, tsv2.AcctMgr, tsv2.ChkHO, tsv2.GrpName, ' +
' tsv2.Service, tsv2.FirstYear, tsv2.RptLvl, ' + 
-- ' ''YM10'' = sum(case when year(tsv2.Billdate) = ''1999'' then tsv2.volume else 0 end), ' +
' ''YM10'' = sum(case when year(tsv2.Billdate) = 1999 then tsv2.volume else 0 end), ' +
' ''YM9'' = sum(case when year(tsv2.Billdate) = 2000 then tsv2.volume else 0 end), ' +
' ''YM8'' = sum(case when year(tsv2.Billdate) = 2001 then tsv2.volume else 0 end), ' +
' ''YM7'' = sum(case when year(tsv2.Billdate) = 2002 then tsv2.volume else 0 end), ' +
' ''YM6'' = sum(case when year(tsv2.Billdate) = 2003 then tsv2.volume else 0 end), ' +
' ''YM5'' = sum(case when year(tsv2.Billdate) = 2004 then tsv2.volume else 0 end), ' +
' ''YM4'' = sum(case when year(tsv2.Billdate) = 2005 then tsv2.volume else 0 end), ' +
' ''YM3'' = sum(case when year(tsv2.Billdate) = 2006 then tsv2.volume else 0 end), ' +
' ''YM2'' = sum(case when year(tsv2.Billdate) = 2007 then tsv2.volume else 0 end), ' +
' ''YM1'' = sum(case when year(tsv2.Billdate) = 2008 then tsv2.volume else 0 end), ' +
-- ' ''MaxDate'' = ' + @MaxDate + ', ' + 
' ''MaxDate'' = ''' + @MaxDate + ''' , ' + 
' ''MaxYr'' = ' + @MaxYear + ' ' + 
' from #tmpsv2 tsv2 ' +
' where tsv2.FirstYear <> '' '' ' +
' group by tsv2.CoSym, tsv2.CoName, tsv2.AcctMgr, tsv2.ChkHO, tsv2.GrpName, ' +
' tsv2.Service, tsv2.FirstYear, tsv2.RptLvl ' +
' order by tsv2.cosym '

PRINT @foo

Results in:


 Select tsv2.CoSym, tsv2.CoName, tsv2.AcctMgr, tsv2.ChkHO, tsv2.GrpName,  tsv2.Service, tsv2.FirstYear, tsv2.RptLvl,  'YM10' = sum(case when year(tsv2.Billdate) = 1999 then tsv2.volume else 0 end),  'YM9' = sum(case when year(tsv2.Billdate) = 2000 then tsv2.volume else 0 end),  'YM8' = sum(case when year(tsv2.Billdate) = 2001 then tsv2.volume else 0 end),  'YM7' = sum(case when year(tsv2.Billdate) = 2002 then tsv2.volume else 0 end),  'YM6' = sum(case when year(tsv2.Billdate) = 2003 then tsv2.volume else 0 end),  'YM5' = sum(case when year(tsv2.Billdate) = 2004 then tsv2.volume else 0 end),  'YM4' = sum(case when year(tsv2.Billdate) = 2005 then tsv2.volume else 0 end),  'YM3' = sum(case when year(tsv2.Billdate) = 2006 then tsv2.volume else 0 end),  'YM2' = sum(case when year(tsv2.Billdate) = 2007 then tsv2.volume else 0 end),  'YM1' = sum(case when year(tsv2.Billdate) = 2008 then tsv2.volume else 0 end),  'MaxDate' = '01/01' ,  'MaxYr' = 2009  from #tmpsv2 tsv2  where tsv2.FirstYear <> ' '  group by tsv2.CoSym, tsv2.CoName, tsv2.AcctMgr, tsv2.ChkHO, tsv2.GrpName,  tsv2.Service, tsv2.FirstYear, tsv2.RptLvl  order by tsv2.cosym

I'm not going to waste my time trying to debug this. Why don't you format it better.

Better yet tell us what you are trying to do -- I see no need to use dynamic SQL here.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources