Query Analayzer Exec Stored Procedure Help 2

[mr2tho]

Hi,
I am trying to get rid of an error message in Crystal Report 9.0 which is using a stored procedure. All reports using a stored proc work fine except that one. And it only gives an error message when the parameters passed causes the sp to return no records...
Lets have a look on what's special about this sp:
This one has a code like this:
-----
SELECT @strQuery='SELECT tb.fnm, '+@Budget'+ 'FROM tb Inner join tb2 WHERE tb.year in '@YrPr

EXEC @srtQuery
------
It's acctually a very long string, I tried to sumarize it.
When I run this with paramaters that doesn't return records in Query Analyzer, it only says:
"The commands completed successfully."

When it returns records, then I am able to see all records with all column names.

When I run my other stored procedures, I can at least see the column names even if I have no records. So that's the difference.

So that one acts differently. I don't know why Query Analyzer says commands completed only instead of informing me that there are no records but
Is there any way to make it behave like that?
Thanks so much in advance...

 

[SQLDenis]

Both of these return column names for me

use pubs
declare @sql varchar(500)
select @sql ='select * from authors'
exec (@sql)
Go

declare @sql varchar(500)
select @sql ='select * from authors where 1 =0'
exec (@sql)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

For debugging purposes, I suggest adding a line before the EXEC statement, so that you can see the query being exec'd.

[!]PRINT @strQuery[/!]
EXEC @strQuery

The query should be printed to the messages window in query analyzer. Copy/paste the query to a new QA window and run it. See if there are any errors.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I think one of you variables is null

check this out

declare @au_ID varchar(50)
declare @sql varchar(500)
select @sql ='select * from authors where au_id =' + @au_ID
exec (@sql)


NULL + anything else is NULL so use COALESCE to check for NULLS

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mr2tho]

Well, now I see that the one of the calculated variables was null and that was the reason...Thank you guys very much and have a great day guys, appreciated...