Getting a syntax error for my SET line
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]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
GOASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 )?
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 )?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
declare @var1 int
EXEC @var1 = dbo.usp_TEST_PARM 'parm1'
SELECT 'RETURN STATUS ' = @var1
GO