Here is the following procedure code:
ALTER PROCEDURE spM2016_ErrorNumberDesignator
-- Add the parameters for the stored procedure here
@procid int = 0,
@debug int = 0,
@errorNum int = 60000 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @error1List nvarchar(max) = '''spM2016_FullTableName'', ''spM2016_NullCheckQuoteName_SchemaDBTable_Names'''
DECLARE @procResults TABLE ([name] sysname);
--SET @errorNum = 60000; uncomment this line
SET @sql = N'SELECT name ' +
N'FROM sys.objects ' +
N'WHERE OBJECT_NAME(@proc) IS NOT NULL ' +
N'AND ' +
N'name = OBJECT_NAME(@proc) ' +
N'AND ' +
N'OBJECT_NAME(@proc) IN (@error1List); ';
SET @params = N'@proc int';
SET @sql = REPLACE(@sql, '@error1List', @error1List);
IF (@debug = 1)
PRINT REPLACE(@sql, '@proc', @procid);
ELSE
BEGIN
INSERT INTO @procResults EXECUTE sp_executesql @sql, @params, @proc = @procid;
IF (@debug = 1)
BEGIN
SELECT * FROM @procResults;
SET @errorNum = 60000;
END
ELSE
BEGIN
-- Insert statements for procedure here
IF EXISTS (SELECT * FROM @procResults)
BEGIN
SET @errorNum = 60001;
END
END
END
END
GO
Can anyone explain to me why the @errorNum parameter output its default value of 60000 when the line SET @errorNum = 60000 is uncommented, but doesn't output 60000 when the line is commented?
For example, when I execute the procedure with @procid = 0 and try and print the @errorNum I get what seems to be an empty value. Does it have something to do with how I've written my if statements?
Here's the code I'm using for execution:
DECLARE @RC int
DECLARE @procid int
DECLARE @debug int
DECLARE @errorNum int
-- TODO: Set parameter values here.
SET @debug = 0;
SET @procid = 0;
EXECUTE @RC = [dbo].[spM2016_ErrorNumberDesignator]
@procid
,@debug
,@errorNum OUTPUT
PRINT @errorNum;
GO
