CODEWITHSUNDEEP
sql-serverstored-procedures
psj01
psj01

Reputation: 3245

Getting an output from a stored procedure

I have created this stored procedure 

CREATE PROCEDURE AddNewMaterialType4 
     (@category varchar(15), 
      @materialType varchar(50),
      @userID varchar(15), 
      @result1 varchar(15) OUTPUT)
AS
    DECLARE @count1 INT;
    SET @count1 = 0;

    SELECT @count1 = COUNT(*) 
    FROM [vmsMaterialType]
    WHERE category = @category 
      AND materialType = @materialType;

    IF @count1 = 0
    BEGIN
        SET NOCOUNT ON;

        INSERT INTO [vmsMaterialType] ([category], [materialType],[createdBy])
        VALUES (@category, @materialType, @userID);

        SET @result1 = 'Success';
    END
    ELSE
       SET @result1 = 'Fail';
GO

When I execute the following I am not getting the result I expect (Success/Fail) instead its giving me the output what ever I declare result1 as.

declare 
    @result1 varchar(15) = '1';
begin 
    execute AddNewMaterialType4 @category = 'Beef', @materialtYPE = '25 Trim', @userID = 'JJ',  @result1 = @result1;
    print @result1;
end;

Upvotes: 1

Views: 59

Answers (1)

Xedni
Xedni

Reputation: 4715

In your execute AddNewMaterialType4 call, you have to explicitly state that @result1 is an output variable. Try this instead:

declare 
    @result1 varchar(15) = '1';
begin 
    execute AddNewMaterialType4 @category = 'Beef', @materialtYPE = '25 Trim', @userID = 'JJ',  @result1 = @result1 output;
    print @result1;
end;

Upvotes: 3

Related Questions