Dynamic query result set 1

[pdbowling]

Hello Everyone.

I have been given an assignment that has me perplexed.

I am curious if there is a way to get the result set of a dynamic query returned to the calling stored procedure. Since dynamic sql runs in it's own context, I can't just assign variables in the @SqlString. I am open to all suggestions. The code below is just my jump off point.

@TableName is a parameter.

DECLARE @SqlString NVARCHAR(max)

SET @SqlString = N'
SELECT rt.name, MAX(rt.currentclass) AS MaxClass
  FROM ' + @TableName + ' AS rt WITH (NOLOCK)
 GROUP BY rt.name
'

Thanks everyone.
Patrick

 

[simian336]

Not sure I follow, could you clarify what you want to do.

Thanks

Simi

 

[djj55]

If I understand you may be looking for: sp_executesql

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bborissov]

DECLARE @SqlString NVARCHAR(max)

SET @SqlString = N'
SELECT rt.name, MAX(rt.currentclass) AS MaxClass
  FROM ' + @TableName + ' AS rt WITH (NOLOCK)
 GROUP BY rt.name
' 

EXEC (@SqlString)
--- or EXEC sp_execute sql @SqlString

Borislav Borissov
VFP9 SP2, SQL Server

 

[pdbowling]

Thanks everyone.

I found a solution. I can use a ## table. Their scope is broader than the # table that goes out of scope after the dynamic sql has been executed.

DECLARE @SqlString NVARCHAR(max)

SET @SqlString = N'
SELECT rt.name, MAX(rt.currentclass) AS MaxClass
  INTO ##tempTable
  FROM ' + @TableName + ' AS rt WITH (NOLOCK)
 GROUP BY rt.name
' 
  EXEC (@SqlString2);

SELECT *
  FROM ##tempLineMaxClass

Now I have the result set of the dynamic sql in my original procedure and can manipulate it. All I need to do is manage the life cycle of the ## table.

Patrick

 

[bborissov]

Why you need to insert into global temp table?
Did you tried my example, it will give you exactly what you want, no need of extra tables.


Borislav Borissov
VFP9 SP2, SQL Server

 

[gk53]

You can have potential problem with global temp table... it is multy user environment... so if you run your script and somebody else run that script second run will fail becouse global temp table already exist...
and if you include statement to drop and recreate table it will refil thae same named table with different data...

 

[SaltyTheFrog]

I beleive you can create the temp table first and insert into it in the dynamic SQL. That will avoid the problem gk53 mentioned.

 

[pdbowling]

Thank you for your input everyone.

@bborissov. Yes, the sql certainly runs in the EXEC() but it does not return the record set to the stored proc. I need to manipulate the table.

@gk53. Very good point.

@SaltyTheFrog. Thank you. I will see how this affects race conditions for nearly simultaneous access.

Thanks, all.
I love this forum.
Patrick