• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

In Statement

OK, So I want to use an IN Statement against a smallint field.

How can I pass in the Paramaters so they work against a smallint field?

For example I want to pass in "1,2,3" as a paramater and have the 1,2,3 go against a field in the database that is a smallint type.

FROM Table
WHERE smallint IN(@paramater)

How can I accomplish this?

1 Solution
If a variable is used directly within a SQL - the variable is interpreted as a single string value, it does not resolve to the list.
You can build the full SQL into a variable and execute that or use a user defined function to take the string and turn into a list of values.
Example of a execute is
IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL 
	DROP TABLE #tmp_table;
CREATE TABLE #tmp_table (
	id		int not null identity(1,1),
	val   int,
	descr varchar(20)
insert into #tmp_table (val, descr) values 
(1,'test 1'),
(2,'test 2'),
(3,'test 3');

declare @list		varchar(10);
set @list = '1,2';

declare @SQL_select  nvarchar(1000);
set @SQL_select = 'select * from #tmp_table where val in ('+@list+')';

exec (@SQL_select);

Open in new window

Alpesh PatelAssistant ConsultantCommented:
FROM Table
WHERE smallint IN(@paramater)

if smallint is int field then parameter values like  1,2,3
and string then '1','2','3'
smithmrkAuthor Commented:
Thanks, I'll give it a try!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now