using sp_executesql to return two output value

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)

using sp_executesql to return two output value

Author

Topic

verybrightstar
Starting Member

16 Posts

Posted - 2005-05-24 : 07:48:50

Hi all ,
as for i know sp_executesql can return one parameter output , how can i return two parameter output using one sp_executesql statement

error throws show must declare the
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@TotalPrice'.

CREATE PROCEDURE [dbo].[SPTest]

AS

declare @SQL as nvarchar(4000)
declare @TotalSold as int
declare @TotalPrice as float

set @TotalSold=0
set @TotalPrice=0

DECLARE @Count int

set @SQL = N'select @Count = COUNT(*) , @TotalPrice=SUM(Price) from dbo.tblTransItems'

execute sp_executesql
@SQL,
N'@Count int OUT', @Count OUT , N'@TotalPrice float OUT' , @TotalPrice OUT

print @Count
print @TotalPrice

kt

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-24 : 08:06:50

execute sp_executesql
@SQL,
N'@Count int OUT, @TotalPrice float OUT', @Count OUT, @TotalPrice OUT

==========================================
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