September 28, 2009 at 2:27 pm
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.
September 28, 2009 at 2:40 pm
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
September 28, 2009 at 3:50 pm
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.
September 28, 2009 at 11:16 pm
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)
September 29, 2009 at 6:57 am
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
September 29, 2009 at 11:25 am
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.
September 29, 2009 at 12:02 pm
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.
September 29, 2009 at 1:07 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply