I'm passing a list of int's (comma separated)
ie. 1, 2, 3, 4
to my sp. But I'm getting an error because the list is a string, and I'm comparing to an int field. Is there a way for me to convert the list to int, without using a user created function?
Note: employeeID is INT
declare @intArray varchar(200)
SELECT *
FROM tbl_Employee
WHERE employeeID IN ( @intArray )
The error is "Cannot convert type varchar to int"
If you are using sql server 2016 and above then use STRING_SPLIT
declare @intArray varchar(200)
Set @intArray = '3,4,6,7'
SELECT *
FROM tbl_Employee
WHERE employeeID IN (select * from STRING_SPLIT(@intArray, ','))
compatibility_level should be 130 or upper. Read more Here.
You don't want to cast that list into an int, but into a list of ints.
There is no cast operator or function for that, but you can use dynamic SQL to get around it.
Basically you write
EXECUTE('SELECT * FROM tbl_Employee WHERE employeeID IN ('+@intArray+')')
Be aware of SQL injection attacks though!
IDs grows, the IN phrase become increasingly ineffective. In 2005/2008, EXISTS is preferred. Also, dynamic SQL can't be cached, so the execution never gets faster as it does with other statements.
EXISTS is not exactly practical here, I simply wanted to point out that it is preferred if the table or IN list is large. That is good to learn that SQL does cache plans for dynamic SQL (for each change). But I think this caching is largely discountable since the query will change frequently. If not, why would you choose to use dynamic SQL?
You're trying to convert not only a string to int, but several ints into one. Do you expect that your SELECT will return all employee's with and ID listed in the array?
I realize that you wanted to do this without a function. However, this is how I currently do it and it works great. Take what you will from my answer.
This code uses a while loop which could likely be improved to a recursive CTE if you are in SQL 2005/2008. You can use the output of the function as a table that you INNER JOIN to which will allow you to filter very quickly.
/*
************************************************************************************************************************
Name: ConvertDelimitedListIntoTable
Description: Converts a list of delimited values into a table for use like a dynamic IN statment
Modification History
Date Author Description
========== ============ ====================================
2009-01-31 B. Williams Initial Creation
************************************************************************************************************************
*/
ALTER FUNCTION [dbo].[ConvertDelimitedListIntoTable] (
@list NVARCHAR(MAX) ,@delimiter CHAR(1) )
RETURNS @table TABLE (
item VARCHAR(255) NOT NULL )
AS
BEGIN
DECLARE @pos INT ,@nextpos INT ,@valuelen INT
SELECT @pos = 0 ,@nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = CHARINDEX(@delimiter,@list,@pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos
ELSE LEN(@list) + 1
END - @pos - 1
INSERT @table ( item )
VALUES ( CONVERT(INT,SUBSTRING(@list,@pos + 1,@valuelen)) )
SELECT @pos = @nextpos
END
DELETE FROM @table
WHERE item = ''
RETURN
END
Use:
DECLARE @intArray varchar(200)
SELECT *
FROM tbl_Employee e
INNER JOIN dbo.ConvertDelimitedListIntoTable(@intArray,',') arr
ON e.EmployeeID = arr.Item
There may also be a quick way to do this with a tally table.
Instead of, Create user defined SQL function:
CREATE FUNCTION [dbo].[CustomStringSplit] ( @Line nvarchar(MAX), @SplitOn nvarchar(5) = '|' ) RETURNS @RtnValue table ( Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, Data nvarchar(100) NOT NULL ) AS BEGIN IF @Line IS NULL RETURN
DECLARE @split_on_len INT = LEN(@SplitOn)
DECLARE @start_at INT = 1
DECLARE @end_at INT
DECLARE @data_len INT
WHILE 1=1
BEGIN
SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at)
SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END
INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
IF @end_at = 0 BREAK;
SET @start_at = @end_at + @split_on_len
END
RETURN
END
