UDF, Comma delimited string, Stored Procedure

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

SQL Server Development (2000)

UDF, Comma delimited string, Stored Procedure

Author

Topic

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 13:39:25

Hi,

Help! I'm having a problem with the above combination of code.

I've written a Stored Procedure which uses an Open Source UDF to process a comma delimited string, creates a temporary table (using a table variable) and dumps my codes into that table. This stored procedure then creates a cursor on a joined table (the temp table with the codes and the permanent table with the data) and inserts or updates into another permanent table.

This stored procedure works independently when I run it through query analyser providing it with a set comma delimited string containing the same data which would normally be passed to it.

The data is passed to the stored procedure through a component application which has a function which builds the stored procedure using parameter objects. This component works. It picks up the correct data and processes it correctly as far as I can tell.

However, when I put the two together and try to send the comma delimited string to the Stored procedure I can only get 29 (or fewer) codes to process. If I select under 29 all will process correctly.
If I select over 29, the stored procedure will only process 29 but the 29 processed are spread out throughout the string. That is, 10 may be processed 2 skipped, 1 processed 5 skipped, etc.

I've allowed for a maximum of 4000 char and tried this using adVarChar and adLongVarChar.

The following is the line of code which is handling the comma delimited string and passing it to the stored procedure. I've tried several different ways of creating this parameter and passing the string through it.

"rsCmd.Parameters.Append rsCmd.CreateParameter(arrNames(i), adLongVarChar, adParamInput, strLength, arrValues(i))"

Does anyone have any ideas on what the problem could be here? If you need more information or code, please let me know. Need to know asap!

Thanks,

Kitty

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-08 : 14:00:01

Kitty
It looks to me very much like you are passing each element of the array through to the stored procedure as a separate parameter -- Im pretty sure that TSQL cannot cope with 4000 params.

What I would do in this situation is use the JOIN string function on the array, using "," as the separator to return 1 long string holding all the values separated by commas and pass that in as the param.
Hope this helps, if it doesnt it means Ive completely misunderstood what you are doing

Graham

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 14:10:40

Hi Graham,

I'm afraid I probably wasn't clear. (Thanks for the quick response though).

The variable arrValues(i) contains a comma delimited string of codes.

For example: it could equal something like: 'ABCDEF001,123456001,123 001,ABCDE 001'

If I do the following from Query Analyser:

declare @SHIPTOCODES varchar(4000)

SET @SHIPTOCODES='TEST 001,TEST 012,TEST 011,TEST 010,TEST 009,TEST 008,TEST 007,TEST 005,NATPOS002,TESTR 001'

SELECT * INTO #TEMP_TABLE FROM fulfillment.dbo.iter_charlist_to_table(@SHIPTOCODES, ',') ORDER BY NSTR

... rest of code ...

I can send a maximum of 363 codes to the stored procedure and they will process properly. This is how it should work.

However, as soon as I try to send those codes through my VB Component Application. Only segments of the string will process unless I send 29 or less.

Hope this clears things up,

Kitty

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-08 : 14:25:02

Im not well up on using the command object - Id suggest for debugging running the SQL profiler on the server and see exactly what is being executed by the server

There isnt any chance that quote chars are included in the string anywhere is there ? That would stop things working

Graham

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 14:30:55

One more clarification:

arrValues(i) where i=1 is the same as the contents of @SHIPTOCODES.

The array itself contains a recordset. For example:

arrValues(0)=trackingnumber
arrValues(1)=shiptocode list
arrValues(2)=signature
arrValues(3)=updated date

My function is a generic function (at least most of it is) which sets up the parameter object based on data sent to it and passes it to various stored procedures.

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 14:32:56

None of the test data sent has contained any quotes, but I'll make sure there's code to handle it - thanks for the heads up on that one.

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 14:44:53

Here's some specs on the architecture and environment.

