October 23, 2004 at 5:52 pm
Is it possible to use a local variable with an IN statement as follows
declare @local
set @local = <[values with single quotes separated by commas]>
select
var1
,var2
from table1
where var3 in (@local)
Every way I've tried running it i.e. assigning the local variable so it has the right number of quote marks and commas the result set is empty. I'm sensing I might have to use embedded SQL to accomplish this.
Any help would be greatly appreciated.
Thanks
g
October 23, 2004 at 9:52 pm
hey
yes its possible.u could use dynamic query...check this out....
hope u get wat u want.....else tell me i will try 2 help u out.
here reg1 is the table name
select * from reg1
collegeid deptid regis
----------- ----------- -----------
2001 1 101
2001 1 202
2001 1 303
2002 2 404
2002 2 505
2002 3 606
declare @var nvarchar(30)
declare @query nvarchar(100)
set @var='101,202,303'
set @query='select * from reg1 where regis in ('+ @var +')'
exec sp_executesql @query
Result::
collegeid deptid regis
----------- ----------- -----------
2001 1 101
2001 1 202
2001 1 303
Rajiv.
October 25, 2004 at 2:00 am
Well Sam
I would also avoid the Dynamic SQL and instead use a User defined Function which does the parsing and the loading into a table in one step. First the Create:
CREATE FUNCTION dbo.ParseDelimitedString
(
@List varchar(100), /* This can be sized as necessary for your specs and can be Nvarchar if unicode is required */
@Parser char(1)
)
RETURNS @Local table
(
Var3 varchar(10) /* Here you can set the type and size of the variable3 that you wanted to compare on */
)
AS
BEGIN /* loop until there isn't another delimiter */
While (Charindex(@Parser,@List)>0)
Begin
Insert Into @Local (Var3)
Select
Var3 = ltrim(rtrim(Substring(@List,1,Charindex(@Parser,@List)-1)))
Set @List = Substring(@List,Charindex(@Parser,@List)+len(@Parser),len(@List))
End
Insert Into @Local (Var3)
Select Var3 = ltrim(rtrim(@List))
Return
END
GO
Next some code to create a "Table1" and your original Query modified to call the Parsing Function in the IN clause:
/*create and load some test values into my table1 which for testing is a table variable */
Declare @Table1 Table (var1 int, var2 int, var3 varchar(255))
insert into @Table1 (var1, var2, var3) VALUES (1,1,'106')
insert into @Table1 (var1, var2, var3) VALUES (2,3,'105')
insert into @Table1 (var1, var2, var3) VALUES (3,4,'102')
insert into @Table1 (var1, var2, var3) VALUES (4,6,'101')
insert into @Table1 (var1, var2, var3) VALUES (7,5,'104')
insert into @Table1 (var1, var2, var3) VALUES (5,2,'103')
/* Pass in the Delimited Values */
Declare @Var3List varchar(100)
Set @Var3List = '101,102,103'
select
var1
,var2
from @table1
Where
var3 IN (Select Var3 from dbo.ParseDelimitedString(@Var3List,','))
Hope this is what you need.
Cheers,
John R. Hanson
VP Operations
MEDePass, Inc.
October 25, 2004 at 2:20 am
Another way
select var1,var2
from table1
where charindex( ',' + var3 + ',' , ',' + @local + ',' ) > 0
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply