CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Dishant
Dishant

Reputation: 1595

How to assign value to a variable from a select statement inside stored procedure?

I have this stored procedure and for debugging purposes, I want to know what value is getting returned, in order to do that I have created a table and logging the value returned from a query but I am not sure on how to get the value in that select statement for loggin.

My stored procedure (modified for demonstration):

BEGIN
    DECLARE @SID INT 

    BEGIN TRY
        SELECT 
            SL.[Column1], SL.[Column2], SL.[Column3], 
            SMD.[Column4], SMD.[Column5]
            (some more logic)               

        BEGIN 
            DECLARE @ProcedureName6 NVARCHAR(168) = 'dbo.MYSP',
                    @ErrorMessage6 NVARCHAR(4000)

            SELECT @ErrorMessage6 = 'SID is:' +  CAST(@SID AS VARCHAR)

            EXEC [Log].[ErrorLoger] @ProcedureName6, @ErrorMessage6
        END
    END TRY
    BEGIN CATCH
        //Some Logic
    END CATCH
END

I actually want SL.[Column2] to be set in @SID for logging.

Upvotes: 1

Views: 2884

Answers (1)

Squirrel
Squirrel

Reputation: 24803

if you have multiple result return from the query, you can insert the result to a temp table. From there, get the required value from the temp table and execute the ErorrLogger SP. 

INSERT INTO #TEMP ( Column1, Column2, . . .)
SELECT SL.[Column1]
        ,SL.[Column2]
        ,SL.[Column3]
        ,SMD.[Column4]
        ,SMD.[Column5]
      (Some more logic)     

-- Get the value of @SID from temp table
SELECT @SID = Column2 FROM #TEMP

-- Return the result
SELECT  Column1, Column2, . . .
FROM    #TEMP

....
EXEC [Log].[ErrorLoger] @ProcedureName6
                       ,@ErrorMessage6

You may also use multiple variable (each for one column) to hold the result of your query. Provided that the query only return single row

Upvotes: 1

Related Questions