June 18, 2012 at 10:26 am
Hello All,
I have a query where I need to use an "IN" clause like:
AND e.Id IN (@EmpId)
but it gives me the following error
Conversion failed when converting the varchar value '162638,152866,147997,166881,166882,147979,137371,111381,150385,195020,156623,181321,88895,128844,104505,124932,105633,76845,107936,138734,137327,45637,174646,141378' to data type int.
It is a huge query and I do not want to do a dynamic sql. What are my options?
Thanks.
June 18, 2012 at 10:48 am
You can't use a variable like that. You should look at the link in my signature for splitting a string.
--EDIT--
Hint -- your query would end up something like this.
select * from Employee d
join dbo.DelimitedSplit8K(@EmpID, ',') s on s.Item = e.Id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 12:32 am
June 19, 2012 at 12:52 am
ramadesai108
Depending on how you generate the values for the @EmpID, it may be worth looking at declaring it as a table and populating it with a select statement.
Then you could use more tradition TSQL syntax if you do not want to use the string splitter function.
example to create & populate a TableVariable
DECLARE @EmpId AS TABLE (n int)
INSERT into @EmpId
SELECT 162638 UNION ALL
SELECT 152866 UNION ALL
SELECT 147997 UNION ALL
SELECT 166881 UNION ALL
SELECT 166882 UNION ALL
SELECT 147979 UNION ALL
SELECT 137371 UNION ALL
SELECT 111381 UNION ALL
SELECT 150385 UNION ALL
SELECT 195020 UNION ALL
SELECT 156623 UNION ALL
SELECT 181321 UNION ALL
SELECT 88895 UNION ALL
SELECT 128844 UNION ALL
SELECT 104505 UNION ALL
SELECT 124932 UNION ALL
SELECT 105633 UNION ALL
SELECT 76845 UNION ALL
SELECT 107936 UNION ALL
SELECT 138734 UNION ALL
SELECT 137327 UNION ALL
SELECT 45637 UNION ALL
SELECT 174646 UNION ALL
SELECT 141378
SELECT * FROM @EmpId
June 19, 2012 at 3:12 am
440692 I am just a number (6/19/2012)
ramadesai108Depending on how you generate the values for the @EmpID, it may be worth looking at declaring it as a table and populating it with a select statement.
Then you could use more tradition TSQL syntax if you do not want to use the string splitter function.
example to create & populate a TableVariable
DECLARE @EmpId AS TABLE (n int)INSERT into @EmpId
SELECT 162638 UNION ALL
SELECT 152866 UNION ALL
SELECT 147997 UNION ALL
SELECT 166881 UNION ALL
SELECT 166882 UNION ALL
SELECT 147979 UNION ALL
SELECT 137371 UNION ALL
SELECT 111381 UNION ALL
SELECT 150385 UNION ALL
SELECT 195020 UNION ALL
SELECT 156623 UNION ALL
SELECT 181321 UNION ALL
SELECT 88895 UNION ALL
SELECT 128844 UNION ALL
SELECT 104505 UNION ALL
SELECT 124932 UNION ALL
SELECT 105633 UNION ALL
SELECT 76845 UNION ALL
SELECT 107936 UNION ALL
SELECT 138734 UNION ALL
SELECT 137327 UNION ALL
SELECT 45637 UNION ALL
SELECT 174646 UNION ALL
SELECT 141378
SELECT * FROM @EmpId
If you are using a Table Variable then I think that your Query should end something like this:
AND e.Id IN (Select EmpId From @EmpId)
And not like below
AND e.Id IN (@EmpId)
But I guess the Error is not because of that. It is a conversion Error and is Occurring somewhere in your code where you are exchanging values between two variables.
Need to see a little more of your code to be sure though.
June 19, 2012 at 7:18 am
Guys the issue is very obvious from the error message posted. The OP has a long comm delimited list in a string.
declare @EmpID varchar(max) = '162638,152866,147997,166881,166882,147979,137371,111381,150385,195020,156623,181321,88895,128844,104505,124932,105633,76845,107936,138734,137327,45637,174646,141378'
Then in the where clause the Employee table has a column name Id (terrible name but that is another topic) which is a datatype int.
Here is the simplified version of the code the OP has.
declare @EmpID varchar(max) = '162638,152866,147997,166881,166882,147979,137371,111381,150385,195020,156623,181321,88895,128844,104505,124932,105633,76845,107936,138734,137327,45637,174646,141378'
create table #Employee(Id int)
select * from #Employee e
where e.Id IN (@EmpId)
So now you can see that the OP needs to split this string before it is usable, or as somebody suggested do something different with before it gets here. Unfortunately any suggestions at this point, other than splitting the string, are complete guesses because the OP has not posted enough detail for anyone to know for sure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 8:39 am
I am passing a string of comma delimited Ints to the stored procedure. Now I need to see whether e.Id is IN those INTs.
From the response I got from you guys is that I need to create a table, insert the INTs and then use it in my "IN" clause. I could use a dynamic sql but the query is very long and too complicated. Are there any other options besides using a table to put the INTs?
Thanks for your time.
June 19, 2012 at 8:49 am
ramadesai108 (6/19/2012)
I am passing a string of comma delimited Ints to the stored procedure. Now I need to see whether e.Id is IN those INTs.From the response I got from you guys is that I need to create a table, insert the INTs and then use it in my "IN" clause. I could use a dynamic sql but the query is very long and too complicated. Are there any other options besides using a table to put the INTs?
Thanks for your time.
Yes read the first response to your thread. You need to parse this string. I even provided what the basic query would look like.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 8:50 am
A few ways. . . .
Since you haven't given us any sample data or DDL, these are untested and will have to be modified for your particular situation.
Dynamic SQL version (please read up on SQL injection before you go anywhere near this!).
CREATE PROCEDURE yourSproc (@EmpID) AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT *' + CHAR(13) + CHAR(10) +
'FROM #Employee e' + CHAR(13) + CHAR(10) +
'WHERE e.Id IN ('+@EmpID+')'; --WARNING!! I'M VULNARABLE TO SQL INJECTION!!
EXECUTE sp_executesql @sql;
END
Using a string splitter: -
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
Then your actual sproc would need to reference the splitter: -
CREATE PROCEDURE yourSproc (@EmpID) AS
BEGIN
SELECT *
FROM #Employee e
CROSS APPLY dbo.DelimitedSplit8k(@EmpID,',') split
WHERE e.Id IN (split.Item);
END
See this article[/url] for how the splitter works as well as performance comparisons.
June 19, 2012 at 12:12 pm
Hello All,
I ended up doing this and it worked:
AND ',' + @EmployeeId + ',' LIKE '%,' + CAST(e.ID AS varchar(10)) + ',%'
Thank you all for your time.
June 19, 2012 at 12:26 pm
ramadesai108 (6/19/2012)
Hello All,I ended up doing this and it worked:
AND ',' + @EmployeeId + ',' LIKE '%,' + CAST(e.ID AS varchar(10)) + ',%'
Thank you all for your time.
That will probably work but this is a nonsargable solution. That means you negate any indexes because it forces a table scan. If you have a large table this will have a huge negative impact on performance. It may be that the performance is acceptable though too. Glad you figured out a solution and thanks for letting us know. We are here if you need anything else.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 8:19 pm
Oops sorry. Wrong answer.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply