Query Problem - SQL Server Forums

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)

Query Problem

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 02:09:25

DECLARE @program_name varchar(255),
@lot_list varchar(8000),
@wafer_list varchar(8000),
@param_name varchar(255),
@hi float,
@low float

SELECT @program_name = "'mobile-05'"
SELECT @lot_list = "60, 65" and so on

SELECT @hi = hi_spec, @low = lo_spec
FROM lot JOIN wafer ON lot.lot_sequence = wafer.wafer_sequence JOIN
test_param_map ON lot.lot_sequence = test_param_map.lot_sequence
WHERE lot.program_name IN (@program_name) AND
lot.lot_sequence IN (@lot_list) AND wafer.wafer_sequence IN (@wafer_list) AND test_param_map.test_name IN (@param_name)

When I run the query it genrates error for lot_list telling
Syntax error converting the varchar value '60,65' to a column of data type int.

While I am passing integer values 60, 65. Please tell me how to remove this error

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-16 : 04:31:20

you can't use a variable in IN statement
use:
declare @sql varchar(8000)

set @sql = 'SELECT ' + @hi + '= hi_spec, ' + @low + '= lo_spec
FROM lot JOIN wafer ON lot.lot_sequence = wafer.wafer_sequence JOIN
test_param_map ON lot.lot_sequence = test_param_map.lot_sequence
WHERE lot.program_name IN (@program_name) AND
lot.lot_sequence IN (' + @lot_list + ') AND wafer.wafer_sequence IN (' + @wafer_list + ') AND test_param_map.test_name IN (' + @param_name + ')'

exec @SQL

Go with the flow & have fun! Else fight the flow :)

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-16 : 04:35:03

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources