SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need some function help, debuggin, destupifying....


Need some function help, debuggin, destupifying....

Author
Message
CptCrusty1
CptCrusty1
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 387
I SUCK I righting funtions.. I just haven't had to do it much. I have a range of numbers in a column that needs to be broken out into a comma seperated list....

I have to fields, a beginning Number (BegNo) and an Ending Number (EndNo) that are a range of numbers... Initially, they (the people with offices and doors) say just concat. No prob. No they want them broken out... SO.....

BegNo EndNo
1 4

Becomes:
BegNo EndNo Range
1 4 1,2,3,4

I started writing a function, but I seem to get brain damage when I get there.... I could do this as RBAR, but it's 500k records to review.


Create Function fn_ExpandLotRange
(
@BLN varchar(5),
@ELN varchar(5)
)
Returns varchar(1000)
AS
Begin

Declare @TLN int --Total numbers needing to be added
Declare @Loop Int --Looping intervals
Declare @End Int --Ending Loop Interval

Declare @Str varchar(1000) --Results are built in here

IF @BLN Like '%[a-z]%'
RETURN ''
ELSE IF @ELN Like '%[a-z]%'
RETURN ''
Else
Set @TLN = convert(int,@ELN) - convert(Int,@BLN) +1

Set @Str = @Str + @BLN + ','
Set @TLN = @TLN - 1

While @TLN >0
BEGIN
Set @STR = @Str+Convert(varchar(5),(Convert(int,@BLN)+1))
Print @STR
END
END

Return @STR;
END



Here's where I am so far....I'm getting the following Errors.


Msg 156, Level 15, State 1, Procedure fn_ExpandLotRange, Line 34
Incorrect syntax near the keyword 'Return'.
Msg 102, Level 15, State 1, Procedure fn_ExpandLotRange, Line 35
Incorrect syntax near 'END'.



Any guidance would be helpful.
Thanks
Crusty.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28409 Visits: 39963
ok capn; you just need a function that expands the integers that should bebetween tow numbers as a comma delimited list, is that right?

here's both an ITVF and a scalar example:

CREATE FUNCTION fn_ExpandLotRangeitvf( @start int,@end int)
returns table
AS
RETURN
SELECT STUFF( (SELECT ',' + convert(varchar,MiniTally.n )
FROM (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n between @start and @end
FOR XML PATH('') ) ,1,1,'') As String
GO

SELECT * from fn_ExpandLotRangeITVF(2,8)
GO

CREATE FUNCTION fn_ExpandLotRange( @start int,@end int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @results varchar(1000)
SELECT @results = STUFF( (SELECT ',' + convert(varchar,MiniTally.n )
FROM (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n between @start and @end
FOR XML PATH('') ) ,1,1,'')
return @results
END

GO
SELECT dbo.fn_ExpandLotRange(3,10)



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CptCrusty1
CptCrusty1
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 387
Yeah, that's right....

Before I use your spiffily written Scalar, how bad is mine? I can take it....
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88070 Visits: 45277
CptCrusty1 (12/11/2012)
Yeah, that's right....

Before I use your spiffily written Scalar, how bad is mine? I can take it....


Err... Molasses in a Canadian winter. :-D

I would recommend you use the table-valued function, not the scalar, unless you're doing this:

SET @SomeVariable = dbo.fn_ExpandLotRange(2,8);

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


CptCrusty1
CptCrusty1
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 387
THANKS Jedi-Gail!...

I already know I suck at writing functions....I'm scarred... permanently scarred... My little children are going to wonder what happend to their daddy now.

I could do this as a procedure, but this is an area that I know I'm weak in so I'm looking for some guidance... and there's no fee for sending insults my way :-P

Crusty.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28409 Visits: 39963
no insults, just help with the logical rules of thumb for procedures and functions:

avoid loops whenever possible: that's cursor, while loops whatever;

like everything in SQL, there's more than one way to do anything, and anything set based is going to be faster...i try to think that SQL can handle a SET of Data in exactly the same amount of time it would take to process a single row.

kind of like moving one apple vs a box of apples...handing it to the next process is the same amount of time.

my examples are probably pretty good, but i'm always ready for a better technique than i'm throwing out there.;

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88070 Visits: 45277
Lowell (12/11/2012)
no insults, just help with the logical rules of thumb for procedures and functions:

avoid loops whenever possible: that's cursor, while loops whatever;


Additionally
Avoid data access in a scalar UDF unless there's no other possible way to do what you need.
Avoid scalar UDFs that will be used in the select statement of large queries, SQL calls them once per row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


CptCrusty1
CptCrusty1
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 387
You guys...(gals) is da bomb...(sniff sniff)... I'm going to name my next kid after you two....
CptCrusty1
CptCrusty1
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 387
Lewis / Gail.

Thanks Bunches for your help. Yes, I'm a dork, but I'm an appreciative dork.

I used the ITVF and discovered that my dataset has some alpha characters as well. I might have to just leave them alone for the time being, BUT... here's my result:

Select R.RecordNumber, R.BeginningLotNo, R.EndingLotNo,
CASE WHEN LTRIM(RTRIM(R.BeginningLotNo)) = LTRIM(RTRIM(R.EndingLotNo)) THEN R.BeginningLotNo
ELSE f.String
END LOT
From Ellis.LandT_3_Raw R
Cross Apply
dbo.fn_ExpandLotRangeITVF(R.BeginningLotNo, R.EndingLotNo) f
Where R.BeginningLotNo Not like '%[a-z]%'
UNION
Select R.RecordNumber, R.BeginningLotNo, R.EndingLotNo, ''
From Ellis.LandT_3_Raw R
Where R.BeginningLotNo like '%[a-z]%'



Thanks again for your help as always...

Gail, I once asked you why you're called Gila Monster. That suggests you're a scaly little beast that likes to chew on it's victims as it envenomates.... don't see that as being you... unless.... you're luring me to my death.... :-D
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88070 Visits: 45277
CptCrusty1 (12/11/2012)
Gail, I once asked you why you're called Gila Monster.


Old story.

Gail
Gila

Notice the letter ordering. Now add a friend with a mild typing problem (typed faster with her right hand than her left) and you have a nickname.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search