October 11, 2011 at 1:32 pm
Hello, everyone,
I'm pretty sure I'm missing something simple here, but its not easy to search anywhere for a keyword of "IN".....
This is a query against the LiteSpeedLocal database, so for you Quest shops out there, it's easy to test. I'm trying to write a query/stored proc that will take in 'Completed', Failed' or 'All' to report on backup history. I created a variable and tried passing it to the IN portion of the query. If the variable is INT, it works. If the variable is VARCHAR and has only one value set, it works fine. If I set it to 2 values, it fails. I know I have the quotes correct. If I output the variable, copy and paste it into the query where the variable is, it works fine.
Am I missing something simple here? I included the code below. Thanks....
DECLARE @StatusName NVARCHAR(100)
SELECT @StatusName = 'Completed'
-- returns data
SELECT 'Varchar with one value'
SELECT StatusTypeID FROM LiteSpeedLocal.dbo.LitespeedStatusType WHERE StatusName IN (@StatusName)
-- 2 parameters returns no data....
SELECT @StatusName = '''Completed'', ''Failed'''
SELECT 'Varchar with two comma-separated values'
SELECT StatusTypeID FROM LiteSpeedLocal.dbo.LitespeedStatusType WHERE StatusName IN (@StatusName)
-- copy/paste of the variable output - this query works fine
--SELECT StatusTypeID FROM LiteSpeedLocal.dbo.LitespeedStatusType WHERE StatusName IN ('Completed', 'Failed')
October 11, 2011 at 2:29 pm
you'll need to grab one of the many split(0 functions that are in the scripts section here on SSC;
there is a big difference between a varchar that happens to contain a comma, and a list of values.
you need a table value function that changes a list like that into a table:
select Item From dbo.DelimitedSplit8K('my,comma,delimited,list',',')
here's one of many examples:
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(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
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
Lowell
October 12, 2011 at 10:45 pm
@pveilleux: [StatusName] contains data like 'Completed' or 'Failed'. The reason why you are not getting the data like ['Completed', 'Failed'] because there is no data in column [StatusName] with both the status. You need to use table value function which converts comma separated list to rows.
Thanks
October 13, 2011 at 7:24 am
Wow......I never thought I'd be attacked for asking what I thought was a simple question. Allow me to address your points one by one here, just so there is no misunderstanding.
CELKO (10/12/2011)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
I deeply apologize for not knowing all the ISO standards off the top of my head here. I'm amazed I've been able to stay employed in the database world for 15 years without knowing these.
CELKO (10/12/2011)
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
You're probably right, I could have posted more info for folks, but at least one person was able to understand what I was asking and provide a reasonable solution to my problem. That is what I was looking for, not instructions on how to post in a forum.
CELKO (10/12/2011)
>> Am I missing something simple here? I included the code below. Thanks... <<
you have no idea how to do data model. There is no such nightmare as a “status_type_name_id_value_nAME” in RDBMS. The basics, please! The proper form is “[role>_]<attribute>_<property>” which means that absurdities like your @status_name should have been “something_status” or “@something_name” in a correct model. Think about how silly your mix of meta-data and data is.
Who are you to determine what my data modeling capabilities are? You have no idea what my background and experience is. Asking a question about a nuance of the IN clause has absolutely no bearing on what my abilities as a data modeler are. Besides, the column name you use as an example (status_type_name_id_value_nAME) is not even in the query. Honestly, I can't even follow what you are trying to say in the above paragraph, so I'm just going to let that go.
CELKO (10/12/2011)
Your next conceptual problem is that you have no idea what First Normal form is. Well, it is is the foundation of all RDBMS. And it also means that all values are scalars and that we do not have CSV, arrays, linked lists, or any other data structure. You think that you can use a CSV in SQL! Wrong, wrong, wrong! Say three “Hail Codds” and stand in the corner.
Your conceptual problem is that you are reading things that aren't there and NOT reading the things that are. How can you tell whether or not my tables are in first normal form or not? Didn't you criticize me above for not posting DDL? How can you possibly tell which form the tables are normalized to if I didn't post DDL?
The best part about your post is that you get all over me for not being a good data modeler and not understanding conceptual design, but you missed the first sentence of where I explained my question. Allow me to quote it:
This is a query against the LiteSpeedLocal database, so for you Quest shops out there, it's easy to test.
It's a vendor product. I didn't create it. I didn't alter it. I had nothing to do with it. I'm just querying it. So, if you want to bust my chops about how a third party designed their database, well, you're barking up the wrong tree, pal. Maybe you should have had one of your editors read my original post and then yours before you posted.
And because it's a vendor database for a widely used product (SQL LiteSpeed, for those of you keeping track), I didn't feel the need to post DDL or example data. The table I'm querying is vendor supplied data, 3 rows of data, and I'm selecting 2 of them.
October 13, 2011 at 7:43 am
Nice rant.
@jc-2 (not short for Jesus-Christ).
You were doing some headway lately but now you're getting back to your old habits. Plz keep up the <previous> good work.
October 13, 2011 at 8:02 am
Ninja's_RGR'us (10/13/2011)
@pveilleuxNice rant.
@jc-2 (not short for Jesus-Christ).
You were doing some headway lately but now you're getting back to your old habits. Plz keep up the <previous> good work.
agreed.
@pveilleux has got a handle on things, for sure.
the rants about ISO-blah blah has exactly as much weight as if i started adding "make sure your forum posts are compliant with Department’s title II regulation, 28 CFR Part 35, and the title III regulation, 28 CFR Part 36. Appendix A "
to every one of my posts.
nobody knows what that means, and they are not going to google about it, then read a 100 page PDF to try and figure out what you are refering to.
If you want to rant about soemthing that is near and dear to your heart, that's one thing, but if you want to make a real impact, instead of name dropping and referencing something, give an example. saying " the ISO blah blah date format like "20111013 is a best practice " goes along way towards changing peoples behavior, and their opinion of your posts as well.
you almost did it when you refered to this:
The proper form is “[role>_]<attribute>_<property>” which means that absurdities like your @status_name should have been “something_status”
but it did not have an example, so it's not helpful enough without jumping around the interwebs looking for context clues.
grabbing an example from Wiki and shoing something like this would be much more valuable.
For ISO blah blah naming conventions you want something like this:
<PersonGivenName>John</PersonGivenName>
Where Person is the Object=Person, Property=Given and Representation term="Name". In this case the optional qualifier is not used.
personally, i try to add an explicit ,TESTED example to every post i make, just to get someone a little better handle on what i was trying to express as the solution.
Lowell
October 13, 2011 at 8:33 am
I'll just add to the "ignore Joe" votes. So far as I can tell, he has a template for all of his posts, and it plugs in every complaint he's ever had about anything, whether they are relevant to the topic at hand or not. In other words, he's pretty much useless in forums. So, just ignore him.
As far as multi-part string parameters go, one thing you might want to look into is table value parameters. If you're using SQL 2008, as per the forum you posted in, you can use those, and they make this kind of query very, very easy to code against.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 8:54 am
GSquared (10/13/2011)
I'll just add to the "ignore Joe" votes. So far as I can tell, he has a template for all of his posts, and it plugs in every complaint he's ever had about anything, whether they are relevant to the topic at hand or not. In other words, he's pretty much useless in forums. So, just ignore him.
Good to know I'm not the only one he's rubbed the wrong way.
GSquared (10/13/2011)
As far as multi-part string parameters go, one thing you might want to look into is table value parameters. If you're using SQL 2008, as per the forum you posted in, you can use those, and they make this kind of query very, very easy to code against.
That's another good option. I'll keep that one in mind.
I did end up finding a way around it. Since it's only 2 possible values, I just created a second variable and - depending upon the parameter passed in to the proc - the variable either gets set to blank or the appropriate status. So, there are 3 combinations I can have:
blank, blank
Completed, blank
blank, Failed
In real SQL, it looks like this in the proc, after checking for a valid parameter:
DECLARE @Completed NVARCHAR(10)
DECLARE @Failed NVARCHAR(10)
IF @status = 'A'
BEGIN
SELECT @Completed = 'Completed'
SELECT @Failed = 'Failed'
END
IF @status = 'C'
BEGIN
SELECT @Completed = 'Completed'
SELECT @Failed = ''
END
IF @status = 'F'
BEGIN
SELECT @Completed = ''
SELECT @Failed = 'Failed'
END
then the SELECT statement looks like this:
SELECT StatusTypeID FROM LiteSpeedLocal.dbo.LitespeedStatusType WHERE StatusName IN (@Completed, @Failed)
It's ugly, but it works. Results come back in 2 seconds, so it's pretty quick. I can't do a ton of tuning, though, since - as we've already established - it's a vendor's database.
October 13, 2011 at 9:06 am
That'll work. Might end up with problems in terms of cached execution plans being good for one or the other but not both, and so on.
You might want to look at Gail's piece on catch-all queries, http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, with an eye on what she has to say about performance. You'll also want to look at "parameter sniffing". They relate to what you're doing here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 9:11 am
GSquared (10/13/2011)
That'll work. Might end up with problems in terms of cached execution plans being good for one or the other but not both, and so on.You might want to look at Gail's piece on catch-all queries, http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, with an eye on what she has to say about performance. You'll also want to look at "parameter sniffing". They relate to what you're doing here.
You guys already bored from PASS??? 😉
October 13, 2011 at 9:19 am
Ninja's_RGR'us (10/13/2011)
GSquared (10/13/2011)
That'll work. Might end up with problems in terms of cached execution plans being good for one or the other but not both, and so on.You might want to look at Gail's piece on catch-all queries, http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, with an eye on what she has to say about performance. You'll also want to look at "parameter sniffing". They relate to what you're doing here.
You guys already bored from PASS??? 😉
If I were at PASS, I doubt I'd be bored by it.
Instead, I'm redoing datacenter migrations for the third time, because of incompetence at the hosting company. Since I've already seen this movie before, and watching files copy from one datacenter to another wasn't exactly exciting the first time around anway, yeah, bored might describe part of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 9:23 am
GSquared (10/13/2011)
Ninja's_RGR'us (10/13/2011)
GSquared (10/13/2011)
That'll work. Might end up with problems in terms of cached execution plans being good for one or the other but not both, and so on.You might want to look at Gail's piece on catch-all queries, http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, with an eye on what she has to say about performance. You'll also want to look at "parameter sniffing". They relate to what you're doing here.
You guys already bored from PASS??? 😉
If I were at PASS, I doubt I'd be bored by it.
Instead, I'm redoing datacenter migrations for the third time, because of incompetence at the hosting company. Since I've already seen this movie before, and watching files copy from one datacenter to another wasn't exactly exciting the first time around anway, yeah, bored might describe part of it.
WTF comes to mind.
Where's the part in the contract where they send you a check for every time this happens?
October 13, 2011 at 9:24 am
Ninja's_RGR'us (10/13/2011)
GSquared (10/13/2011)
Ninja's_RGR'us (10/13/2011)
GSquared (10/13/2011)
That'll work. Might end up with problems in terms of cached execution plans being good for one or the other but not both, and so on.You might want to look at Gail's piece on catch-all queries, http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, with an eye on what she has to say about performance. You'll also want to look at "parameter sniffing". They relate to what you're doing here.
You guys already bored from PASS??? 😉
If I were at PASS, I doubt I'd be bored by it.
Instead, I'm redoing datacenter migrations for the third time, because of incompetence at the hosting company. Since I've already seen this movie before, and watching files copy from one datacenter to another wasn't exactly exciting the first time around anway, yeah, bored might describe part of it.
WTF comes to mind.
Where's the part in the contract where they send you a check for every time this happens?
The part where it says "Salary Exempt" comes to mind.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 9:33 am
GSquared (10/13/2011)
Ninja's_RGR'us (10/13/2011)
GSquared (10/13/2011)
Ninja's_RGR'us (10/13/2011)
GSquared (10/13/2011)
That'll work. Might end up with problems in terms of cached execution plans being good for one or the other but not both, and so on.You might want to look at Gail's piece on catch-all queries, http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, with an eye on what she has to say about performance. You'll also want to look at "parameter sniffing". They relate to what you're doing here.
You guys already bored from PASS??? 😉
If I were at PASS, I doubt I'd be bored by it.
Instead, I'm redoing datacenter migrations for the third time, because of incompetence at the hosting company. Since I've already seen this movie before, and watching files copy from one datacenter to another wasn't exactly exciting the first time around anway, yeah, bored might describe part of it.
WTF comes to mind.
Where's the part in the contract where they send you a check for every time this happens?
The part where it says "Salary Exempt" comes to mind.
This is also the part where stop sending money comes to mind too :-D.
Yes I know how this usually ends.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply