HELP on Dynamic query execution

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

Transact-SQL (2000)

HELP on Dynamic query execution

Author

Topic

snrani
Starting Member

7 Posts

Posted - 2005-09-07 : 08:55:16

Hi,

I need to execute a query dynamically and store the results in the table for further processing. Attached is the piece of code to do the same. Can anyone please tell me what is wrong with the code and why is it not giving me the desired results.

Thanks in advance.

declare @QueryString nvarchar(2000)
declare @WhereClause varchar(250)

Select SaleCategory,SaleDesc,SaleLiveDate,SaleNo,SaleStatus,SaleType,CAST(1 AS bigint) AS AuditID,AuthStatus,
Identity(int,1,1) As zzz into #SaleAudit
from SaleAudit Where 0 = 1

set @WhereClause = ' ctrycode = 1'

Set @QueryString = 'Select SaleCategory,SaleDesc,SaleLiveDate,SaleNo,SaleStatus,SaleType,AuditID,AuthStatus
into #SaleAudit from SaleAudit(nolock) Where Authstatus is null and Status = ''D'' and ' + @WhereClause

print @QueryString
insert into #SaleAudit
exec sp_executeSQL @QueryString --total results are stored in #AccountAudit

Select * from #SaleAudit(nolock)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-07 : 09:11:28

the temp table #SaleAudit is not accessible from a exec(@dynamicSQL) statement.

You either need to make it a global temp.. or a perminant table (and then drop it at the end)

Or! If you really wanted to do it right, you'd figure out how not to use dynamic SQL

...

for example

Declare @ctryCode int

and (@ctryCode is null or ctryCode = @ctryCode)

works for:

Set @ctryCode = 1
And
Set @ctryCode = null

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

snrani
Starting Member

7 Posts

Posted - 2005-09-08 : 03:33:23

[quote]Originally posted by Seventhnight

the temp table #SaleAudit is not accessible from a exec(@dynamicSQL) statement.

You either need to make it a global temp.. or a perminant table (and then drop it at the end)

Or! If you really wanted to do it right, you'd figure out how not to use dynamic SQL

...

for example

Declare @ctryCode int

and (@ctryCode is null or ctryCode = @ctryCode)

works for:

Set @ctryCode = 1
And
Set @ctryCode = null

Thanks you gave me another idea to implement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources