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 03, 2013 12:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 172, Visits: 450
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 03, 2013 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1449331
Posted Friday, May 03, 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