Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need some function help, debuggin, destupifying.... Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 12:25 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
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.
Post #1395277
Posted Tuesday, December 11, 2012 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 12,963, Visits: 32,510
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1395281
Posted Tuesday, December 11, 2012 12:53 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
Yeah, that's right....

Before I use your spiffily written Scalar, how bad is mine? I can take it....
Post #1395284
Posted Tuesday, December 11, 2012 1:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1395293
Posted Tuesday, December 11, 2012 1:05 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
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.
Post #1395296
Posted Tuesday, December 11, 2012 1:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 12,963, Visits: 32,510
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1395304
Posted Tuesday, December 11, 2012 1:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1395309
Posted Tuesday, December 11, 2012 1:51 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
You guys...(gals) is da bomb...(sniff sniff)... I'm going to name my next kid after you two....
Post #1395313
Posted Tuesday, December 11, 2012 3:05 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:39 PM
Points: 200, Visits: 358
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....
Post #1395340
Posted Tuesday, December 11, 2012 3:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1395345
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse