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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paultran00Author Commented:
I found one way:

declare @var1  int
EXEC @var1 = dbo.usp_TEST_PARM 'parm1'
SELECT 'RETURN STATUS ' = @var1
GO
1
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.