Pass resultset of dynamic SQL to Stored Procedure

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

SQL Server Development (2000)

Pass resultset of dynamic SQL to Stored Procedure

Author

Topic

jlrajan
Starting Member

6 Posts

Posted - 2004-11-01 : 16:39:56

From within a Stored Procedure, I am using a Dynamic SQL statement to get the record count of a table from another database. The pseudocode is like this

DECLARE @qry varchar(500), @db_name varchar(100), @count int
SET @db_name = 'northwind'
SET @qry = 'DECLARE @count int'
SET @qry = @qry + CHAR(10)
SET @qry = @qry + 'SELECT @count = COUNT(*) FROM ' + @db_name + '..categories'
SET @qry = @qry + CHAR(10)
SET @qry = @qry + 'SELECT @count'
EXEC (@qry)
SELECT @count

I need the value of the @count variable to be used in the Stored Procedure. My question is how to pass the @count value from the dynamic SQL to the main SP?

Kristen
Test

22859 Posts

Posted - 2004-11-01 : 17:27:29

Use sp_executeSQL and an OUTPUT parameter

Kristen

jlrajan
Starting Member

6 Posts

Posted - 2004-11-02 : 15:27:57

Could you please provide me some sample code(s) on using sp_executesql with an OUTPUT parameter. I tried the following code and it throws an error.

DECLARE @count int, @qry nvarchar(4000), @db_name varchar(40)
SET @db_name = 'Northwind'
SET @qry = 'DECLARE @cnt int'
SET @qry = @qry + CHAR(10)
SET @qry = @qry + 'SELECT @cnt = COUNT(*) FROM ' + @db_name + '..categories'
SET @qry = @qry + CHAR(10)
SET @qry = @qry + 'SELECT @cnt'
EXEC sp_executesql @qry OUTPUT @count
SELECT @count

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 15:41:15

why do you need a stored proc like this in the first place? everywhere that you do something like this:

exec your_sp 'databaseName','tablename'

you can just replace it with this:

select count(*) from databasename..tablename

What is the advantage of this stored proc? unless it is a "DBA maintence" type stored procedure, a sp should never ask for database objects (table, column, database names) as parameters. otherwise, why bother with the sp?

I never understand why people complicate things like this ......

- Jeff

jlrajan
Starting Member

6 Posts

Posted - 2004-11-02 : 15:46:27

Hi Jeff,

This script will be running parallelly in multiple servers having different server names & database names in each. Hence, I have parameterized the DB names. This is part of a data migration exercise between 2 databases. The result of this need to be stored as a log table.

Pls help...

Kristen
Test

22859 Posts

Posted - 2004-11-03 : 01:25:37

A search here reveals this which hopefully answers you question

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41413&SearchTerms=sp_executesql,output

Kristen

PeterT
Starting Member

2 Posts

Posted - 2004-11-19 : 15:05:36

I had the same problem.
Unfortunately I think this is limitation of the T-SQL compiler.
You have variable @count that declared outside the exec statement.

The folowing approach did not help either:
SET @count = exec('SELECT COUNT(*) FROM ' + @db_name + '..categories')
I think insert into ... exec('SELECT COUNT(*) FROM ' + @db_name + '..categories')
may actually work.

Peter

quote:
Originally posted by jlrajan

From within a Stored Procedure, I am using a Dynamic SQL statement to get the record count of a table from another database. The pseudocode is like this

DECLARE @qry varchar(500), @db_name varchar(100), @count int
SET @db_name = 'northwind'
SET @qry = 'DECLARE @count int'
SET @qry = @qry + CHAR(10)
SET @qry = @qry + 'SELECT @count = COUNT(*) FROM ' + @db_name + '..categories'
SET @qry = @qry + CHAR(10)
SET @qry = @qry + 'SELECT @count'
EXEC (@qry)
SELECT @count

I need the value of the @count variable to be used in the Stored Procedure. My question is how to pass the @count value from the dynamic SQL to the main SP?

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-19 : 15:44:55

Yes folks...it's dynamic sql day here at SQLTeam


USE Northwind
GO

SET NOCOUNT ON
DECLARE @sql varchar(8000)

SELECT @sql = 'SET NOCOUNT ON'+CHAR(13) + CHAR(10) + 'CREATE TABLE #myTemp99(TABLE_NAME sysname, NumRows int) ' + CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'INSERT INTO #myTemp99(TABLE_NAME, NumRows) ' + CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'SELECT ''' + TABLE_NAME +  ''', COUNT(*) FROM [' + TABLE_NAME + '] UNION ALL ' + CHAR(13) + CHAR(10)
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'
SELECT @sql = LEFT(@sql,LEN(@SQL)-12)+ CHAR(13) + CHAR(10)
SELECT @SQL = @SQL + 'SELECT * FROM #myTemp99'+ CHAR(13) + CHAR(10)
SELECT @SQL = @SQL + 'DROP TABLE #myTemp99'
--SELECT @sql

EXEC(@sql)
GO

SET NOCOUNT OFF
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources