Returning a scalar value from dyanmic 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

SQL Server Development (2000)

Returning a scalar value from dyanmic SQL

Author

Topic

tribune
Posting Yak Master

105 Posts

Posted - 2005-01-26 : 14:24:26

I'm attempting to get a scalar integer value, specificly the count(*) of a dyanmic query, and produce it as an output parameter of a stored procedure. Since the procedure itself produces only a subset the actual records (say records 1-99), I don't know the total count of the records, e.g. 1-99 of 1,000.

Suppose my query is "select count(*) from customers"

set @SQL = "select count(*) from customers"

set @TotalResultsCount = cast(exec(@SQL) as int)

@TotalResultsCount is specific as an "int output" in the sproc parameter.

How do I do this?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-26 : 14:52:08

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myCounts99(mySPID int, myCount int)
GO

DECLARE @sql varchar(8000)

SELECT @sql = 'DELETE FROM myCounts99 WHERE mySPID = ' + CONVERT(varchar(4),@@SPID) + CHAR(13)
+ 'INSERT INTO myCounts99(mySPID, myCount) SELECT ' + CONVERT(varchar(4),@@SPID) + ', COUNT(*) FROM Orders'
SELECT @sql
EXEC(@sql)

DECLARE @x int
SELECT @x = myCount FROM myCounts99 WHERE mySPID = @@SPID
SELECT @x
GO

SET NOCOUNT OFF
DROP TABLE myCounts99
GO

[/code]

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-26 : 16:19:07

or
declare @SQL nvarchar(1000)
delare @i int
set @SQL = 'select @i = count(*) from customers'
exec sp_executesql @sql, N'@i int out', @i out
set @TotalResultsCount = @i

see
http://www.mindsdoor.net/SQLTsql/sp_executeSQL.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-26 : 17:06:07

OUT

That's what I was missing...


DECLARE @Count int
execute sp_executesql 
          N'select @Count = COUNT(*) from Northwind.dbo.Orders',
          N'@Count int OUT', @Count OUT
SELECT @Count

Brett

8-)

tribune
Posting Yak Master

105 Posts

Posted - 2005-02-03 : 19:23:35

Thank you very much!

David D
Starting Member

1 Post

Posted - 2005-04-06 : 05:23:09

quote:
Originally posted by nr

or
declare @SQL nvarchar(1000)
delare @i int
set @SQL = 'select @i = count(*) from customers'
exec sp_executesql @sql, N'@i int out', @i out
set @TotalResultsCount = @i

What would be the solution if table 'customers' is also a variable?
f.e. @table
I always get an error: "Must declare the variable '@table'."

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-06 : 05:54:16

This isnt recommended at all, it can be done

What do you think would happen if you got this to work?
eg
EXECUTE YourSproc 'YourBigTable DROP YourBigTable'

Start a new thread outlining exactly what you want to do and any code/sample data you have done so far

Edit:
Welcome to the forum

Beauty is in the eyes of the beerholder

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-06 : 07:56:49

declare @SQL nvarchar(1000)
delare @i int
set @SQL = 'select @i = count(*) from ' + @table
exec sp_executesql @sql, N'@i int out', @i out
set @TotalResultsCount = @i

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources