Problem with IN Statement in SQL Server
I currently have a a query where I pass in some delimited values as a varchar. When I go to test against this list in my Where clause SQL Server freaks out and tells me I need to have integers . Not sure what to do.
@states varchar(max)
in C# I pass this in as - @states='AL,CT,FL'
Then in my query it looks like this :
Select *
From dhpDrivers
Where dhpDriver.State in(@states)
error I get is :
Conversion failed when converting the varchar value 'AL,CT,FL' to data type int
The dhpDriver.State field is not an integer field so I'm a bit confused.Any idea what I need to do with my states list to make this work ? Any advice would be much appreciated.
Jason
@states varchar(max)
in C# I pass this in as - @states='AL,CT,FL'
Then in my query it looks like this :
Select *
From dhpDrivers
Where dhpDriver.State in(@states)
error I get is :
Conversion failed when converting the varchar value 'AL,CT,FL' to data type int
The dhpDriver.State field is not an integer field so I'm a bit confused.Any idea what I need to do with my states list to make this work ? Any advice would be much appreciated.
Jason
Paul MacDonald
Is this a stored procedure? Is the passed parameter defined as an Int?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dhpDriver.State is a char(2). This is source field I need to compare against.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is the:
-- code to insert values from @states to @statesTable
-- insert the first state into the table
INSERT INTO @statesTable
SELECT LEFT(@states, CHARINDEX(',', @states) - 1)
-- dynamically create a table of seq #s from 1 to 9999 to help parse input parameter
;WITH
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
CROSS JOIN cteDigits [100s]
CROSS JOIN cteDigits [1000s]
)
INSERT INTO @statesTable
SELECT
SUBSTRING(@states, num + 1, CHARINDEX(',', @states + ',', num + 1) - num - 1) AS state
FROM cteTally
WHERE
num BETWEEN 2 AND LEN(@states) AND
SUBSTRING(@states, num, 1) = ','
-- code to insert values from @states to @statesTable
-- insert the first state into the table
INSERT INTO @statesTable
SELECT LEFT(@states, CHARINDEX(',', @states) - 1)
-- dynamically create a table of seq #s from 1 to 9999 to help parse input parameter
;WITH
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
CROSS JOIN cteDigits [100s]
CROSS JOIN cteDigits [1000s]
)
INSERT INTO @statesTable
SELECT
SUBSTRING(@states, num + 1, CHARINDEX(',', @states + ',', num + 1) - num - 1) AS state
FROM cteTally
WHERE
num BETWEEN 2 AND LEN(@states) AND
SUBSTRING(@states, num, 1) = ','
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not necessarily for performance it isn't.
That could have awful performance, depending on the number of rows and number of values entered, and if a state index exists on the table.
That could have awful performance, depending on the number of rows and number of values entered, and if a state index exists on the table.
ok! if we have a look-up table for states we can use this format :) and still use index and performance would not be awful :)
Select d.*
From dhpDrivers d inner join dphStates s on s.state=d.state and ','+@states+',' like '%,'+s.State+',%'
Select d.*
From dhpDrivers d inner join dphStates s on s.state=d.state and ','+@states+',' like '%,'+s.State+',%'
If you could create a table that's guaranteed to hold all the state values (*), you could do this:
SELECT ...
FROM dbo.dhpDrivers d
INNER JOIN (
SELECT state
FROM dbo.dphStates
WHERE
',' + @states + ',' LIKE '%,' + state + ',%'
) AS s ON
s.state = d.state
...
(*) Sometimes "state" includes things like "DC", Canadian provinces, etc., so it's more than just a fixed list of (exactly) 50 values.
I still see that as a disadvantage over just dynamically creating a much more limited table from only the state values that are entered in the parameter for that run.
However, I'd probably change to a true temporary table, #states, rather than @statesTable, so that stats are available for SQL, so the optimizer will "know" it's a very small table.
SELECT ...
FROM dbo.dhpDrivers d
INNER JOIN (
SELECT state
FROM dbo.dphStates
WHERE
',' + @states + ',' LIKE '%,' + state + ',%'
) AS s ON
s.state = d.state
...
(*) Sometimes "state" includes things like "DC", Canadian provinces, etc., so it's more than just a fixed list of (exactly) 50 values.
I still see that as a disadvantage over just dynamically creating a much more limited table from only the state values that are entered in the parameter for that run.
However, I'd probably change to a true temporary table, #states, rather than @statesTable, so that stats are available for SQL, so the optimizer will "know" it's a very small table.
ScottPletcher, is your last post any different then my post @ 37744667 :)
Yes. I reduced the number of rows as much as possible BEFORE joining to the main table.
Again, I still think that is an inferior approach to creating a temp table of the actual values passed in, since very few values are likely to be passed in for a typical run.
Again, I still think that is an inferior approach to creating a temp table of the actual values passed in, since very few values are likely to be passed in for a typical run.
ASKER
So I created a User Defined function called CSVStringToTable to pump out my @states into a single column table.
So when @states = 'AL,OH,PA'
and I call the function like so : CSVStringToTable(@states)
I get a single column table (column name is ItemList) with 3 records. One record per state.
so in my stored proc I create a temp table like so :
SELECT * INTO #TEMP_StateList34 FROM CSVStringToTable(@states)
this lets lets me do my in function from my where clause:
WHERE dhpDriver.State in(SELECT Itemlist from #TEMP_StateList34)
AS Kdo mentioned earlier the problem is that you really need something that sql server sees as an list and not a string. Thank you for all the help.
So when @states = 'AL,OH,PA'
and I call the function like so : CSVStringToTable(@states)
I get a single column table (column name is ItemList) with 3 records. One record per state.
so in my stored proc I create a temp table like so :
SELECT * INTO #TEMP_StateList34 FROM CSVStringToTable(@states)
this lets lets me do my in function from my where clause:
WHERE dhpDriver.State in(SELECT Itemlist from #TEMP_StateList34)
AS Kdo mentioned earlier the problem is that you really need something that sql server sees as an list and not a string. Thank you for all the help.
Looks good.
A primary key on the temp table should help SQL perform (slightly) better. The more states there are in the table, the more they key should help.
A primary key on the temp table should help SQL perform (slightly) better. The more states there are in the table, the more they key should help.