InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 02:29:20 GMT20http://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspx[quote][b]GilaMonster (12/11/2012)[/b][hr][quote][b]CptCrusty1 (12/11/2012)[/b][hr]Gail, I once asked you why you're called Gila Monster. [/quote]Old story.GailGilaNotice 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.[/quote]A perplexing enigma now demystified. :-PTue, 11 Dec 2012 18:38:07 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxOh.... 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....Tue, 11 Dec 2012 16:20:13 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspx[quote][b]CptCrusty1 (12/11/2012)[/b][hr]Gail, I once asked you why you're called Gila Monster. [/quote]Old story.GailGilaNotice 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.Tue, 11 Dec 2012 15:41:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxLewis / 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:[code="sql"]Select R.RecordNumber, R.BeginningLotNo, R.EndingLotNo,CASE WHEN LTRIM(RTRIM(R.BeginningLotNo)) = LTRIM(RTRIM(R.EndingLotNo)) THEN R.BeginningLotNo ELSE f.StringEND LOTFrom Ellis.LandT_3_Raw R Cross Apply dbo.fn_ExpandLotRangeITVF(R.BeginningLotNo, R.EndingLotNo) fWhere R.BeginningLotNo Not like '%[a-z]%'UNIONSelect R.RecordNumber, R.BeginningLotNo, R.EndingLotNo, ''From Ellis.LandT_3_Raw R Where R.BeginningLotNo like '%[a-z]%'[/code]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.... :-DTue, 11 Dec 2012 15:05:35 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxYou guys...(gals) is da bomb...(sniff sniff)... I'm going to name my next kid after you two....Tue, 11 Dec 2012 13:51:03 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspx[quote][b]Lowell (12/11/2012)[/b][hr]no insults, just help with the logical rules of thumb for procedures and functions:avoid loops whenever possible: that's cursor, while loops whatever; [/quote]AdditionallyAvoid 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.Tue, 11 Dec 2012 13:42:32 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxno 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.;Tue, 11 Dec 2012 13:33:24 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxTHANKS 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 :-PCrusty.Tue, 11 Dec 2012 13:05:27 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspx[quote][b]CptCrusty1 (12/11/2012)[/b][hr]Yeah, that's right....Before I use your spiffily written Scalar, how bad is mine? I can take it....[/quote]Err... Molasses in a Canadian winter. :-DI would recommend you use the table-valued function, not the scalar, unless you're doing this:SET @SomeVariable = dbo.fn_ExpandLotRange(2,8);Tue, 11 Dec 2012 13:01:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxYeah, that's right....Before I use your spiffily written Scalar, how bad is mine? I can take it....Tue, 11 Dec 2012 12:53:59 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxok 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:[code]CREATE FUNCTION fn_ExpandLotRangeitvf( @start int,@end int)returns tableASRETURN 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 StringGO SELECT * from fn_ExpandLotRangeITVF(2,8) GO CREATE FUNCTION fn_ExpandLotRange( @start int,@end int)RETURNS varchar(1000)AS BEGINDECLARE @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 ENDGOSELECT dbo.fn_ExpandLotRange(3,10)[/code]Tue, 11 Dec 2012 12:47:38 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1395277-149-1.aspxI 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 4Becomes:BegNo EndNo Range 1 4 1,2,3,4I 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.[code="sql"]Create Function fn_ExpandLotRange( @BLN varchar(5), @ELN varchar(5))Returns varchar(1000)ASBegin 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[/code]Here's where I am so far....I'm getting the following Errors.Msg 156, Level 15, State 1, Procedure fn_ExpandLotRange, Line 34Incorrect syntax near the keyword 'Return'.Msg 102, Level 15, State 1, Procedure fn_ExpandLotRange, Line 35Incorrect syntax near 'END'.Any guidance would be helpful.ThanksCrusty.Tue, 11 Dec 2012 12:25:48 GMTCptCrusty1