This is a multi-tier program with an ASP front end, a VB 6.0 COM+ Application which handles the middle tier, and a SQL Server 2000 Back End which uses mainly Stored Procedures.

I'm trying to enhance the performance of the program by using comma delimited strings in order to reduce db accesses.

The rest of the details I've outlined in my first post.

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-08 : 17:14:23

Kitty
Looking at what you have written, it should work ok, but of course it always does !!

Going back to your command parameter creation line,
rsCmd.CreateParameter(arrNames(i), adLongVarChar, adParamInput, strLength, arrValues(i))
how is strLength set ? any chance that it isnt large enough to hold your string ?

The SQL Profiler will show up if the string is being truncated

Graham

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 17:31:28

I just test for length using len(). The length returned matches the number of characters in the string. I've checked this.

I will try running Profiler and see what happens. Thanks for the tip.

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 17:53:07

Profiler shows the following:
exec spcloADDMULTIPLERECIPIENTSTOORDER 'AAA006', 'TORLTD001,ONTLTD', 0, 'KITTY'

However, I know that a larger string is actually being processed because 29 records are saved to the database. Profiler is only showing 1 full code and 1 partial code? Since I'm a rookie at using Profiler I don't know what this signifies. Can you enlighten me?

I also know that the end of the string is being processed so it isn't being truncated.

Follows is the results of the full string in an annotated text file, if this helps:

A-CHAW004,A-CHAW001,A-CHAW005,A-CHAW003, - saved to db
CKALT 048,CKALT 039,CKALT 043,CKALT 044,CKALT 037,CKALT 010, - skipped

A-CHAN002,A-CHAN003,A-CHAN006,ACHAN 003,ACHAN 001, - saved to db

JONPAR001, - skipped

A&AAUT001,ACHWIN006,ACHWIN019,ACHWIN018,ACHWIN005,ACHWIN013,ACHWIN017, - saved to db

MTNT 005, - skipped

CFCWR 003,7JOURS001, - saved to db

CFTRR 059,CFTRR 060,CFTRR 056,CFTRR 055,CFTRR 042,CFTRR 041,CFTRR 035,CFTRR 046,CFTRR 047,CFTRR 036,CFTRR 034,CHEOR 001, - skipped

55TH 001,4SEASO001,3RDQU 001,24IMAG001, - saved to db

FOXQUE001,FOXHOM002,FOXHO2001,FOXHOM001,FOXHO2004,FOXHO2003,FOXZZZ016,FOXZZZ015,FOXZZZ014,FOXZZZ013,FOXZZZ012,FOXZZZ011,FOXZZZ001,FOXZZZ003,FOXZZZ024, - skipped

2ROOMS001,- saved to db

TORLTD001,ONTLTD001,MONTLT001, - skipped

12345 001,100000001, - saved to db

MIJO 001,TSNT 046, - skipped

ACLK 011, - saved to db

GREY 031,TVAPUB001, - skipped

CFBRR 006,CFQCT 003,CFQCT 002, - saved to db

VANCOP004,VANCOP003,VANCOP002,CITYVA001,NEWWI 001,Z103 001,CIVTT 040,MITVT 009,MITVT 029,MITVT 018,TEST 001,TEST 012,TEST 011,TEST 010,TEST 009,TEST 008,TEST 007,TEST 005,NATPOS002,TESTR 0015,NATPOS002,TESTR 001 - skipped

original string
A-CHAW004,A-CHAW001,A-CHAW005,A-CHAW003,CKALT 048,CKALT 039,CKALT 043,CKALT 044,CKALT 037,CKALT 010,A-CHAN002,A-CHAN003,A-CHAN006,ACHAN 003,ACHAN 001,JONPAR001,A&AAUT001,ACHWIN006,ACHWIN019,ACHWIN018,ACHWIN005,ACHWIN013,ACHWIN017,MTNT 005,CFCWR 003,7JOURS001,CFTRR 059,CFTRR 060,CFTRR 056,CFTRR 055,CFTRR 042,CFTRR 041,CFTRR 035,CFTRR 046,CFTRR 047,CFTRR 036,CFTRR 034,CHEOR 001,55TH 001,4SEASO001,3RDQU 001,24IMAG001,FOXQUE001,FOXHOM002,FOXHO2001,FOXHOM001,FOXHO2004,FOXHO2003,FOXZZZ016,FOXZZZ015,FOXZZZ014,FOXZZZ013,FOXZZZ012,FOXZZZ011,FOXZZZ001,FOXZZZ003,FOXZZZ024,2ROOMS001,TORLTD001,ONTLTD001,MONTLT001,12345 001,100000001,MIJO 001,TSNT 046,ACLK 011,GREY 031,TVAPUB001,CFBRR 006,CFQCT 003,CFQCT 002,VANCOP004,VANCOP003,VANCOP002,CITYVA001,NEWWI 001,Z103 001,CIVTT 040,MITVT 009,MITVT 029,MITVT 018,TEST 001,TEST 012,TEST 011,TEST 010,TEST 009,TEST 008,TEST 007,TEST 005,NATPOS002,TESTR 001

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 18:08:57

This is what Profiler outputs when use the string indicated in the text file in the previous post. The profiler output shown above is related to another string. (just to avoid any confusion).

exec spcloADDMULTIPLERECIPIENTSTOORDER 'AAA006', 'A-CHAW004,A-CHAW', 0, 'KITTY'

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-08 : 18:12:05

ok, Im foxed now
Can you post the code for the SP - I know you said it worked in query analyser, but there might be some clue there

Ive only used the profiler a few times and found it very handy, maybe there is a limit to the amout of text it will show.

In cases like this, I resort to adding debugging code into the SP...

I have a table called gplTable with a huge text column (varchar(8000)) and an identity column, then at points in the sp where I need to inspect values, I insert into gplTable the string values I want to inspect. With this, you will at least be able to see exactly what is being passed into the sp.

good luck, it is a real pig of a problem !

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 18:34:07

Tell me about it! lol. I've been tearing my hair out over this one! (Yes, and I know curses on me for using cursors! lol)

