Not sure of best way to select different status values

Question

Not sure of best way to select different status values

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

Jonnie Pettersson SSC Veteran Points: 257 More actions · Answer 1

meichner (2/20/2008)
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

It stripped the < and > of my post but I have changed that now...

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

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?

Yes, his code was correct, but it was stripped by the site. He has since edited his post to correct the xml.

Jonnie for performance reason we should not use the in clause. Since your function is returning a table the option for best performance is to do an INNER JOIN on the table. This will accomplish the same goal and allow for better performance.

e.g.

Select *

from Order a inner join SplitStringToInt(@StatusCodes) as b

on a.Status = b.idno

edited for grammer

My blog: http://jahaines.blogspot.com

Jonnie Pettersson SSC Veteran Points: 257 More actions · Answer 3

Adam Haines (2/20/2008)

...Jonnie for performance reason we should not use the in clause. Since your function is returning a table the option for best performance is to do an INNER JOIN on the table. This will accomplish the same goal and allow for better performance.

Absolutely! but the in clause was easier to demonstrate 😀

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Adam Haines (2/20/2008)
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 = '' + REPLACE( @filter, '|', '') + ''
The string @x looks like this at this point: testtest2
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.

Outstanding, Adam! Thanks for the breakdown!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meichner SSCertifiable Points: 5563 More actions · Answer 5

Sorry to be a pest about this, but when I executed the function I got the following error:

Insert Failed because the following Set options have incorrect settings: 'ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING'...

Do I need to do something else in my (your) script?

Thanks

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

Can you display the entire error message?

My blog: http://jahaines.blogspot.com

meichner SSCertifiable Points: 5563 More actions · Answer 7

First I would like to thank you for all your help. I really appreciate it.

I wanted to test the function so I issued the following Select Statement:

select idno

From dbo.SplitStringToInt('1|2|3')

The output was:

Msg 1934, Level 16, State 1, Line 1

INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Thanks again

Adam Haines SSC-Insane Points: 23217 More actions · Answer 8

When you create your stored procedure you have to use the following SET options:

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

You have to alter your stored procedure or drop and recreate it. If you do not specify these set options or set them reverse to what I have, the xml will throw an error.

link for more info:

http://blogs.msdn.com/james_world/archive/2007/02/12/passing-variable-numbers-of-parametes-to-sprocs-using-xml.aspx

My blog: http://jahaines.blogspot.com

meichner SSCertifiable Points: 5563 More actions · Answer 9

meichner

SSCertifiable

Points: 5563

February 20, 2008 at 10:18 am

#780294

That did the trick. Many thanks

Adam Haines SSC-Insane Points: 23217 More actions · Answer 10

No problem. Glad I could help.

My blog: http://jahaines.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Sorry I'm showing up late to the party...:), but - you don't really need to use a function for this. You can simply flip the LIKE syntax on its head.

like so:

set @statuscodes='|'+@statuscodes+'|';

Select *

from orders

where @statuscodes like '%|'+status+'|%'

----------------------------------------------------------------------------------
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?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 12

Yeah. Technically he could use xml to dynamically parse his string and directly relate it to his table.

DECLARE @x XML

SET @x = '' + REPLACE( @filter, '|', '') + ''

SELECT *

FROM Order a

INNER JOIN

(SELECT x.i.value('.', 'INT') AS [Status]

FROM @x.nodes('//i') x(i)

) AS b

ON a.Status = b.Status

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 13

I think he chose to make it an udf, so he could reuse the logic.

My blog: http://jahaines.blogspot.com

meichner SSCertifiable Points: 5563 More actions · Answer 14

Your idea seemed like an interesting idea so I thought I would give it a try just to see how it worked. When I tried to run it I get the following error:

Error: Conversion failed when converting the varchar value '%|' to data type int.(22018,245), Batch 1 Line 3

Here is my code:

Declare @StatusCodes VarChar(50)

Set @StatusCodes ='|1|2|'

Select * from Orders

Where @StatusCodes Like '%|'+ status +'|%'

Am I doing anything wrong?

Thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 15

LIKE applies to text/char-based columns. If your status field is numeric, then you would need to CAST it to a varchar(20) (making the width appropriate to your data).

So:

Declare @StatusCodes VarChar(50)

Set @StatusCodes ='|1|2|'

Select * from Orders

Where @StatusCodes Like '%|'+ cast(status as varchar(20)) +'|%'

----------------------------------------------------------------------------------
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?