Out parameter is correctly assigned, but caller only sees a NULL. Clue?

out-parameters, sql-server-2014, stored-procedures, t-sql

Solution

You need to mention the `@categoryId` parameter as `OUTPUT` while calling the procedure else it is not going to return the value. Call the procedure like this

exec dbo.InsertCategory 'C1', 'Category1', 'Catégorie1', @ts, @categoryId OUTPUT;

Example

CREATE PROCEDURE Procd (@a INT, @b INT output)
AS
  BEGIN
      SELECT @b = @a
  END

DECLARE @new INT

EXEC Procd 1,@new 
SELECT @new -- NULL

EXEC Procd 1,@new OUTPUT 
SELECT @new -- 1

Problem

I have this procedure that gets dropped/created as part of a T-SQL script - the idea is to insert a parent record, and output its ID to the caller so that I can insert children records using that ID. ``` if exists (select * from sys.procedures where name = 'InsertCategory') drop procedure dbo.InsertCategory; go create procedure dbo.InsertCategory @code nvarchar(5) ,@englishName nvarchar(50) ,@frenchName nvarchar(50) ,@timestamp datetime = null ,@id int output as begin if @timestamp is null set @timestamp = getdate(); declare @entity table (_Id int, EntityId uniqueidentifier); declare @entityId uniqueidentifier; if not exists (select * from dwd.Categories where Code = @code) insert into dwd.Categories (_DateInserted, Code, EntityId) output inserted._Id, inserted.EntityId into @entity values (@timestamp, @code, newid()); else insert into @entity select _Id, EntityId from dwd.Categories where Code = @code; set @id = (select _Id from @entity); set @entityId = (select EntityId from @entity); declare @english int; set @english = (select _Id from dbo.Languages where IsoCode = 'en'); declare @french int; set @french = (select _Id from dbo.Languages where IsoCode = 'fr'); exec dbo.InsertTranslation @entityId, @english, @englishName, @timestamp; exec dbo.InsertTranslation @entityId, @french, @frenchName, @timestamp; end go ``` Then a little further down the script it's called like this: ``` declare @ts datetime; set @ts = getdate(); declare @categoryId int; exec dbo.InsertCategory 'C1', 'Category1', 'Catégorie1', @ts, @categoryId; exec dbo.InsertSubCategory 'SC1', @categoryId, 'Description (EN)', 'Description (FR)', @ts ``` When I debug the script and step through line by line, I can see that `dbo.InsertCategory` correctly assigns the `@id` out parameter, which the script sees as `@categoryId` - the problem is that `@categoryId` is always `null`, and so I'm not getting anything inserted into `dwd.SubCategories`. What am I doing wrong?

Original source