DTS & Stored Procedures

#1

i have a stored procedure that counts the number of records in a table and returns an intege

ie

CREATE PROCEDURE TESTSP
@RTNVAL INTEGER OUTPU
A

Set NoCount o
Select @RTNVAL = Count(*) from Table

If @RtnVal > 0
begi
Set @RtnVal =
Return(@RtnVal
en
els
Return(@RtnVal
G

If i call this from an executeSQL task how can i assign the returnvalue to a global varaible

#2

Try either one in an Execute SQL task:

exec ? = TESTSP

or exec TESTSP ? output

"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

i have a stored procedure that counts the number of records in a table and
returns an integer

ie

CREATE PROCEDURE TESTSP
@RTNVAL INTEGER OUTPUT
AS

Set NoCount on
Select @RTNVAL = Count(*) from Table1

If @RtnVal > 0
begin
Set @RtnVal = 1
Return(@RtnVal)
end
else
Return(@RtnVal)
GO

If i call this from an executeSQL task how can i assign the returnvalue to
a global varaible

#3

Ilya,

I have already tried that.

my ExecuteSQlTask looks like this
DECLARE @SPReturn Integer EXEC Boss_UpdateHeaders
@Licence = '217353' , @ModifiedDate = '21/01/2004' ,
@UserSerial = '009' , @BureauVolume= '000000' ,
@FileStatus = 'Unsubmitted' , @FileType = 'Live' ,
@Action = 'A' , @RtnVal = @SPReturn OUTPUT

but when i click on the Execute SQL Task properties
Parameters button i get a message that there are no
parameters for this query, and i can not set the output
to a Global Varaible.

Quote:

-----Original Message-----
Try either one in an Execute SQL task:

exec ? = TESTSP

or exec TESTSP ? output

"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com
wrote in message
news:B32F7FE9-8530-4C36-9A93-
568AEF4F42C3 (AT) microsoft (DOT) com...
i have a stored procedure that counts the number of
records in a table and
returns an integer

ie

CREATE PROCEDURE TESTSP
@RTNVAL INTEGER OUTPUT
AS

Set NoCount on
Select @RTNVAL = Count(*) from Table1

If @RtnVal > 0
begin
Set @RtnVal = 1
Return(@RtnVal)
end
else
Return(@RtnVal)
GO

If i call this from an executeSQL task how can i
assign the returnvalue to
a global varaible

.

#4

As per Ilya's post u have to use question mark <?> as place holder instead of @spretur
also need to make sure that output parameter is in the first line of text because of OLEDB parser issue with DT
somewhat related to KBArticle - 30437

HTH

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS & Stored Procedures

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS & Stored Procedures - 02-11-2004 , 05:41 AM

Re: DTS & Stored Procedures - 02-11-2004 , 07:41 AM

Re: DTS & Stored Procedures - 02-11-2004 , 07:57 AM

Re: DTS & Stored Procedures - 02-12-2004 , 04:46 AM