CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
tnw
tnw

Reputation: 23

Output parameter for stored procedure

I'm trying to write a value to an output parameter. In my THEN statement I'd like to either return outParam as 1 or 0 but not sure how to modify the below statement to do so. Can anyone help? Thanks!

CREATE PROCEDURE [dbo].[myProc] 
     @var1 UNIQUEIDENTIFIER, 
     @outParam BIT OUTPUT
AS
    SELECT 
        CASE
           WHEN EXISTS (SELECT 1
                        FROM [Table1] AS [i]
                        WHERE (CONVERT(VARCHAR(36), [i].[var1]) = @var1) 
                          AND ([i].[BitColumn] = 1))
              THEN CAST(1 AS BIT) 
              ELSE CAST(0 AS BIT)
        END
GO

Upvotes: 2

Views: 63

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8111

You're ever so close! Just set it and go.

CREATE PROCEDURE [dbo].[myProc] @var1 uniqueidentifier, 
@outParam bit OUTPUT
AS
SELECT @outParam = CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Table1] AS [i]
        WHERE (CONVERT(VARCHAR(36), [i].[var1]) = @var1) AND ([i].[BitColumn] = 1))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END;
GO

Upvotes: 3

Related Questions