December 11, 2012 at 12:25 pm
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 @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.
December 11, 2012 at 12:47 pm
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
December 11, 2012 at 12:53 pm
Yeah, that's right....
Before I use your spiffily written Scalar, how bad is mine? I can take it....
December 11, 2012 at 1:01 pm
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. 😀
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
December 11, 2012 at 1:05 pm
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 😛
Crusty.
December 11, 2012 at 1:33 pm
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
December 11, 2012 at 1:42 pm
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
December 11, 2012 at 1:51 pm
You guys...(gals) is da bomb...(sniff sniff)... I'm going to name my next kid after you two....
December 11, 2012 at 3:05 pm
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.... 😀
December 11, 2012 at 3:41 pm
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
December 11, 2012 at 4:20 pm
Oh.... Wow... I have a friend that can't stop his team in India from calling him Brain.... I'm sure you can figure that one out...
I'll have to tell you how I got the moniker of Crusty.... D'OH! and that is a hint....
December 11, 2012 at 6:38 pm
GilaMonster (12/11/2012)
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.
A perplexing enigma now demystified. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply