June 19, 2010 at 4:00 am
I am not sure what you mean by dynamic sql in your case.
Dynamic sql is the way to execute sql from a given string. Achieved by using EXECUTE(@SQL) function or using sp_executesql.
For you case the simple (not the best) code would look something like:
declare @sql nvarchar(8000)
set @sql = N'Select * from EMPINFO where empnm in (''' +
REPLACE(@val,',',''',''') +''' )'
EXECUTE(@sql)
The above should work for given input example, however it has some limitations (eg. if your string has the space next to comma, it will not behave properly).
The best (and more elegant) way would be creating the split table-valued function which will split your comma separated string into table of values (you can find example of splitting string to table on this site), then, in your dynamic sql you would simply join to your table-valued function.
June 19, 2010 at 5:06 am
This method utilized the DelimitedSplit function. You really should read the article mentioned in the remarks, and build your very own permanent tally table - with a properly built table with a tight clustered index (fillfactor=100), this will be even faster.
CREATE FUNCTION [dbo].[DelimitedSplit] (
@list varchar(max),
@Delimiter char(1)
)
RETURNS TABLE
AS
RETURN
-- first, need to break down into separate items. See Jeff Moden's article:
-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/
-- for how a tally table can split strings apart.
WITH
-- if you have your own tally table, omit these CTEs.
Tens (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 ),
Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
-- if you have your own tally table, omit the above CTEs and continue from here.
ItemSplit (ItemOrder, Item) AS (
SELECT N,
RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,
CHARINDEX(@Delimiter,@Delimiter + @list + @Delimiter,N+1)-N-1)))
FROM Tally --<<<<<< if you have your own tally table, put the schema qualifier here >>>>>> --
WHERE N < LEN(@Delimiter + @list + @Delimiter)
AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = @Delimiter
)
SELECT ItemID = ROW_NUMBER() OVER (ORDER BY ItemOrder),
Item
FROM ItemSplit
GO
Now, to show how to utilize it:
DECLARE @test-2 TABLE (
ID int IDENTITY,
Col1 varchar(max)
)
-- make some delimited data.
INSERT INTO @test-2
SELECT 'BOB,Joe,Fred,John' UNION ALL
SELECT 'Jim,Billy,Greg,Laura' UNION ALL
SELECT 'Tim,Clinton,Sarah,Amanda' UNION ALL
SELECT 'Sam,Sandy'
-- now, rip it all apart
SELECT OriginalRow = t1.ID,
FirstSplitID = ds.ItemID,
ds.Item
FROM @test-2 t1
CROSS APPLY dbo.DelimitedSplit(t1.Col1, ',') ds
ORDER BY OriginalRow, FirstSplitID
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply