Exec @SQL not working

Question

Post reply

Exec @SQL not working

WebTechie

SSChampion

Points: 11405
More actions
September 28, 2009 at 2:27 pm

#385452

Hello,
I've built a string into a variable called @sql which is a nvarchar(3000).
I can write print (@sql) and get the returned string. I then take the return string and copy it to another windows. It works well.
When I try to exec it with EXEC @sql, I get in the message tab "Command(s) completed successfully." However no rows.
(Just to show how I am building the string. The whole code is long. However, it works when I return the string and copy it another window.)
set @sql = 'select '+@q + rtrim(@mCompanyName) + @q + ',' +
@q + @acctTable + @q + ',' + 'A.DEX_ROW_ID,
A.SOURCDOC,
DATEPART(yyyy, A.TRXDATE).....
I know this is simple and I am just missing something, but why wouldn't I get any rows?
Thanks,
Tony.
Things will work out. Get back up, change some parameters and recode.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you using:

exec (@SQL)

or

exec @sql?

The difference is the parentheses.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WebTechie SSChampion Points: 11405 More actions · Answer 2

I've tried both.

With the parentheses:

EXEC @sql

Msg 2812, Level 16, State 62, Line 127

Could not find stored procedure ''.

With the parenteses:

Command(s) completed successfully.

I would like the actual rows returned.

Thanks,

Tony

Things will work out. Get back up, change some parameters and recode.

arun.sas SSChampion Points: 11831 More actions · Answer 3

WebTechie38 (9/28/2009)
I would like the actual rows returned.

Hi,

Some of the parameter you declared for the dynamically sql may leave it as null i.e. your not set the value for all declare variables,

Example

declare @sql nvarchar(200),

@sql1 nvarchar(50),

@sql2 nvarchar(50),

@id1 nvarchar(50)

set @id1 = '2'

set @sql1 = 'select top '+ @id1 + ' * from MYTABLE'

set @sql = @sql1+@sql2

exec (@sql)

same showed with result

declare @sql nvarchar(200),

@id1 nvarchar(50)

set @id1 = '2'

set @sql = 'select top '+ @id1 + ' * from MYTABLE'

exec (@sql)

GSquared SSC Guru Points: 260824 More actions · Answer 4

Right before you execute the string, add "print @sql", see what you get.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WebTechie SSChampion Points: 11405 More actions · Answer 5

Yes, I agree. That is my normal troubleshooting.

I used print (@SQL) to make sure allthe variables get initialized. I can take the output of print (@SQL) and run it in another window and it runs fine.

EXEC (@sql) does not work.

Thanks.

Things will work out. Get back up, change some parameters and recode.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 6

Whenever I have faced such situations with dynamic SQL; invariably the issue has been some variable being null ( as @arun.sas said and demonstrated).

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

It's hard to speculate what is wrong with code you didn't post.

Maybe it has nothing to do with dynamic SQL. Maybe the query is just not returning any rows.

WebTechie SSChampion Points: 11405 More actions · Answer 8

I continually am so thankful for this forum.

You were right. I had one variable that was null. I went through each variable and found it. I corrected the sql statement. Now I am getting rows.

Thanks for pointing me in the right direction!!!

Tony

Things will work out. Get back up, change some parameters and recode.