CODEWITHSUNDEEP
sql-servervariablesstored-procedures
Sadjad Johansson
Sadjad Johansson

Reputation: 320

How to initialize a variable in stored procedure with select statement

I wonder how I can directly give this variable (@p_txtValue) in the stored procedure (EXEC dbo.bisting_sp_common_Event_SetParameter) a value with a select statement without using SET statement above.

This is my code where I initialize @l_txtTemp with a SET statement then the give @p_txtValue the value from it.

SELECT 
    ssh.No_
    ,ssh.[Bill-to Customer No_]
    ,RANK () over (ORDER BY ssh.No_) Rank_Id
INTO #shipments --DROP TABLE #shipments
FROM dbo.[Production$Sales Shipment Header] SSH 
WHERE SSH.[Sell-to Customer No_] IN ('3004', '3003', '3002') 
  AND ssh.[Posting Date]>'2015-10-01'
  AND NOT EXISTS (SELECT * 
                  FROM dbo.[bisting System Monitor] tSub1 
                  WHERE tSub1.[Row Type] = 'Log' 
                    AND tSub1.[Type] ='Checkpoint Init' 
                    AND tSub1.[Master Event Type] = @l_txtCurrMasterEventType
                    AND tSub1.[Document No_] = ssh.No_)

    SELECT @l_intRankIDCurr = 1, @l_intRankIDMax = (SELECT MAX(Rank_Id) FROM #shipments)

    WHILE (@l_intRankIDCurr <= @l_intRankIDMax) 
    BEGIN
        SELECT @l_txtShipmentNo = No_
        FROM #shipments WHERE Rank_Id = @l_intRankIDCurr

        SET @l_txtTemp = CASE (SELECT t1.[Bill-to Customer No_] 
                               FROM #shipments t1 
                               WHERE t1.Rank_Id = @l_intRankIDCurr)
                     WHEN '3002' THEN 'Uppsala.csv'
                     WHEN '3003' THEN 'Gränby.csv'
                     WHEN '3004' THEN 'Örebro.csv'
                   END 

            IF @p_intLogLevel >= 4 PRINT CONVERT(VARCHAR(19),CURRENT_TIMESTAMP,121)+': Create set document no parameter'
            EXEC dbo.bisting_sp_common_Event_SetParameter @p_bigintEventID=@l_bigintChildEventID
                ,@p_txtAttributeLvl1='Action SP Filename'
                ,@p_txtValue= @l_txtTemp

        SELECT @l_intRankIDCurr += 1

    END

Is it possible to make something like this? this example below didn't work for me neither the other ones i tried:

IF @p_intLogLevel >= 4 
   PRINT CONVERT(VARCHAR(19),CURRENT_TIMESTAMP,121)+': Create set document no parameter'

EXEC dbo.bisting_sp_common_Event_SetParameter 
        @p_bigintEventID = @l_bigintChildEventID
        ,@p_txtAttributeLvl1 = 'Action SP Filename'
        ,@p_txtValue = (SELECT [Attribute] 
                        FROM [TABLE] t1 
                        WHERE t1.Rank_Id = @l_intRankIDCurr)

Upvotes: 2

Views: 2259

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Only way is:

declare @p_txtValue nvarchar(max) = 
 (SELECT [Attribute] FROM [TABLE] t1 WHERE t1.Rank_Id = @l_intRankIDCurr)

and:

EXEC dbo.bisting_sp_common_Event_SetParameter ....@p_txtValue = @p_txtValue

because of:

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] }

that means that you can pass directly string 'somevalue' or with a variable.

Upvotes: 2

Related Questions