March 4, 2009 at 1:55 pm
Hello,
I am sending this parameter "'01','02','03'" to a variable that is in a IN function [ie: Select * From Table Where Data IN(@Variable)]. It seems that SQL cant reconize the string Im sending and the result of the SP is nothing; so is there a way to send this to a IN condition or I have to change it for OR and = ?
Thanks for reading.
March 4, 2009 at 2:01 pm
You probably need to use dynamic sql in your stored procedure. If more help, you will need to post the code.
March 4, 2009 at 2:19 pm
You have to parse the string into a table of values, or use dynamic SQL. I recommend parsing, because that doesn't open up injection security issues.
The fastest way I know of to parse a simple string like that is to convert it to XML and then query that. That only works if you're on SQL 2005 or later, which I assume you are from the forum you posted in.
I've attached a sample script for using XML to parse (XML doesn't display well in the forum, so attaching works best in my experience). Assume that @List is your input parameter, the part to put in the query starts with declaring the XML variable.
Note that this won't work well if you have XML special characters in your list, like "&" or the greater/less symbols.
Does that help?
- 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
March 4, 2009 at 2:19 pm
/*[SP_Dispo_X_Leg3]*/
ALTER PROCEDURE [dbo].[SP_Dispo_X_Leg3]
@ano as nvarchar(4),
@mes as nvarchar(14),
@Lugar as nvarchar(2),
@press as nvarchar(1)
AS
DECLARE
@CodDispon as nvarchar(2),
SET @CodDispon ='99'
IF @press=1
BEGIN
SELECT DisPoS.* , ( DisPoS.ValTotDispo - ISNULL(Otes.saldoOT,0) ) AS SALDOD
From (
SELECT PeMovimientoCb.Ndoc , PeMovimientoCb.Cdoc , PeMovimientoCb.Tdoc ,PeMovimientoCp.Lugar,
PeMovimientoCp.CodRubro, PeMovimientoCb.detalle AS Descripcion, PeMovimientoCb.mes ,
PeMovimientoCb.fecha , PeMovimientoCB.VALORTOTAL, sum(PeMovimientoCp.VALOR) as ValTotDispo
FROM PeMovimientoCb INNER JOIN PeMovimientoCp ON PeMovimientoCb.ano = PeMovimientoCp.ano AND
PeMovimientoCb.mes = PeMovimientoCp.mes AND PeMovimientoCb.Cdoc = PeMovimientoCp.Cdoc AND
PeMovimientoCb.Ndoc = PeMovimientoCp.Ndoc
WHERE (PeMovimientoCb.ano = @ano) AND (PeMovimientoCb.Cdoc = @CodDispon) AND
(PeMovimientoCb.Estado IS NULL OR PeMovimientoCb.Estado = N'') and (PeMovimientoCb.mes IN (@mes))
GROUP BY PeMovimientoCb.Ndoc, PeMovimientoCp.CodRubro, PeMovimientoCb.Cdoc, PeMovimientoCb.Tdoc,
PeMovimientoCb.DETALLE, PeMovimientoCb.mes ,PeMovimientoCp.lugar, PeMovimientoCb.fecha, PeMovimientoCb.VALORTOTAL
HAVING (PeMovimientoCp.lugar LiKe '%'+ @Lugar)
GO
/*SENDING PARAMETERS TO [SP_Dispo_X_Leg3]*/
DECLARE@return_value int
EXEC@return_value = [dbo].[SP_Dispo_X_Leg3]
@ano = N'2009',
@mes = N'''01'',''02'',''03''',
@Lugar = N'01',
@press = N'1'
March 4, 2009 at 3:05 pm
Would end up looking like what I've attached 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
March 4, 2009 at 11:14 pm
GSquared (3/4/2009)
The fastest way I know of to parse a simple string like that is to convert it to XML and then query that. That only works if you're on SQL 2005 or later, which I assume you are from the forum you posted in.
I've found that a Tally table split pretty much beats up an XML split... probably doesn't matter here as it's only a single row, but I wouldn't want to see anyone use it on a million rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2009 at 3:15 pm
Jeff Moden (3/4/2009)
GSquared (3/4/2009)
The fastest way I know of to parse a simple string like that is to convert it to XML and then query that. That only works if you're on SQL 2005 or later, which I assume you are from the forum you posted in.I've found that a Tally table split pretty much beats up an XML split... probably doesn't matter here as it's only a single row, but I wouldn't want to see anyone use it on a million rows.
The problem I have with using Tally/Numbers tables in long string splits (like 1,000+ elements in the string) is that you have to be sure you're highest number is at least equal to the length of the string + 1.
For example, if you have 1 to 10,000 in your Tally table, the string had better not be more than 10,000 characters long, no matter how many elements that is.
For small strings, which is admittedly much more normal, with say 10 elements that are less than 10 characters each, either one will give you the results in such minimal time that performance won't matter. (I tested it on my desktop machine and got 18 milliseconds for Numbers table on the first run vs 20 milliseconds on the XML version, and then both version dropped to sub-millisecond on subsequent runs.)
But if you are ever going to have to handle really large strings, you either need a simply huge Tally table, or you need to use an XML method, which won't care how many elements or how long the original string.
I tested on a 10,000-element string, average of 5 characters per element, total length of string just over 44,000 characters. My 10-thousand number Numbers table only gave me the first 2,200 elements, but the XML method gave me all 10-thousand.
I expanded the Numbers table out to 50,000 numbers, and got all 10,000 elements, but how many people keep a table that size?
And the speed difference between the two was measurable, but only to a server. The 50k-range Numbers table did the parse in 146 milliseconds, while the XML version did it in 248 milliseconds. Big difference on a massively overloaded server, but not anything any human user is going to notice in an application.
If interested, the Numbers table version ended up with IO stats: 1 scan, 81 logical reads. The XML version had no IO stats since it didn't touch any tables. No scans, no reads, etc.
So, if you can guarantee you'll never have a string split larger than you Numbers/Tally table, yeah, it's a better solution. But you're the one who brought up things like million-row splits.
Here's the code I tested these with:
set nocount on;
declare @String varchar(max);
select @String = coalesce(@String + ',' + cast(Number as varchar(10)), cast(Number as varchar(10)))
from dbo.Numbers
where Number <= 10000;
set statistics time on;
select substring(@String + ',', Number, charindex(',', @String + ',', Number) - Number)
from dbo.Numbers
where Number <= len(@String)
and substring(',' + @String, Number, 1) = ','
order by Number;
set statistics time off;
set nocount on;
declare @String varchar(max);
select @String = coalesce(@String + ',' + cast(number as varchar(10)), cast(number as varchar(10)))
from dbo.Numbers
where Number <= 10000;
set statistics time on;
declare @XML XML;
select @XML = ' ';
select Nodes.x.query('.').value('(row/@x)[1]','int')
from @XML.nodes('row') Nodes(x);
set statistics time off;
Play with the field size, number of elements, etc., as you like in those.
Also note, there's probably a better way to implement the XML query. I'm far from a master of that field.
I did make the mistake in my earlier post of saying "fastest", which isn't true. The Numbers table is faster. But I've found the XML split more reliable in some circumstances.
- 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
March 5, 2009 at 3:58 pm
That's why I'll frequently use a programmable Tally CTE. I've got that bad boy written up in the Tally table article...
Thanks for the test code... I'll try to find the time to crank out an example or two tonight using it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2009 at 7:36 am
Took another look at what you wrote, and realized maybe you meant tables with large numbers of rows, each of which needs to be parsed. So, I threw together a test for that too.
I expected a speed difference between the Numbers version and the XML version. As already stated, the Numbers version is faster. And with a hard-coded limitation of 10 elements max, and about 50 characters max, the XML version is definitely not necessary, as even a small Numbers table will work for that.
We've already gone over the CTE Numbers concept in detail in other threads. No need to go into it for my sake. (Actually, if I'm not mistaken, I'm the one who did the speed tests and such on that a year ago when it first came up for the two of us.)
Interestingly enough, in the attached test, the Numbers version finishes in about 16 seconds on my machine. The XML version never finished, because I killed it after nearly 10 minutes. I was expecting a speed difference, but not that much of one. I only ever use it on single-string operations, and use the Numbers version on anything where I can guarantee string length and number of elements won't overload the table.
- 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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply