return select from dynamic sql

[venerable bede]

Hi All

I have a concatenated sql string:

set @sqlString = 'Select * from blah'

I can then of course execute it with :

exec (@sqlString)

I need to return this select somehow. How can I do something like :

Select * from (exec (@sqlString))


Thanks In Advance

[szlamany]

The exec() does return the SQL - look at this image - both recordsets appear at the bottom - one from the "pure select" and one from the "dynamic select"

[venerable bede]

I dont get any result set from within a stored procedure.

I just get '1 row affected'

[szlamany]

Do you have a SET NOCOUNT ON at the top of that SPROC?

[TysonLPrice]

Someone here gave me this tip and I'm assuming this is MS SQL. Run the query in Enterprise Mangaer or a native query window and direct the output to text. In my environment its on the Query menu, Results to, Results to text. Sometimes you will see things are returning that do not show in the results grid. Also, although it doesn't look like it here, shutting ANSI warning off will eliminate warning messages you might not care about.


Definately set NO COUNT ON as szlamany posted.

[szlamany]

Good point - if you are getting an error (like you might be doing a SUM() and you have NULL's in the field you are summing) that error message might get "in front of the return data" when called from code.

I always create my SPROCS in a QUERY WINDOW - usually with an EXECUTE of that SPROC at the bottom to test it.

Then I save my CREATE SPROC as a .SQL text file...

[venerable bede]

The problem I am having guys is that when I right click on my sproc and choose execute I get 2 returned sets of data.

The top result set is what is returned from the select but below that I get

Return Value
0

Been a while since I've used sql server. Am I doing something wrong?

[szlamany]

Are you using SQL Server Management Studio?

If so - are you making a NEW QUERY WINDOW?

If not - do so and put the name of the SPROC in the window - without anything in front of it.

Put each parameter following that name.

Click the EXECUTE button.

What do you see in the RESULTS window?? One or two recordsets?

What is in the MESSAGES window?

[venerable bede]

Sorry for a late reply but I've been ill.

Anyways I still find that when I run this procedure from code I get no return set at all.

I must have to select from the executed sql string but really cant figure out how to do it.
Do you think I have to create a temp table?

[szlamany]

The problem you are having seems very odd - it should work just as you have it.

Please do the steps I asked for in my last post - they are required for me to assist you in discovering the reason for your issue.