| Author |
 Topic  |
|
|
tribune
Posting Yak Master
USA
105 Posts |
 Posted - 01/26/2005 : 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 - 01/26/2005 : 14:52:08
|
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
Brett
8-) |
 |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 01/26/2005 : 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. |
Edited by - nr on 01/26/2005 16:20:25 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 01/26/2005 : 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
USA
105 Posts |
Posted - 02/03/2005 : 19:23:35
|
| Thank you very much! |
|
|
|
David D
Starting Member
1 Posts |
Posted - 04/06/2005 : 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
United Kingdom
583 Posts |
Posted - 04/06/2005 : 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  |
Edited by - AndyB13 on 04/06/2005 05:55:34 |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 04/06/2005 : 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. |
|
|
| |
Topic |
|
Returning a scalar value from dyanmic SQL
