Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

In clause is giving problem in dynamic sql. Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 12:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 205, Visits: 518
Hi All,

I am trying to run below simple query. I am trying to pass comma separated string into my query but its not working. if i pass single value then it works fine. Pklease guide.

Declare @DayName varchar(100)
--set @DayName = 'Monday'
Set @DayName = '''Monday''' +','+ '''Sunday''' +','+' ''Friday'''
select * from tbldays where weekday in(@DayName)
select @DayName


thanks
Abhas.
Post #1449055
Posted Friday, May 3, 2013 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
abhas (5/3/2013)
Hi All,

I am trying to run below simple query. I am trying to pass comma separated string into my query but its not working. if i pass single value then it works fine. Pklease guide.

Declare @DayName varchar(100)
--set @DayName = 'Monday'
Set @DayName = '''Monday''' +','+ '''Sunday''' +','+' ''Friday'''
select * from tbldays where weekday in(@DayName)
select @DayName


thanks
Abhas.

SQL is a declarative language, not an interpreted language so when your code runs here is what is actually run:

select * from tbldays where weekday in('''Monday'',''Sunday'',''Friday''')

Which obviously will return no results.

Is this for code that will run within a stored procedure? If so, then look into passing an XML Document to the stored procedure instead of a delimited string. Then you can shred the XML document with normal T-SQL commands and join to it from your existing query.

If you still want to use a delimited string because XML is not your strength or because of client limitations there are string splitting functions available that will turn your delimited string into a table that you can join to in your query. Here is a good start with a T-SQL and SQLCLR option: http://www.sqlservercentral.com/articles/Tally+Table/72993/


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1449331
Posted Friday, May 3, 2013 1:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Something like this is what you need. The splitter function allows you to do a set-based join to get your query results.


--sample date table

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[WeekDay] NVARCHAR(50) NULL,
PRIMARY KEY (ID))

INSERT INTO #TempTable
SELECT 'Monday' UNION ALL
SELECT 'Tuesday' UNION ALL
SELECT 'Wednesday' UNION ALL
SELECT 'Thursday' UNION ALL
SELECT 'Friday'



DECLARE @DayName VARCHAR(100)
SET @DayName = 'Monday,Sunday,Friday'

SELECT
[WeekDay]
FROM
#TempTable AS tt
INNER JOIN
dbo.DelimitedSplit8K(@DayName,',') AS dsk
ON tt.WeekDay = dsk.Item


Output:


WeekDay
Monday
Friday


 
Post #1449345
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse