SQL: passing string to IDS using IN clause
DECLARE @STR_IDS VARCHAR(15)
SET @STR_IDS='7,15,18'
UPDATE TBL_USERS WHERE ID IN @STR_IDS
I know the update statement is not working as the id is of type INT and I am replacing the varachar value there. How can I change the query so that it performs like it actually does?
UPDATE TBL_USERS WHERE ID IN (7,15,18)
Thanks to advace
a source to share
Op doesn't mention the database, so I'm just using SQL Server because the SQL example in the question looks like TSQL. There are many ways to split a string in SQL Server. This article covers PRO and CON for almost every method:
You need to create a split function. Here's how you can use the split function:
SELECT
*
FROM YourTable y
INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value
I prefer the number table approach for splitting a string in TSQL , but there are many ways to split strings in SQL Server, see the previous link which explains PRO and CON of each.
For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers
that contains rows 1 through 10,000:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
Once the Numbers table is set up, create this split function:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
(
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO
Now you can easily split the CSV string into a table and join or use it as you need, even from within dynamic sql. Here's how to use it from your question:
UPDATE t
SET Col1=...
FROM dbo.FN_ListToTable(',','7,15,18') dt
INNER JOIN TBL_USERS t ON CAST(dt.value AS INT)=t.id
a source to share
Stolen from here :
CREATE FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
Just do:
UPDATE tbl_users
SET ...
FROM fn_split('7,15,18', ',') q
JOIN tbl_users
ON id = CAST(q.value AS INT)
a source to share
Lately, I prefer to use custom table types to pass parameter lists, but I used this utility function:
CREATE FUNCTION [dbo].[fn_CommasToIntTable]
(
@CommaList varchar(8000)
)
RETURNS @ParsedList TABLE
(
TokenID int
)
AS
BEGIN
DECLARE @CurrentToken varchar(10)
DECLARE @Pos int
SET @CommaList = LTRIM(RTRIM(@CommaList))+ ','
SET @Pos = CHARINDEX(',', @CommaList, 1)
IF REPLACE(@CommaList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @CurrentToken = LTRIM(RTRIM(LEFT(@CommaList, @Pos - 1)))
IF @CurrentToken <> ''
BEGIN
INSERT @ParsedList (TokenID)
VALUES (CAST(@CurrentToken AS int))
END
SET @CommaList = RIGHT(@CommaList, LEN(@CommaList) - @Pos)
SET @Pos = CHARINDEX(',', @CommaList, 1)
END
END
RETURN
END
You would use it like this:
SELECT (Columns)
FROM (Table)
WHERE ID IN (SELECT TokenID FROM dbo.fn_CommasToIntTable(@idList))
a source to share