How to store results from dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to store results from dynamic SQL

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2002-08-23 : 15:03:53

How do I store the results of a dynamic SQL statement into a variable? I am somehow missing this, as I'm sure it can be done. Here's the SPROC...

********************************************************************
CREATE PROCEDURE Get_RecordCount @TableName VarChar(100), @@RetVal INT OUTPUT AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT COUNT(*) FROM ' + @TableName + ')'
Exec (@SQL)

-- Question 1. Now, how do I set @@RetVal here to the results?

GO
*********************************************************************

And here is the testing of it via Query Analyzer...

*********************************************************************
Declare @TableName Varchar(100)
Declare @NumRecs INT

SELECT @TableName = (Select TableDataBase+'.'+TableOwner+'.'+TableName FROM TableRecordCount WHERE ID_KEY = 1)

EXEC Get_RecordCount @TableName, @NumRecs OUTPUT

--2. Question 2. If I get @@RetVal set in the above sp, then @NumRecs should be = @@RetVal, right?

SELECT @TableName,@NumRecs
********************************************************************

solart
Posting Yak Master

148 Posts

Posted - 2002-08-23 : 15:31:31

Would a user defined function be what you need instead of a sproc?

BOl says:

Creates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.

solart

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-23 : 15:42:34

too bad you can't execute stored procedures from within udf's ... so the EXEC('') part would return an error ...

what you need to do is look at sp_executesql ... here i did the nasty for you already...

CREATE PROCEDURE GetRecordCount (@TableName VARCHAR(255)) AS
SET NOCOUNT ON

DECLARE @intCount INT, @strSQL NVARCHAR(4000)
SET @strSQL = 'SELECT @Count = COUNT(*) FROM ' + @TableName

EXEC sp_executesql @strSQL, N'@Count INT OUTPUT', @intCount OUTPUT
RETURN @intCount
GO

... then just

EXEC @Count = GetRecordCount 'TableName'

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2002-08-23 : 16:23:47

Thanks Onamuji, that works alot better than using temp tables. Could I call this within an UPDATE statement, something like this or do I use FETCH?

UPDATE myTable SET myIntField = (EXEC GetRecordCount myTable.TableNameField)

Thanks again...

solart
Posting Yak Master

148 Posts

Posted - 2002-08-23 : 16:36:04

Kudos to Onamuji

solart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources