How to assign column value to a variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to assign column value to a variable

Author

Topic

ikhuram
Starting Member

28 Posts

Posted - 2004-06-01 : 08:38:56

How to assign column value to a variable in dynamic SQL?

SET QUOTED_IDENTIFIER OFF
DECLARE @sql varchar(8000),
@program_name varchar(255),
@lot_id varchar(255),
@wafer_id varchar(255),
@wafer_sequence int,
@bin_count numeric(18),
@wafer_count numeric(18),
@col_name varchar(255),
@col_data numeric(18)

select @program_name = 'mobile-05'
select @lot_id = 'GAL-LOT'
select @wafer_id = 'GAL-LOT-03'
select @wafer_sequence = 69
select @col_name = 'Bin1'

select @sql = "select @col_data="+@col_name+" from ##temp"+
" WHERE program_name = '"+@program_name+"'" +"AND lot_id = '"+@lot_id+"'"+
" AND wafer_id = '"+@wafer_id+"'"+" AND wafer_sequence = "+cast(@wafer_sequence as varchar(100))
print @sql
exec(@sql)
print @col_data

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-01 : 08:42:30

You have to declare @col_data inside the dynamic sql. You will also have to do the print inside of it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ikhuram
Starting Member

28 Posts

Posted - 2004-06-01 : 08:52:10

I have done like this but it does not print the value of @col_data.
Also will @col_data retain its value after execution of SQL?

SET QUOTED_IDENTIFIER OFF
DECLARE @sql varchar(8000),
@program_name varchar(255),
@lot_id varchar(255),
@wafer_id varchar(255),
@wafer_sequence int,
@bin_count numeric(18),
@wafer_count numeric(18),
@col_name varchar(255),
@col_data numeric(18)

select @program_name = 'mobile-05'
select @lot_id = 'GAL-LOT'
select @wafer_id = 'GAL-LOT-03'
select @wafer_sequence = 69
select @col_name = 'Bin1'

select @sql = "declare @col_data numeric(18) select "+@col_name+" from ##temp"+
" WHERE program_name = '"+@program_name+"'" +"AND lot_id = '"+@lot_id+"'"+
" AND wafer_id = '"+@wafer_id+"'"+" AND wafer_sequence = "+cast(@wafer_sequence as varchar(100))+" print @col_data"
print @sql
exec(@sql)
print @col_data

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-01 : 08:54:49

First, are you saying that you will only return 1 row?

If not then you'll only get the last value from the SET

Second, your query is pretty selective...except for the column name. Why is that? You seem to know exactly what your after...why not the column.

Third, to do what you want, you need the dynamic sql to put the reults to a table...the select the value from the table to a local variable...

Brett

8-)

ikhuram
Starting Member

28 Posts

Posted - 2004-06-01 : 08:55:11

yes its printing the value, but I want to use is value after execution of SQL, how is it possible?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-01 : 09:16:16

Oh, it's printing @col_data. You aren't putting anything in @col_data for it to print. Look at your code again. If you are wanting to reuse this, you need to run your little select results into a temp table. You can then query the temp table to populate the @col_data and use it however you want.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources