ParseName - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ParseName

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-02-22 : 01:04:58

I have some SQL statements that when posted in QA, work fine. When I put that SQL statement into a stored procedure with a parameter, it no longer works. I think it all to do with the parameter.
@vs_EntityIDSubString = '4439,6473'

My SP looks like this:
CREATE PROCEDURE ctcGetBillingAddress
@vs_EntityIDSubString nvarchar(100) AS

SELECT *
FROM ctcPeople.dbo.EntityAddress
WHERE UseForFinancial = 1
AND Convert(nvarchar(100),EntityID) IN (@vs_EntityIDSubString)

When I post this Select statement into QA and set @vs_EntityIDSubString, it does not return anything (I am assuming becuase of the quotes). When I put the value of @vs_EntityIDSubString into its place holder, it works (without the quotes). I realize that the quotes are making it process as a whole instead of two different ID values. But.... the problem is that when I try to call the stored procedure and send it 4439,6473 as the parameter (w/o quotes), it things it two seperate parameters, and then fails. Add the quotes and it does not return data because it is looking for the whole thing.

I was thinking that I needed to maybe use Parsename to parse out the ID's after it is passed and use EntityID = XXX OR EntityID = XXX, etc, but I cannot find any other posts where the user doesn't know how many items need to be parsed. Sometimes I may only have 1 id, sometimes 2 id's, sometimes many. That is why In was so convienent.

I hope this makes sense. I am certainly at a loss.

Thanks,
JAdauto

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 01:42:07

the @vs_EntityIDSubString is treated as one single text expression.

The in effect query is something like


SELECT *
FROM ctcPeople.dbo.EntityAddress
WHERE UseForFinancial = 1
AND Convert(nvarchar(100),EntityID) IN ('''4439,6473''')

Which will not result in any record match.

If you are passing csv string into the stored procedure, make use of CSVTable here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-22 : 02:16:17

SELECT *
FROM ctcPeople.dbo.EntityAddress
WHERE UseForFinancial = 1
AND ',' + @vs_EntityIDSubString + ',' LIKE '%,' + CAST(EntityID AS NVARCHAR) + ',%'

Peter Larsson
Helsingborg, Sweden

JohnH
Starting Member

13 Posts

Posted - 2007-02-23 : 16:50:32

Thanks Peso, I needed that, too!

John Hopkins

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-02-26 : 23:00:37

quote:
Originally posted by Peso

SELECT *
FROM ctcPeople.dbo.EntityAddress
WHERE UseForFinancial = 1
AND ',' + @vs_EntityIDSubString + ',' LIKE '%,' + CAST(EntityID AS NVARCHAR) + ',%'

Peso, this worked like a CHARM!!! Thanks so much,
JAdauto

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-27 : 02:04:42

seems like this approach will force a table scan which will not perform well if the table is large. you might want to look into the methods discussed in the thread khtan referenced above.

also Erland has a good discussion of arrays in sql here:
http://www.sommarskog.se/arrays-in-sql.html

www.elsasoft.org

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-03-01 : 01:10:23

Peso, your solution works perfectly. However, speed is an issue for me. The point of moving some of my code to an SP (or several SP's)is to try to speed up an already very lengthy process. My latest test proved to be 22 minutes longer then my original test. I will still keep this solution in mind for small tables and appreciate it.

So I am now looking at implementing the CSVTable that khtan wrote about. I have created the UDF just as created in the link, but when I am attempting use it, it keeps telling me "Invalid object name dbo.CSVTable". This is my first experience with UDF, so is there something I am not doing?

My where now looks like this:
WHERE EntityID in (Select StringVal from dbo.CVSTable(@vs_EntityIDSubString))

Is this correct or have I completely missed the point?
Thx,
JAdauto

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 01:51:42

Did you create the CSVTable() function ?

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-03-01 : 01:59:04

Yes. I copied and pasted it just like in the link (see below). It seems like the error is about rights or something, but not being familiar with the UDF's, I thought maybe it was something silly I was not doing.

CREATE function CSVTable (@Str varchar(7000))
returns @t table (numberval int, stringval varchar(100), DateVal datetime)
as
begin

declare @i int;
declare @c varchar(100);

set @Str = @Str + ','
set @i = 1;
set @c = '';

while @i <= len(@Str)
begin
if substring(@Str,@i,1) = ','
begin
insert into @t
values (CASE WHEN isnumeric(@c)=1 THEN @c else Null END,
rtrim(ltrim(@c)),
CASE WHEN isdate(@c)=1 then @c else Null END)
set @c = ''
end
else
set @c = @c + substring(@Str,@i,1)
set @i = @i +1
end
return
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 02:12:08

yes. just run this in the Query Analyzer. Did you get any error ? If it is, do post the error message

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-03-01 : 10:21:38

When I post the Function in QA, it tells me that CSVTable already exists. When I call my stored procedure from QA, it tells me

Invalid object name 'dbo.CVSTable'.

I know this stored procedure has way more details then you need to look at, but maybe if you see it in its entirety, something will pop up that I am doing incorrectly. Anyway here is what I am calling that is giving me the invalid object name:

CREATE PROCEDURE ctcGetTransHeadersList2
@vl_ARPeriodID int,
@vl_BalanceTypeID int,
@vs_EntityIDSubString nvarchar (100),
@vl_ChargeOrder1 int,
@vl_ChargeOrder2 int,
@vl_ChargeOrder3 int,
@vl_ChargeOrder4 int
As

SELECT
th.* ,
td.RevenueCodeDeptID,
rc.Code,

CASE tm.BatchTypeENUM
WHEN 17 THEN 1
WHEN 16 THEN 2
WHEN 2 THEN 3
WHEN 4 THEN 5
ELSE 4
END As 'ChargeTypeOrder',

CASE WHEN IsNumeric(TH.TicketNumber) = 1
THEN Convert(numeric(10),(th.TicketNumber))
WHEN IsNumeric(TH.TicketNumber) = 0
THEN 1000000000
END As 'TicketNumSortOrder',

CASE @vl_ChargeOrder1
WHEN 1 THEN Convert(varchar(10),(th.TransDate),101)
WHEN 2 THEN 'TicketNumSortOrder' + th.TicketNumber
WHEN 3 THEN 'ChargeTypeOrder'
WHEN 4 THEN rc.Code
END as ChargeOrder1,

Case @vl_ChargeOrder2
WHEN 1 THEN Convert(varchar(10),(th.TransDate),101)
WHEN 2 THEN 'TicketNumSortOrder' + th.TicketNumber
WHEN 3 THEN 'ChargeTypeOrder'
WHEN 4 THEN rc.Code
End as ChargeOrder2,

Case @vl_ChargeOrder3
WHEN 1 THEN Convert(varchar(10),(th.TransDate),101)
WHEN 2 THEN 'TicketNumSortOrder' + th.TicketNumber
WHEN 3 THEN 'ChargeTypeOrder'
WHEN 4 THEN rc.Code
End as ChargeOrder3,

Case @vl_ChargeOrder4
WHEN 1 THEN Convert(varchar(10),(th.TransDate),101)
WHEN 2 THEN 'TicketNumSortOrder' + th.TicketNumber
WHEN 3 THEN 'ChargeTypeOrder'
WHEN 4 THEN rc.Code
End as ChargeOrder4

FROM TransHeader AS th
INNER JOIN TransDetail as td
ON th.ID = td.TransHeaderID
INNER JOIN TransMain as tm
ON th.TransMainID = tm.ID
INNER JOIN RevenueCode as rc
ON td.RevenuecodeDeptID = rc.ID
WHERE tm.ARPeriodID = @vl_ARPeriodID
AND td.BalanceTypeID = @vl_BalanceTypeID
AND tm.BatchStatusENUM IN (3, 4)
AND th.EntityID in (Select StringVal from dbo.CVSTable(@vs_EntityIDSubString ))

ORDER BY th.EntityID,ChargeOrder1, ChargeOrder2,ChargeOrder3,ChargeOrder4
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 10:42:41

Yes. Something did pop up.

AND th.EntityID in (Select StringVal from dbo.CVSTable(@vs_EntityIDSubString ))

Do you see that also ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 10:47:05

Should have spoted it here also.

quote:
Originally posted by JAdauto

Peso, your solution works perfectly. However, speed is an issue for me. The point of moving some of my code to an SP (or several SP's)is to try to speed up an already very lengthy process. My latest test proved to be 22 minutes longer then my original test. I will still keep this solution in mind for small tables and appreciate it.

So I am now looking at implementing the CSVTable that khtan wrote about. I have created the UDF just as created in the link, but when I am attempting use it, it keeps telling me "Invalid object name dbo.CSVTable". This is my first experience with UDF, so is there something I am not doing?

My where now looks like this:
WHERE EntityID in (Select StringVal from dbo.CVSTable(@vs_EntityIDSubString))

Is this correct or have I completely missed the point?
Thx,
JAdauto

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-03-01 : 10:53:38

Oh my goodness.. I feel like such a dummy. Again.. right in front of me!! Thank you. I am going to run another test to see if this helps improve my overall time. If not, I'll be going to back to the drawing board with this sp. Thank you soooo much for your help with this item.
JAdauto

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-01 : 12:36:56

CVS is a drugstore. CSV is "comma-separated-values"

easy to get them mixed up.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources