CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
topwik
topwik

Reputation: 3527

Can I pass the result of a select statement as a value for a parameter into a stored procedure?

I was just trying this knowing that my select would return one row. Is something like this possible or will I need a temporary variable?

lets say my stored procedure took one parameter:

exec dbo.GetUserData @UserName = UserName from @MyTempTable where UserId= @UserId

Or what if the parameter expected was XML? Is there a way I can do something like

exec dbo.GetUserData @UserXml =
    select 
        case 
            when @val = 1 then '1' 
            when @val = 0 then '0' 
            else NULL 
        end as '@MyId',
        @ThisId as '@ThisId',
        @ThatId as '@ThatId'
    FOR XML PATH('Info')

Upvotes: 0

Views: 4763

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332521

XML or otherwise, assign the value to the variable before using the variable in the EXEC call.

DECLARE @UserName [data type]

BEGIN

  SELECT @UserName = UserName 
    FROM @MyTempTable 
   WHERE UserId = @UserId

    EXEC dbo.GetUserData @UserName

END

Upvotes: 4

Rob Gray
Rob Gray

Reputation: 3266

You'll need a temporary variable.

DECLARE @username nvarchar(20) 
SELECT @username = Username FROM @MyTempTable WHERE UserId=@UserId

exec dbo.GetUserData @Username.

Upvotes: 4

Related Questions