Here's the SP code and below is the UDF I'm using (I've tested the UDF and it works properly too)

declare @TRACKINGNUMBER varchar(6)
declare @SHIPTOCODES varchar(4200)
declare @RATECODE varchar(2)
declare @SIGNATURE varchar(20)

--350 SHIPTOCODES - max allowable is 363 (includes comma delimiters)
SET @SHIPTOCODES='A-CHAW004,A-CHAW001,A-CHAW005,A-CHAW003,CKALT 048,CKALT 039,CKALT 043,CKALT 044,CKALT 037,CKALT 010,A-CHAN002,A-CHAN003,A-CHAN006,ACHAN 003,ACHAN 001,JONPAR001,A&AAUT001,ACHWIN006,ACHWIN019,ACHWIN018,ACHWIN005,ACHWIN013,ACHWIN017,MTNT 005,CFCWR 003,7JOURS001,CFTRR 059,CFTRR 060,CFTRR 056,CFTRR 055,CFTRR 042,CFTRR 041,CFTRR 035,CFTRR 046,CFTRR 047,CFTRR 036,CFTRR 034,CHEOR 001,55TH 001,4SEASO001,3RDQU 001,24IMAG001,FOXQUE001,FOXHOM002,FOXHO2001,FOXHOM001,FOXHO2004,FOXHO2003,FOXZZZ016,FOXZZZ015,FOXZZZ014,FOXZZZ013,FOXZZZ012,FOXZZZ011,FOXZZZ001,FOXZZZ003,FOXZZZ024,2ROOMS001,TORLTD001,ONTLTD001,MONTLT001,12345 001,100000001,MIJO 001,TSNT 046,ACLK 011,GREY 031,TVAPUB001,CFBRR 006,CFQCT 003,CFQCT 002,VANCOP004,VANCOP003,VANCOP002,CITYVA001,NEWWI 001,Z103 001,CIVTT 040,MITVT 009,MITVT 029,MITVT 018,TEST 001,TEST 012,TEST 011,TEST 010,TEST 009,TEST 008,TEST 007,TEST 005,NATPOS002,TESTR 001'

SET @TRACKINGNUMBER='AAA006'
SET @RATECODE='0'
SET @SIGNATURE='KITTY'

SELECT * INTO #TEMP_TABLE FROM fulfillment.dbo.iter_charlist_to_table(@SHIPTOCODES, ',') ORDER BY NSTR

DECLARE @SHIPTOCODE varchar(9)
DECLARE @ATTENTIONLASTNAME varchar(60)
DECLARE @ATTENTIONFIRSTNAME varchar(30)
DECLARE @SHIPVIA varchar(2)
DECLARE @PROVINCESEQUENCECODE varchar(5)

DECLARE @count int
DECLARE @i int
DECLARE @temp int

DECLARE this_cursor CURSOR
LOCAL
DYNAMIC
FOR SELECT SHIPTOCODE,ATTENTIONLASTNAME,ATTENTIONFIRSTNAME,SHIPVIA,PROVINCESEQUENCECODE,SIGNATURE FROM #TEMP_TABLE,RECIPIENTLIST WHERE #TEMP_TABLE.NSTR=RECIPIENTLIST.SHIPTOCODE ORDER BY SHIPTOCODE

OPEN this_cursor

SET @count=(SELECT count(*) FROM #TEMP_TABLE)

SET @i=1
SET @temp=0

WHILE(@i<=@count)
BEGIN
IF @i=1
FETCH FIRST FROM this_cursor INTO @SHIPTOCODE,@ATTENTIONLASTNAME,@ATTENTIONFIRSTNAME,@SHIPVIA,@PROVINCESEQUENCECODE,@SIGNATURE
ELSE
FETCH NEXT FROM this_cursor INTO @SHIPTOCODE,@ATTENTIONLASTNAME,@ATTENTIONFIRSTNAME,@SHIPVIA,@PROVINCESEQUENCECODE,@SIGNATURE

SET @temp=(SELECT count(*) FROM FULFILLMENT.DBO.SHIPPINGINFORMATION WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE=@SHIPTOCODE)
IF @temp<=0
INSERT INTO FULFILLMENT.DBO.SHIPPINGINFORMATION VALUES (@TRACKINGNUMBER,@SHIPTOCODE,@ATTENTIONLASTNAME,@ATTENTIONFIRSTNAME,@SHIPVIA,@PROVINCESEQUENCECODE,@SIGNATURE,GETDATE())
ELSE
UPDATE FULFILLMENT.DBO.SHIPPINGINFORMATION SET TRACKINGNUMBER=@TRACKINGNUMBER,SHIPTOCODE=@SHIPTOCODE,ATTENTIONLASTNAME=@ATTENTIONLASTNAME,ATTENTIONFIRSTNAME=@ATTENTIONFIRSTNAME,SHIPVIA=@SHIPVIA,PROVINCESEQUENCECODE=@PROVINCESEQUENCECODe,SIGNATURE=@SIGNATURE,UPDATED=GETDATE() WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE=@SHIPTOCODE

EXECUTE spcloADDBOXFORRECIPIENTTOORDER @TRACKINGNUMBER,@SHIPTOCODE,@RATECODE,1,"1X1X1",0.00,@SIGNATURE

SET @temp=(SELECT count(*) FROM FULFILLMENT.DBO.SHIPPINGINFORMATION WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001')
IF @temp>0
DELETE FROM SHIPPINGINFORMATION WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001'

SET @temp=(SELECT count(*) FROM FULFILLMENT.DBO.BOXES WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001')
IF @temp>0
DELETE FROM BOXES WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001'

SET @i=@i+1
END

CLOSE this_cursor
DEALLOCATE this_cursor
SELECT * FROM #TEMP_TABLE ORDER BY NSTR
GO

UDF------------------------------------------------------------
--Iterative method of working with SQL Arrays by Erland Sommarskog, SQL Server MVP.
--http://www.algonet.se/~sommar/arrays-in-sql.html#iterative

CREATE FUNCTION iter_charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END

SET @leftover = @tmpstr
END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END

Appreciate all your help!

Kitty

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 18:41:25

oops gave you an old copy of the SP - Here's the correct one - sorry.

declare @TRACKINGNUMBER varchar(6)
declare @SHIPTOCODES varchar(4200)
declare @RATECODE varchar(2)
declare @SIGNATURE varchar(20)

--350 SHIPTOCODES - max allowable is 363
SET @SHIPTOCODES='A-CHAW004,A-CHAW001,A-CHAW005,A-CHAW003,CKALT 048,CKALT 039,CKALT 043,CKALT 044,CKALT 037,CKALT 010,A-CHAN002,A-CHAN003,A-CHAN006,ACHAN 003,ACHAN 001,JONPAR001,A&AAUT001,ACHWIN006,ACHWIN019,ACHWIN018,ACHWIN005,ACHWIN013,ACHWIN017,MTNT 005,CFCWR 003,7JOURS001,CFTRR 059,CFTRR 060,CFTRR 056,CFTRR 055,CFTRR 042,CFTRR 041,CFTRR 035,CFTRR 046,CFTRR 047,CFTRR 036,CFTRR 034,CHEOR 001,55TH 001,4SEASO001,3RDQU 001,24IMAG001,FOXQUE001,FOXHOM002,FOXHO2001,FOXHOM001,FOXHO2004,FOXHO2003,FOXZZZ016,FOXZZZ015,FOXZZZ014,FOXZZZ013,FOXZZZ012,FOXZZZ011,FOXZZZ001,FOXZZZ003,FOXZZZ024,2ROOMS001,TORLTD001,ONTLTD001,MONTLT001,12345 001,100000001,MIJO 001,TSNT 046,ACLK 011,GREY 031,TVAPUB001,CFBRR 006,CFQCT 003,CFQCT 002,VANCOP004,VANCOP003,VANCOP002,CITYVA001,NEWWI 001,Z103 001,CIVTT 040,MITVT 009,MITVT 029,MITVT 018,TEST 001,TEST 012,TEST 011,TEST 010,TEST 009,TEST 008,TEST 007,TEST 005,NATPOS002,TESTR 001'

SET @TRACKINGNUMBER='AAA006'
SET @RATECODE='0'
SET @SIGNATURE='KITTY'

SELECT * INTO #TEMP_TABLE FROM fulfillment.dbo.iter_charlist_to_table(@SHIPTOCODES, ',') ORDER BY NSTR

DECLARE @SHIPTOCODE varchar(9)
DECLARE @ATTENTIONLASTNAME varchar(60)
DECLARE @ATTENTIONFIRSTNAME varchar(30)
DECLARE @SHIPVIA varchar(2)
DECLARE @PROVINCESEQUENCECODE varchar(5)

DECLARE @count int
DECLARE @i int
DECLARE @temp int

DECLARE this_cursor CURSOR
LOCAL
DYNAMIC
FOR SELECT SHIPTOCODE,ATTENTIONLASTNAME,ATTENTIONFIRSTNAME,SHIPVIA,PROVINCESEQUENCECODE,SIGNATURE FROM #TEMP_TABLE,RECIPIENTLIST WHERE #TEMP_TABLE.NSTR=RECIPIENTLIST.SHIPTOCODE ORDER BY SHIPTOCODE

OPEN this_cursor

SET @count=(SELECT count(*) FROM #TEMP_TABLE)

SET @i=1
SET @temp=0

WHILE(@i<=@count)
BEGIN
IF @i=1
FETCH FIRST FROM this_cursor INTO @SHIPTOCODE,@ATTENTIONLASTNAME,@ATTENTIONFIRSTNAME,@SHIPVIA,@PROVINCESEQUENCECODE,@SIGNATURE
ELSE
FETCH NEXT FROM this_cursor INTO @SHIPTOCODE,@ATTENTIONLASTNAME,@ATTENTIONFIRSTNAME,@SHIPVIA,@PROVINCESEQUENCECODE,@SIGNATURE

SET @temp=(SELECT count(*) FROM FULFILLMENT.DBO.SHIPPINGINFORMATION WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE=@SHIPTOCODE)
IF @temp<=0
INSERT INTO FULFILLMENT.DBO.SHIPPINGINFORMATION VALUES (@TRACKINGNUMBER,@SHIPTOCODE,@ATTENTIONLASTNAME,@ATTENTIONFIRSTNAME,@SHIPVIA,@PROVINCESEQUENCECODE,@SIGNATURE,GETDATE())
ELSE
UPDATE FULFILLMENT.DBO.SHIPPINGINFORMATION SET TRACKINGNUMBER=@TRACKINGNUMBER,SHIPTOCODE=@SHIPTOCODE,ATTENTIONLASTNAME=@ATTENTIONLASTNAME,ATTENTIONFIRSTNAME=@ATTENTIONFIRSTNAME,SHIPVIA=@SHIPVIA,PROVINCESEQUENCECODE=@PROVINCESEQUENCECODe,SIGNATURE=@SIGNATURE,UPDATED=GETDATE() WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE=@SHIPTOCODE

EXECUTE spcloADDBOXFORRECIPIENTTOORDER @TRACKINGNUMBER,@SHIPTOCODE,@RATECODE,1,"1X1X1",0.00,@SIGNATURE

SET @i=@i+1
END

SET @temp=(SELECT count(*) FROM FULFILLMENT.DBO.SHIPPINGINFORMATION WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001')
IF @temp>0
DELETE FROM SHIPPINGINFORMATION WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001'

SET @temp=(SELECT count(*) FROM FULFILLMENT.DBO.BOXES WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001')
IF @temp>0
DELETE FROM BOXES WHERE TRACKINGNUMBER=@TRACKINGNUMBER AND SHIPTOCODE='DUMMY!001'

CLOSE this_cursor
DEALLOCATE this_cursor
SELECT * FROM #TEMP_TABLE ORDER BY NSTR
GO

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-08 : 18:45:54

By the way if anyone has any suggestions on how I can avoid using the cursors in this SP I'd be glad to incorporate it.

gpl
Posting Yak Master

195 Posts

Posted - 2004-03-08 : 19:31:34

there isnt anything that jumps out at me
I notice you end the procedure by returning the contents of the temp table - do you get back everything you expect to be in it ?

Does this procedure really work in query analyser ? the cursor creation function uses an equi-join to select the records - ie only those values in the temp table that are also on the RECIPIENTLIST table are worked on

as far as removing the cursor is concerned, how about declaring explicitly the temp table, and add an extra column - a flag to say whether or not the row exists in the SHIPPINGINFORMATION table, something like (and you will have to excuse the simplistic code, its gone midnight here)

create table #Temp_Table (
listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000),
WasFound Int
)

then to populate it, something along the lines of

Insert #Temp_Table
Select
listpos,
str,
nstr,
Case When s.TRACKINGNUMBER Is NULL Then 0 Else 1 End

FROM #TEMP_TABLE
JOIN FULFILLMENT.DBO.RECIPIENTLIST r on #TEMP_TABLE.NSTR=r.SHIPTOCODE
LEFT JOIN FULFILLMENT.DBO.SHIPPINGINFORMATION s where s.TRACKINGNUMBER=r.TRACKINGNUMBER AND s.SHIPTOCODE=r.SHIPTOCODE

The theory here is that it merges the code from both the initial population of the temp table and the select code from the cursor, I might have got the logic a bit wrong. If there is a matching row on the SHIPPINGINFORMATION table, then a 1 is inserted in the temp table. If this is NULL, then no value was found and a 0 is inserted.

Now with 2 simple update / insert statements you can do your processing (ok, maybe 3 passes for the call to the SP after)
along the lines of

INSERT INTO FULFILLMENT.DBO.SHIPPINGINFORMATION
Select r.TRACKINGNUMBER,r.@SHIPTOCODE,r.ATTENTIONLASTNAME,r.ATTENTIONFIRSTNAME,r.SHIPVIA,r.PROVINCESEQUENCECODE,r.SIGNATURE,GETDATE()
from
FROM #TEMP_TABLE
JOIN FULFILLMENT.DBO.RECIPIENTLIST r on #TEMP_TABLE.NSTR=r.SHIPTOCODE
WHERE WasFound = 0

the update is similar (but I forget the actual syntax for doing an udate from a table joined to another, its on this site somewhere), but importantly you would add the condition to the end of the where clause ... WasFound = 1

Graham

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-08 : 20:52:47

Take a look at these articles:

http://www.sqlteam.com/searchresults.asp?SearchTerms=csv

There are a few that show how to parse CSVs and insert them into a table. One of them even does it in one operation, no cursors, no temp tables.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-09 : 01:29:03

Hmmm...looking through your procedure, I noticed that you are assigning the input variable @SHIPTOCODES to a static list in the first line. Then why are you passing a value for this variable from the ASP page?


--350 SHIPTOCODES - max allowable is 363
SET @SHIPTOCODES='A-CHAW004,A-CHAW001,A-CHAW005,A-CHAW003,CKALT 048,CKALT 039,CKALT 043,CKALT 044,...

But, of course, this still doesn't explain why it processes only 29 codes when you call the SP from your middleware component.

OS

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-03-09 : 03:25:42

quote:
Originally posted by kittyhundal
UDF------------------------------------------------------------
--Iterative method of working with SQL Arrays by Erland Sommarskog, SQL Server MVP.
--http://www.algonet.se/~sommar/arrays-in-sql.html#iterative

Have you actually read the whole article?
Erland has provided some very fine solutions for this problem, and the rest of his site is also worth reading as it might affect your problem, too.

--Frank
http://www.insidesql.de

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-09 : 10:08:46

The code posted is my test code. It was used to ensure that the procedure worked properly. When it's called that code is commented out and the data is sent through the component.

quote:
Originally posted by mohdowais

Hmmm...looking through your procedure, I noticed that you are assigning the input variable @SHIPTOCODES to a static list in the first line. Then why are you passing a value for this variable from the ASP page?

--350 SHIPTOCODES - max allowable is 363
SET @SHIPTOCODES='A-CHAW004,A-CHAW001,A-CHAW005,A-CHAW003,CKALT 048,CKALT 039,CKALT 043,CKALT 044,...

But, of course, this still doesn't explain why it processes only 29 codes when you call the SP from your middleware component.

OS

kittyhundal
Starting Member

25 Posts

Posted - 2004-03-09 : 10:11:11

I did read the article, but perhaps I missed something? I didn't notice anything about this particular problem. I'll check it out again but perhaps you can also refer me to the section(s) you're talking about?

quote:
Originally posted by Frank Kalis

quote:
Originally posted by kittyhundal
UDF------------------------------------------------------------
--Iterative method of working with SQL Arrays by Erland Sommarskog, SQL Server MVP.
--http://www.algonet.se/~sommar/arrays-in-sql.html#iterative

Have you actually read the whole article?
Erland has provided some very fine solutions for this problem, and the rest of his site is also worth reading as it might affect your problem, too.

--Frank
http://www.insidesql.de

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources