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

  • 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.

  • 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!

  • Yeah, that's right....

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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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!

  • 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
  • You guys...(gals) is da bomb...(sniff sniff)... I'm going to name my next kid after you two....

  • 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.... 😀

  • 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
  • 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....

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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