Bdtjtk

I need to get a query from a database table that contains a parameter. Then use that query to update another table but I need to be able to pass another parameter to that update statement.

declare @locnum int

set @locnum = 032



declare @tempPersonID int

set @tempPersonID = 10008



declare @passwordQuery varchar(max)

set @passwordQuery = (select passwordQuery from location where locationNum = @locnum)



select @passwordQuery



update tempPerson 

set [password] = @passwordQuery

where tempPersonID = @tempPersonID



select *

from tempPerson

select @passwordQuery returns (select left(firstname,1) + left(lastname,1) + custom as [password] from tempPerson where tempPersonID = @tempPersonID). I need to able to use the @tempPersomID parameter in this query and the where statement.

Firstly, as you are updating the record that you are selecting the information from you only need the following as your @passwordQuery:

left(firstname,1) + left(lastname,1)  + [custom]

Then the following code takes your code and adapts it for dynamic SQL:

declare @locnum int = 032, @tempPersonID int = 10008, @passwordQuery varchar(max), @sql nvarchar(max), @params nvarchar(max);



select @passwordQuery = passwordQuery from [location] where locationNum = @locnum;



-- select @passwordQuery



set @sql = 'update tempPerson set [password] = ' + @passwordQuery

  + ' where tempPersonID = @tempPersonID';

set @params = '@tempPersonID int';



execute sp_executesql @sql, @params, @tempPersonID = @tempPersonID; 



select *

from tempPerson

You Can Execute Dynamic SQL Statements with parameter using either the EXEC or sp_ExecuteSQL Statements.

In your Case, The sp_ExecuteSQLseems more suitable. The Syntax for the same is as below

sp_executesql [ @stmt = ] statement  

[   

  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   

     { , [ @param1 = ] 'value1' [ ,...n ] }  

]  

So Your Entire Script can be re-written as below :

DECLARE @locnum INT

        @tempPersonID INT,

        @passwordQuery VARCHAR(MAX),

        @params VARCHAR(500),

        @ParamOut VARCHAR(500)



SELECT  

    @locnum = 032,

    @tempPersonID = 10008,

    @params = N'@tempPersonID INT,@MyPwd VARCHAR(500) OUTPUT'



SELECT

    @passwordQuery = 'SET @MyPwd = ('+passwordQuery+')'

    FROM Location 

        WHERE LocationNum = @locnum





sp_ExecuteSQL(@passwordQuery,@params,@tempPersonID,@MyPwd = @ParamOut OUTPUT )



UPDATE tempPerson 

    SET 

        [password] = @ParamOut

    WHERE tempPersonID = @tempPersonID



select *

from tempPerson