February 14, 2008 at 7:30 am
On many of my tables I store a status value. The values are Active, On Hold, Cancelled, In Legal and Closed. There are times when I need to return all rows that are Active or on Hold. Other times when I need to return all rows that are Active or Closed. I am trying to come up with a method that I can use to return any combination of status values. I am not sure which of the following options is a better option:
1. Create/Call a stored procedure that returns all the rows for one specific status multiple times (once for each status value) and then combine the result sets into one result set.
2. Create/Call a stored procedure that returns the specific combination of status values that I looking for.
3. Create/Call a dynamic stored procedure that passes in status values that I am looking for
I would appreciate it if I could get some opinions as to which solution is the best solution or if there is a better solution that I have not thought of.
Thanks
February 14, 2008 at 8:13 am
I dont know how your tables are indexed or anything but you could use some simple xml to parse a string and retreive values from the table based on the string. I would prefer this method over returning unwanted results or dynamic sql.
CREATE PROCEDURE TEST
@filter VARCHAR(100)
AS
BEGIN
DECLARE @x XML
SET @x = '?i?' + REPLACE( @filter, '|', '?/i??i?') + '?/i?'
SELECT *
FROM MyTable
WHERE [Status]
IN (SELECT x.i.value('.', 'VARCHAR(7)')
FROM @x.nodes('//i') x(i))
END
You can then execute the code like
EXEC Test 'ACTIVE|ONHOLD'
February 14, 2008 at 8:17 am
Man, I hate how this site strips the XML. You have to change the question marks to open and close xml tags ( )
DECLARE @x XML
SET @x = '?i?' + REPLACE( @filter, '|', '?/i??i?') + '?/i?'
February 14, 2008 at 9:57 am
Adam - if you do a find and replace prior to posting that, and replace the brackets with their HTML equivalents - it won't get stripped.
Meaning - replace
> with >
< with <
you'd still have to post it in a code block if you want to maintain some semblance of alignment.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 14, 2008 at 10:24 am
Post with Matt's tip.
CREATE PROCEDURE TEST
@filter VARCHAR(100)
AS
BEGIN
DECLARE @x XML
SET @x = '<i>' + REPLACE( @filter, '|', '</i><i>') + '</i>'
SELECT *
FROM MyTable
WHERE [Status]
IN (SELECT x.i.value('.', 'VARCHAR(7)')
FROM @x.nodes('//i') x(i))
END
February 14, 2008 at 10:25 am
Ah, much better. Thanks for the tip, I knew the equivilant but didnt even think to use it.
Thanks.
February 14, 2008 at 10:55 am
I came up with a different solution that I am close to fixing, but am having a problem with. I decided to use an IN clause so that my selection would include a line like:
where STATUS in (@statuses)
Exec SomeProc 'HOLD, ACTIVE'
Unfortunately I was not told by my manager that the status values are actually Ints. For instance Active = 1, On Hold = 2... Etc.
So I tried
Exec SomeProc '1,2'
This generated a conversion error. So now I am stuck. Can I use an "IN" clause to solve this problem or do I need to make use of the solutions that have been provided?
Thanks
February 14, 2008 at 11:08 am
Like I said I would not use dynamic sql, if I did not have to. Dynamic SQL does not reuse execution plans and can hinder performance, if there are lots of records. The solution I gave you will take any number of statuses, in any delmited format, and check them against the table. Basically it is doing what you are but without dynamic sql.
February 14, 2008 at 11:16 am
You run into more problems with dynamic sql like indexing, caching, recompiles etc. But if you must go with a dynamic sql solution I would recommend that you use sp_executesql as this does allow for query plan reuse and has built in functionality for parameters.
February 19, 2008 at 5:52 am
At this point I am not sure if I am better off using dynamic sql or using optional parameters in my t-sql script. Any thoughts?
Thanks
February 20, 2008 at 5:12 am
meichner (2/19/2008)
At this point I am not sure if I am better off using dynamic sql or using optional parameters in my t-sql script. Any thoughts?Thanks
You should not use dynamic sql unless you have to...and this is not such a case. Instead you should do like Adam suggests...or do something like this
--Adam's code wrapped in a function
CREATE FUNCTION SplitStringToInt
(
@filter VARCHAR(8000)
)
RETURNS @numbers TABLE
(
idno int
)
AS
BEGIN
DECLARE @x XML
SET @x = '<i>' + REPLACE( @filter, '|', '</i><i>') + '</i>'
INSERT INTO @numbers(idno)
SELECT x.i.value('.', 'int') FROM @x.nodes('//i') x(i)
RETURN
END
Then you can use this function in your sprocs...
Select * from Order where Status in (Select idno from SplitStringToInt(@StatusCodes)) --@StatusCodes = '1|2|3|4'
February 20, 2008 at 5:40 am
Thanks, I will give that a shot.
February 20, 2008 at 6:35 am
Ok... I'm a bit new to 2k5... just installed it a couple of months ago and still coming up to speed on how some of the new features work.
Can someone explain to me how the XML was used to this bit of computational heaven? Thanks...
--Jeff Moden
February 20, 2008 at 7:07 am
The first thing we are doing is adding tags to all the values in the delimited string. E.g.
declare @filter varchar(25)
set @filter = 'test|test2'
DECLARE @x XML
SET @x = '<i>' + REPLACE( @filter, '|', '</i><i>') + '</i>'
The string @x looks like this at this point: <i>test</i><i>test2</i>
SELECT x.i.value('.', 'VARCHAR(7)') as [test]
FROM @x.nodes('//i') x(i)
Next, we use the new xquery nodes to return all values from delimited string in table format. We place this piece of code in the from clause, so we can reference the values in the select, with the column reference x(i). The column aliase has to be referenced in the select. Below I changed the column alias to demonstrate how the column alias and the select must match. I will also point out that the '.' is supplied to tell the value method where to look for the data, in this case the root of x.nodes.
SELECT x.abc.value('.', 'VARCHAR(7)') as [test]
FROM @x.nodes('//i') x(abc)
We then tell the query to retrieve each value from the nodes return and cast it as a varchar(7) by using the xquery value method.
February 20, 2008 at 7:13 am
I am new to this XML query stuff so please forgive the question. I have tried to get your function to complile, but I keep getting errors about an alias of x(i). Is the syntax in your post correct?
Thanks
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply