How to Used mutiple values in dynamic sql

Question

Post reply

How to Used mutiple values in dynamic sql

somansam

SSC Veteran

Points: 280
More actions
June 19, 2010 at 3:16 am

#236797

Hi I need to pass the varchar string in dynamic sql.

e.g.

I have declare @val = 'Sam,Joe'

I need to used this above values in where caluse like below

Select * from EMPINFO

where empnm in ('SAM','JOE')

How do i split and used mutiple values in dynamic sql

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

WayneS SSC Guru Points: 95405 More actions · Answer 2

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://qa.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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2