Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


exec storedProc get return value


exec storedProc get return value

Author
Message
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
Hi guys,

I am very experienced in T-SQL but am still fairly new to SSIS and am trying to run before I walk. I have an Execute SQL task which runs a stored procedure that updates some database records. It takes two input parameters which are supplied in package variables. so far so good - the code runs and succeeds.

The storedProc does some validation (e.g. does the ID passed in exist in the database) and returns -1 if there is an issue or 99 if it runs to completion as expected. There is no other results set.

How do I configure the SQL statement and the results set.

Current config is as follows:

Execute SQL Task:
ResultSet:- None
ConnectionType:- OLE DB
SQLSourceType:- Direct input
SQL Statement:- exec OperationalBatchGenerateFile ?, ?
IsQueryStoredProcedure:- false
BypassPrepare:- True

Parameter Mapping:
User::BatchID, Input,SHORT,0
User::TimeStamp,Inout,NVARCHAR,1
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
-Create a variable to hold your return value.
-Change your ResultSet: None to SingleRow
-Map your variable to your result set
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
Thanks Daniel, I am struggling with this concept... I did as you suggested and get processing errors..
Here is the storedProc that is being called from the SQL task


ALTER PROCEDURE [dbo].[OperationalBatchGenerateFile](@BatchID int, @timestamp nvarchar(50))
AS
BEGIN
SET NOCOUNT ON
DECLARE @msg nvarchar(255)
DECLARE @Cancel as int
SET @Cancel = 0

/* check if the batch ID exists */
IF (SELECT count(*) from opsBatch where batchID = @BatchID) = 0
BEGIN
SET @msg = 'Batch ID ' + cast(@BatchID as nvarchar(5)) + ' is not recognised'
raiserror(@msg,1,1)
SET NOCOUNT OFF
RETURN -1 -- quit the routine with error: This should never happen because the data has been extracted from the batch id
END

/* if we have passed all of the above tests then we can update the batch and the lines */

BEGIN TRANSACTION -- all pass or all fail
UPDATE
opsBatchRecord
SET
mailStatus = 'Exported',
MailStatusDate = cast(convert(nvarchar(20),getdate() ,112) as datetime)
WHERE
BatchID = @BatchID
UPDATE
opsBatch
SET
Status = 'Exported',
ExtractFileName = cast(@BatchID as nvarchar(5)) + '_' + @timestamp + '.txt'
WHERE
BatchID = @BatchID

COMMIT TRANSACTION

Return 99
SET NOCOUNT OFF
END




in Mgmt Studio, if you run

EXEC OperationalBatchGenerateFile 1234, '20121113_104300'



Then the procedure runs but does not return a results set.

If you use the right click->Execute on the sp and you get the following script

DECLARE @return_value int

EXEC @return_value = [dbo].[OperationalBatchGenerateFile]
@BatchID = 10111,
@timestamp = N'20121113_114300'

SELECT 'Return Value' = @return_value

GO



then the return value is 99 or -1 depending on what happens. It is this value that I want to interrogate.

Can I get at this return value without a results set or alternatively pickup the raiserror()
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
Quick update.

One problem I was having was that I had not renamed the default resultset to 0 to return the first column.

Now I get the error message in the Progress tab

Error: No result rowset is associated with the execution of this query.

So it am still not getting the return value
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Try making the variable an object type. Although you are really only returning a single value, the task views it as a set that might contain more than one column value.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
The solution is to write the SQL statement as

EXEC ?= usp_myStoredProc ?,?


and set the parameters as Param0 = ReturnValue, Param1 = Input, Param2=Input and assign Param0 to the variable you want to carry through.

Thanks to everyone for the help offered
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search