May 2, 2005 at 12:27 pm
I have a list of employeeids in a comma seperated string (688,708,87,88). I'd like to be able to pass this my query to SQL and have it return all of records out of my table 'employees' that match that using the WHERE IN conditional. Problem is that the employee column is an integer and the list is a string. SQL Server does not like looking in a string for an integer. I've tried casting the integer as a CHAR or VARCHAR and I never get any matches or records returned.
Any ideas? Your help by reading is already appreciated.
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
May 2, 2005 at 12:40 pm
Declare @list varchar(200)
set @list = '688,708,87,88'
Select * from Employees
where charindex(','+ cast(empID as varchar(10)) +',', ','+@List+',') > 0
* Noel
May 2, 2005 at 12:40 pm
This is 1 of the most discussed topics here. Basically when you pass the (333, 222, 333, ) etc in as a parameter SQL "sees" it as the LITERAL "(333, 222, 333, )" and not as an ARRAY.
You can either A. parse the string into a table variable, temp table, or real table and then start process or B. search around here for a script to parse the string.... that you would call like a function.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 2, 2005 at 12:45 pm
This will run much faster (the longer the list, the faster) :
Declare @list varchar(200)
set @list = '688,708,87,88'
Select * from Employees
where empID in (Select Element from dbo.Split(@list, ','))
CREATE FUNCTION [dbo].[Split] (@vcDelimitedString nVarChar(4000),
@vcDelimiternVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString- The string to be split
@vcDelimiter- String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR:Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------
***************************************************************************/
RETURNS @tblArray TABLE
(
ElementIDsmallintIDENTITY(1,1) not null primary key, --Array index
ElementnVarChar(1200)null--Array element contents
)
AS
BEGIN
DECLARE
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint
SET @siDelSize= LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
There are also set based approch to this problem that can run 10s times faster than this function... msg me if you need that much performance out of this proc.
May 2, 2005 at 12:54 pm
Thanks all for the response.
Should this work with a view also? Here is the query I want it to go into and it still will not work with the new SPLIT function above:
SELECT *
FROM qryActivityCostReportPARAMETERBASED
WHERE (StartDate BETWEEN @sdate AND @edate) AND (Employeeid IN
(SELECT element
FROM dbo.Split(@employeeids, ',')))
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
May 2, 2005 at 1:00 pm
You can't have a parameter in a view so no... By I suspect another problem. What error r u getting from sql server? Or Are you just not getting the expected results?
try this :
Select Element from dbo.Split ('a,b,d,r', ',')
May 2, 2005 at 1:04 pm
I tried that split you gave me and like I thought it is splitting OK. I get a vertical array.
I should clarify what I am trying to do. The select is selecting from a view. The parameters you see (@sdate, etc) are for Reporting Services. The parameters are getting passed in OK to the query - for example I can pass dates in edata and sdate. And when I hard code the employeeids it works OK also.
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
May 2, 2005 at 1:24 pm
Then I guess that the problem comes from the employes list... can we see how you populate that variable?
May 2, 2005 at 1:32 pm
I must have had another issue. It seems to be working now.
Thank you all for the function, etc. I learned quite a bit - for example I did not know you could define your own SQL functions.
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
May 2, 2005 at 2:05 pm
I'll let this sink in before I show you the set-based solution.
You might consider it seriously if this report is run very often.. or if the in list is very large.
May 2, 2005 at 2:12 pm
Thanks.
I don't plan on the in list being more than 50 people. But who knows.
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
May 2, 2005 at 2:33 pm
Actually, I would like to suggest you use a stored procedure instead of a view if at all possible.
* Noel
May 3, 2005 at 1:11 am
See if this helps: http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 3, 2005 at 11:34 am
As I've been asked for the set based solution here it comes :
--I use this table for many other string operations as well
GO
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, PkNumber + len(@vcDelimiter)) - PkNumber - len(@vcDelimiter)) as EachID
FROM dbo.Numbers
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
STOP
I also have a startup proc that pins the numbers table into memory (takes 32K)... so the access is always super fast.
USE IDEAL
CREATE PROCEDURE [dbo].[AutoExecPinTableNumbers]
AS
SET NOCOUNT ON
EXEC sp_tableoption 'Numbers', 'pintable', 'true'
SET NOCOUNT OFF
GO
USE MASTER
GO
CREATE PROCEDURE [dbo].[sp_AutoEXEC]
AS
SET NOCOUNT ON
EXEC Ideal.dbo.AutoExecPinTableNumbers
SET NOCOUNT OFF
GO
exec sp_procoption N'sp_AutoEXEC', N'startup', N'true'
GO
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply