SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Returning a scalar value from dyanmic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tribune
Posting Yak Master

USA
105 Posts

Posted - 01/26/2005 :  14:24:26  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote

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-)
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/26/2005 :  16:19:07  Show Profile  Visit nr's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/26/2005 :  17:06:07  Show Profile  Reply with Quote
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-)
Go to Top of Page

tribune
Posting Yak Master

USA
105 Posts

Posted - 02/03/2005 :  19:23:35  Show Profile  Reply with Quote
Thank you very much!
Go to Top of Page

David D
Starting Member

1 Posts

Posted - 04/06/2005 :  05:23:09  Show Profile  Visit David D's Homepage  Reply with Quote
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'."
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 04/06/2005 :  05:54:16  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/06/2005 :  07:56:49  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000