Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wilshaw
Starting Member
10 Posts |
 Posted - 2012-01-23 : 14:13:30
|
Hi - been struggling with this for hours now....One of the parameters I want to query in this dynamic sql is a comma delimited string. I'm trying to query the parameter in an 'IN' statement. @Airport nVarChar(200) = NULL quote:
//@Airport value will be similar to:- value1, value2, value3, value4
DECLARE @sSql nvarchar(500), @paramlist nvarchar(200) SET @sSQL = 'SELECT jobTitle FROM Location WHERE locationActive = 1 ' IF @Airport <> ' ' SELECT @sSql = @sSql + ' AND airport IN (@_airport) ' SELECT @paramlist = '@_airport nvarchar(200)' EXEC sp_executesql @sSql, @paramlist, @AirportI've tried encapsulating each variable in the string with a single quote (i.e. 'variable1', 'variable2'), 2 single quotes etc. but still nothing. Not even sure this is possible, but would appreciate some guidance. I would rather keep away from a LIKE query as the database is quite large and performance is a key factor.Many thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-23 : 14:17:05
|
| Take a look at this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170528 It is not exactly identical to your problem, but it discusses how to handle the comma-separated values. Perhaps you can build your dynamic SQL to use one of those approaches? |
 |
|
|
wilshaw
Starting Member
10 Posts |
Posted - 2012-01-23 : 16:23:42
|
| Hi - thanks for that.The thread you pointed me to was a bit too advanced for my little SQL knowledge, but having understood what it was saying, took me to this article on 4 Guys which I followed better (for my level anyway).http://www.4guysfromrolla.com/webtech/031004-1.shtml |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-23 : 16:48:25
|
| glad to help. The code I posted was supposed to help show how to use a udf splitter* so I guess at least it helped a little.there are many different approaches for the splitting udf. the WHILE loop one is one of the slowest. However, unless it's a problem then it's a moot point, if all you ever need to do is split a list of 4 - 20 elements then it just won't matter.Keep figuring things out for yourself and you'll do fine.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-24 : 00:09:11
|
| Go for table valued parameters and I bet you wont regret it.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-01-24 : 00:51:42
|
| You say that, but I've been burned with this approach before. In SQL Server a UDF returning a table always has a cardinality of 1. Depending on the data, this can cause the optimiser make some wrong decisions when that's significantly wrong.Not saying it will, but it might.The solution to this was to copy the UDF to a temp table, which sucks, but it seemed to get the card correct after that. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-01-24 : 00:56:33
|
| Not sure what a UDF has to do with a table valued parameters ?After Monday and Tuesday even the calendar says W T F .... |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-24 : 04:24:10
|
quote:
Originally posted by Sachin.Nand
Go for table valued parameters and I bet you wont regret it.After Monday and Tuesday even the calendar says W T F ....
++Love em, just wish they could be written to as well as read from.wilshaw. You may want to read this:http://www.sommarskog.se/arrays-in-sql-2008.htmlCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Dynamic SQL