Convert 2005 table valued function to 2000

Question

Post reply

Convert 2005 table valued function to 2000

allen davidson

SSCertifiable

Points: 7042
More actions
October 2, 2007 at 3:31 am

#175614

Hi there, I have a table valued function writen in 2005 which I can join to using CROSS APPLY.
I ned to port the code to SQL 2000 where I dont have CROSS APPLY. So I thought write to a Table and then JOIN later on.
However I get an error message on my converted code.
Original 2005 code is
CREATE FUNCTION [dbo].[UnpackString2] (@cl_accno CHAR(6), @enq_date DATETIME, @broch_req VARCHAR(255))
RETURNS @broch_req_table TABLE(cl_accno CHAR(6), enq_date DATETIME, broch_req VARCHAR(255))
AS
BEGIN
DECLARE @mystring varchar(255), @myword varchar(50)
DECLARE @i int,@j int
SET @mystring = @broch_req
SELECT @i = 0,@j = 0
IF SUBSTRING (@mystring, LEN (@mystring), 1) <> ','
BEGIN
SELECT @mystring = @mystring + ','
END
SELECT @i = CHARINDEX (',', @mystring, @i + 1)
WHILE @i > 0
BEGIN
SELECT @myword = SUBSTRING (@mystring, @j-2+1, (@i - @j-2) -1)
INSERT @broch_req_table VALUES (@cl_accno, @enq_date, LTRIM(@myword))
SELECT @j-2 = @i
SELECT @i = CHARINDEX (',' , @mystring, @i + 1)
END
END
My attempt to convert to 2000 looks like this
CREATE TABLE BrochReqTable2
(
clacc_no CHAR(6),
enq_date DATETIME,
broch_req VARCHAR(255))
GO
CREATE FUNCTION [dbo].[UnpackString2] (@cl_accno CHAR(6), @enq_date DATETIME, @broch_req VARCHAR(255))
--RETURNS @broch_req_table TABLE(cl_accno CHAR(6), enq_date DATETIME, broch_req VARCHAR(255))
RETURNS INT
AS
BEGIN
DECLARE @mystring varchar(255), @myword varchar(50)
DECLARE @i int,@j int
SET @mystring = @broch_req
SELECT @i = 0,@j = 0
IF SUBSTRING (@mystring, LEN (@mystring), 1) <> ','
BEGIN
SELECT @mystring = @mystring + ','
END
SELECT @i = CHARINDEX (',', @mystring, @i + 1)
WHILE @i > 0
BEGIN
SELECT @myword = SUBSTRING (@mystring, @j-2+1, (@i - @j-2) -1)
-- INSERT @broch_req_table VALUES (@cl_accno, @enq_date, LTRIM(@myword))
INSERT BrochReqTable2 VALUES (@cl_accno, @enq_date, LTRIM(@myword))
SELECT @j-2 = @i
SELECT @i = CHARINDEX (',' , @mystring, @i + 1)
END
RETURN 0
END
Error mesage is
Msg 443, Level 16, State 15, Procedure UnpackString2, Line 21
Invalid use of side-effecting or time-dependent operator in 'INSERT' within a function.
Any help gratefully received.
Allen

Viewing 12 posts - 1 through 11 (of 11 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

See second version of fnParseList function here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

N 56°04'39.16"
E 12°55'05.25"

allen davidson SSCertifiable Points: 7042 More actions · Answer 2

Hi Peter, Thanks for the reply.

My 2005 function was trying to build a TABLE variable taht I could join to using CROSS APPLY.

Because I can't CROSS APPLY in sql2k I tried to make a TABLE.

I have since read that I cannot do an INSERT into anything but a TABLE variable in a function.

So (sadly) i've cobbled it into a stored proc that (worse still) uses a cursor. Feel like I'm going backwards.

Allen

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

Heh... You did go backwards, indeed...

Instead of me trying to figure out what the function is supposed to do, please tell us so we can tell you how to do it. Include some before'n'after data, please.

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

allen davidson SSCertifiable Points: 7042 More actions · Answer 4

Hi Jeff, Thanks for your interest.

The function takes a comma delimited string and splits it up. It takes three parameters 1. an account number 'clacc_no' 2. a date and 3. the string to parse.

Input data looks like this.

'123456' '1-JAN-2007' 'ab,cd,ef,gh'

'654321' '1-JAN-2007' 'xx,yy'

approx 200,000 rows like this

Output wants to look like this

'123456' '1-JAN-2007' 'ab'

'123456' '1-JAN-2007' 'cd'

'123456' '1-JAN-2007' 'ef'

'123456' '1-JAN-2007' 'gh'

'654321' '1-JAN-2007' 'xx'

'654321' '1-JAN-2007' 'yy'

The 2005 code returns a table variable that the input data can be joined to however in 2000 I cant see how to do it (but I bet it can) without a cursor.

Allen

David Burrows SSC Guru Points: 65143 More actions · Answer 5

Where does the data come from?

If from a table then you can use this in a subquery

SELECTa.AccountNo,a.[Date],SUBSTRING(',' + a.[String] + ',', n.Number + 1,

CHARINDEX(',', ',' + a.[String] + ',', n.Number + 1) - n.Number - 1)

AS [Value]

FROM

a

INNER JOIN Numbers n

ON n.Number > 0

AND SUBSTRING(',' + a.[String] + ',', n.Number, 1) = ','

AND n.Number < LEN(',' + a.[String] + ',')

You will need to create a Numbers table, there are pleanty of examples on this site

Far away is close at hand in the images of elsewhere.
Anon.

allen davidson SSCertifiable Points: 7042 More actions · Answer 6

Hi David,

Thankls for this, data does indeed come from a table. I'll give it a try, I _think_ I understand what you are doing.

Allen

allen davidson SSCertifiable Points: 7042 More actions · Answer 7

Hi David,

That is very cool, I have not used a numbers table before. I see by running it that the Number points to the start of each delimited string.

I guess that it joins the table to parse to the numbers table multiple times because the join conditions

ON Number > 0 -- understand this

AND SUBSTRING(',' + a.[String] + ',', n.Number, 1) = ',' -- I read this joins on positions with a comma ? however outputting n in the select list points to the start of each delimited string? what am i missing?

AND n.Number < LEN(',' + a.[broch_req] + ',') -- dont run off the end

David Burrows SSC Guru Points: 65143 More actions · Answer 8

I read this joins on positions with a comma

Correct 🙂

however outputting n in the select list points to the start of each delimited string

n.Number is the char position of the first char of each delimited string in the input

Therefore

,SUBSTRING(',' + a.[String] + ',', -- Input string with forced delims to prevent errors

n.Number + 1, -- First char of delimited string

CHARINDEX(',', ',' + a.[String] + ',', n.Number + 1) - n.Number - 1) -- length of delimited string

Far away is close at hand in the images of elsewhere.
Anon.

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

A big key here is that David DIDN'T even use a function (although you could if it's for RBAR GUI code)... it splits a whole table's worth of information in a single select and you can join against that result.

Here's one way to make a permanent copy of a "Tally" or "Numbers" table... change to suit your needs...

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

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

allen davidson SSCertifiable Points: 7042 More actions · Answer 10

A big thanks David & Jeff.

I _do_ now understand the code, I was focusing (obsessing) on the join and not considering the SUBSTRING

Another good day at school!

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

Heh... thanks for the "apple" 😛

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