need help with T-SQL Syntax
-
Hi
I am passing a paramter to a sp which is a variable list of numbers (ie. '1234,1245,5678').
I want to use this parameter in a comparison statement such as
select field2 from table1 where field1 in (@parameter)
not sure how to do this.
Any ideas would be greated appreciated.
Thanks for you help.
-
If performance is not an issue, you can do this like :
select field2 from table1 where ','+@parameter+',' like '%,' + field1 + ',%'
If field1 is not a character type, you should convert it....
I tested using this query :
create table table1( field1 varchar(1024),field2 int)
declare @parameter varchar(1024)
insert table1 values ( '1234',1)
insert table1 values ( '1245',2)
insert table1 values ( '5678',3)
insert table1 values ( '5487',4)
set @parameter = '1234,1245,5678'
select field2 from table1 where ','+@parameter+',' like '%,' + field1 + ',%'
-
Hi,
there ist another way that's even quite fast, as long as the parameter string is not extremly lenghty.
CHARINDEX returns the first occurrance of your TableID within the parameter string.
To make sure this works, you must add another comma at the beginning and the end of your parameter string and it may not contain blanks between the commas and the IDs:
CREATE FUNCTION dbo.fnMyFunc
(@Parm varchar(2000) = NULL)
...
DECLARE @ParameterIDs VARCHAR(2000)
IF (@Parm IS NULL)
SET @ParameterIDs = '0'
ELSE
SET @ParameterIDs = ',' + REPLACE(@Parm, ' ', '') + ','
...
SELECT ...
WHERE CHARINDEX(',' + CAST(TableID AS VARCHAR) + ',', @ParameterIDs) > 0
...
Hope this helps.
-
I had to do something similar recently and created the select as dynamic sql, selecting the result into a temp table. You can then use a standard select to get the data out of the tmp table.
SET @p_Query = '
INSERT INTO #tmp_table (field1)
SELECT field2 from table1 where field1 in (' + @parameter+ ')'
END
EXEC (@p_Query)
Select field1 from #tmp_table
-
#temp_table will not work, as the table will be disappeared after the exec. a ##temp_table will work, but you have to use transactions, as the ##temp_table can be used by everyone ...
Here are the 3 solutions, depending of what you need to do with the result of the select :
-- Prepare test environment
set nocount on
if object_id('dbo.table1') is not null drop table dbo.table1
create table dbo.table1( field1 varchar(1024),field2 int)
insert dbo.table1 values ( '1234',1)
insert dbo.table1 values ( '1245',2)
insert dbo.table1 values ( '5678',3)
insert dbo.table1 values ( '5487',4)
insert dbo.table1 values ( '1234',5)
declare @parameter varchar(1024)
set @parameter = '1234,1245,5678'
-- method 1 :
select field2 from dbo.table1 where ','+@parameter+',' like '%,' + field1 + ',%'
-- method 2 : dynamic sql
declare @query nvarchar(1024)
set @query = 'select field2 from dbo.table1 where field1 in (' + @parameter + ')'
exec sp_executesql @query
-- method 3 : if you need the results in a temp table
begin tran
if object_id('temppdb..##work') is not null drop table ##work
set @query = 'select field2 into ##work from dbo.table1 where field1 in (' + @parameter + ')'
exec sp_executesql @query
select * from ##work
commit tran
-
Thanks for all your replies. I'll try them and see what I can do.
thanks again.
-
The simple solution we used was to add a user-defined function which returns a table [ ufn_GetIDs(@inputstring varchar(7000)) ] Then, in sp, you can do
-------------------------------------------------
create proc (@inputpara varchar(7000))
as
select field2 from tableA where
field1 in ( select ID from ufn_GetIDs(@inputpara) )
-----------------------------------------------
Here is our implementation of [ufn_GetIDs]
=============================
CREATE function dbo.ufn_GetIDs(@IDArray varchar(7000))
RETURNs @MyTable TABLE(ID int)
as
BEGIN
declare @len int
set @len = len(@IDArray)
declare @FirstIndex int
declare @LastIndex int
set @FirstIndex = 1
while (@FirstIndex <= @len)
BEGIN
select @LastIndex = CHARINDEX(',', @IDArray, @FirstIndex)
if (@LastIndex = 0)
begin
insert into @MyTable select cast(substring(@IDArray, @FirstIndex, @len) as int)
select @FirstIndex = @len+1
end
ELSE
begin
if (@LastIndex > @FirstIndex)
insert into @MyTable select cast(substring(@IDArray, @FirstIndex, @LastIndex-@FirstIndex) as int)
select @FirstIndex = @LastIndex + 1
end
END
return
END
-
Joe,
I could not get your query to run on MS SQL 2K. There were problems with the substring functions (From..For...), plus problems using || (is this two bitwise ORs).
Why is this? Is your syntax wrong or is this a Microsoft issue? I'm interested in your solution, as it is set based.
cl
Signature is NULL
-
Well, I thought I'd be original. I haven't read everyone's post but I think I have the most unique Idea for going about doing this. Also, it works for any Table!
First, Script this Function into your Database. This function will return your Delimieted List as a Sinlge Column Table of Type VarChar. If you're thinking "Why Varchar?" then you should know that even a Numeric Column can be searched using a Varchar data type.
CREATE FUNCTION dbo.udf_GetDelimetedValues
(
@Values VarChar(2000), @Delimeter VarChar(1) = ','
)
RETURNS
@Table Table(Value VarChar(50))
AS
BEGIN
-- Variables
Declare @Position SmallInt
Set @Position = 0
If (Right(@Values, 1) <> @Delimeter) Set @Values = @Values + @Delimeter
While (CharIndex(@Delimeter, @Values, @Position + 1) > 0)
BEGIN
Insert Into @Table
Values(
LTrim(RTrim(SubString(@Values, @Position, CharIndex(@Delimeter, @Values, @Position + 1) - @Position)))
)
Set @Position = CharIndex(@Delimeter, @Values, @Position + 1) + 1
END
RETURN
END
Now, to use this is simple.
Select * From MyTable Where ID IN(Select * From dbo.udf_GetDelimetedValues('1,2,3,4,5,6,7,8,9'))Select * From Users Where FirstName IN(Select * From dbo.udf_GetDelimetedValues('Billy,Bob,Jim,Joe,Marry'))There ya go! Enjoy!
-
Here is an article by Erland Sommarskog, SQL Server MVP, that shows several solutions to this problem, comparing performance, ease of use and other aspects:
http://www.sommarskog.se/arrays-in-sql.html
Razvan
-
For efficient processing this is best done using a table rather than comma delimited string.
If you call the sp from t-sql then create a table variable and pass that instead.
I you call the sp from a client such as web page or vb/c# then either make multiple calls and combine the results in the client or insert parameters into a temp table and then call the sp without parameters.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply