Dynamic T-SQL Update Query Syntax Help

[YaBabyYa]

Hello,

I have an update query which is being created dynamically.

The update query needs to select a value from a temporary table and use that value to make the update.

If the value was in a variable @var_val the query is

Update <Table_Name> Set Column_Name = @var_val where Column_Name > @var_val

However, @var_val is contained in a table.

I am trying to select the table. The actual statement is:


***********************************************************

Update ##Out_Test_word28July2007205415483137 Set e = Select var_val from ##STVR_FLOAT_Test_word28July2007205415483137 where e > Select var_val from ##STVR_FLOAT_Test_word28July2007205415483137



***********************************************************


This is giving me a the problem. Can someone help me wit the syntax please.

Thanks.

 

[YaBabyYa]

I have checked all the tables. They are being created properly. The Update query is complaining at the Select line

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.


I have tested:

Select var_val from ##STVR_FLOAT_Test_word28July2007205415483137

returns a value


Select * from ##Out_Test_word28July2007205415483137


returns multiple rows and columns and column 'e' is one of them.

 

[YaBabyYa]

Is there a way to save the information temporarily into a variable?

@my_var = Exec ( Select var_val from ## .. )

Exec (Update Table_name Set E = @my_var where E > @my_var' )

 

[YaBabyYa]

Hello,

I am trying to create a dynamic T-SQL update query. The query syntax is wrong and the main problem is that I can't retrieve a value from a global table to be used in the Update query.

I have tried looking at several forums and places for a solution.

I was reading somewhere (I can't find the link any more) there is a varbinary value that is associated with the connection or session that the stored proc is executing from, I was thinking of using this value to read the data from the table.

Can anyone point me in the right direction.

Thanks.

 

[ESquared]

First of all, you have a space between your pound signs. That won't work.

Second, I posted in your other thread similar to this one about how you can avoid a global temporary table simply by creating the table outside of dynamic SQL first and then dropping/recreating it or altering it inside of dynamic SQL.

Third, you can get variables out of dynamic SQL using sp_executesql:

DECLARE @readvalue varchar(100
exec sp_executesql N'select @val = col from table', N'@val varchar(100) OUTPUT', @readvalue OUTPUT
SELECT @readvalue

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]