July 9, 2009 at 10:45 am
Is there a way I can EXEC proc successfully WITHOUT having to pass the ' ' as a parameter to accomodate OUTPUT?
>> Here is how I invoke this proc:
This works: EXEC spr_MyProc 'Widgets_1', ' ' <--- this includes a space to accomodate Output parm???
This fails: EXEC spr_MyProc 'Widgets_1'
>> Here is my PROC (a shell anyway)
_____________________________________
CREATE PROCEDURE spr_MyProc
@MyInput NVARCHAR(254) = " ",
@returncode INT output
AS
... some code to build @MySSIS_initiation
-- last 2 lines of proc are:
EXEC @returncode = xp_cmdshell @MySSIS_initiation
SELECT @returncode
July 9, 2009 at 10:51 am
If you have an output parameter then you need to pass a variable in that spot so that the output can go into something.
This will work and allow you to see the value of the output parameter.
DECLARE @returnCode int
EXEC spr_MyProc 'Widgets_1', @returnCode OUTPUT
SELECT @returnCode
If you want that as a return code, not an output proc, then this is how to do it.
CREATE PROCEDURE spr_MyProc
@MyInput NVARCHAR(254) = " "
AS
... some code to build @MySSIS_initiation
-- last 2 lines of proc are:
EXEC @returncode = xp_cmdshell @MySSIS_initiation
RETURN @returncode
GO
DECLARE @returnCode int
EXEC @returnCode = spr_MyProc 'Widgets_1'
SELECT @returnCode
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply