Getting a syntax error for my SET line


Open in new window

Hi, I'm using SQL SERVER 2008R2.

My stored procedure RETURN 1 or 0 and I want to capture that return value.   Problem is I'm getting a syntax error for my SET line

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'EXECUTE'.
[code][code]

Open in new window

[/code][/code]


CODE:


declare @var1  int

set @var1 = EXECUTE dbo.usp_TEST_PARM 'parm1'

--print var1

USE [MEDD_CLAIMS]
GO

/****** Object:  StoredProcedure [dbo].[usp_TEST_PARM]    Script Date: 03/07/2018 15:43:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*

EXECUTE dbo.usp_TEST_PARM 'parm1'

*/

CREATE PROCEDURE [dbo].[usp_TEST_PARM]

@PASS_PARM as varchar(50)='Opps'

AS

SET NOCOUNT ON

SELECT [Field1]='Hello',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello2',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello3',[Field2]='Seconds'

RETURN 3

SET NOCOUNT OFF


GO

Open in new window

paultran00Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Correct, you can not do :  set @var1 = execute ......

And :  exec  set @var1 =

Is the correct way if you dont want the content (ie the selects), but, the better way.... You can also declare an output parameter and capture that


alter PROCEDURE [dbo].[usp_TEST_PARM]

@PASS_PARM as varchar(50)='Opps',
@OUT_Param int output

AS

SET NOCOUNT ON

SELECT [Field1]='Hello',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello2',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello3',[Field2]='Seconds'

set @out_param = 3      -- better to set @out_param = @@rowcount

SET NOCOUNT OFF


GO


-- then you can do

declare @var1  int
EXEC  dbo.usp_TEST_PARM 'parm1',@var1 output
select @var1

Open in new window

And we get both the select results and the output param.
See https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx
0
 
paultran00Author Commented:
I found one way:

declare @var1  int
EXEC @var1 = dbo.usp_TEST_PARM 'parm1'
SELECT 'RETURN STATUS ' = @var1
GO
1
 
paultran00Author Commented:
Hi,

The production stored procedures use RETURN and not the OUTPUT parameter so I can't declare an output parameter.

THIS WORKS: The SSIS package calls the stored procedure usp_TEST_PARM and it returns a value to SSIS to capture and  know if there’s an error or not.

QUESTION: When the parent stored procedure usp_TEST_PARM  calls a child stored procedure and there’s an error in the child stored procedure, how do we get the child stored procedure to RETURN  a value so that SSIS would know that an error occurred in the child stored procedure (so that SSIS can send an alert email that says that an error happened in the child stored procedure and not the parent stored procedure usp_TEST_PARM  )?
0
 
Mark WillsTopic AdvisorCommented:
Getting into a different question now :) But have a read through : http://www.sqlteam.com/article/handling-errors-in-stored-procedures talks about error levels and aborts and trapping

You might also want to read THROW : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql
and RAISERROR : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql

But seeing as there will be some kind of error handling (ie edit procedure), then consider very strongly, the use of OUTPUT.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.