Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #127 > unrolled thread

Stored Procedure Code Critique Requested

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Stored Procedure Code Critique Requested Gene Wirchenko <genew@ocis.net> - 2011-04-05 14:26 -0700
    Re: Stored Procedure Code Critique Requested Erland Sommarskog <esquel@sommarskog.se> - 2011-04-06 07:43 +0000
      Re: Stored Procedure Code Critique Requested Gene Wirchenko <genew@ocis.net> - 2011-04-06 11:43 -0700
        Re: Stored Procedure Code Critique Requested Erland Sommarskog <esquel@sommarskog.se> - 2011-04-06 23:58 +0200
    Re: Stored Procedure Code Critique Requested Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-06 21:15 +0200
      Re: Stored Procedure Code Critique Requested Gene Wirchenko <genew@ocis.net> - 2011-04-06 14:59 -0700
        Re: Stored Procedure Code Critique Requested Erland Sommarskog <esquel@sommarskog.se> - 2011-04-07 00:05 +0200
    Re: Stored Procedure Code Critique Requested Philipp Post <post.philipp@googlemail.com> - 2011-04-12 06:14 -0700

#127 — Stored Procedure Code Critique Requested

Dear SQLers:

     I have finished writing my first involved stored procedure.  It
makes strings nice.  It works, but it might be better.

     Have I, for example, missed out on functions that could have made
it easier?

     replace() does not work as I expected when the search string is
partly contained in the result of a replace
          replace(N'three   spaces',N'  ',N' ')
will result in
          N'three  spaces'
not
          N'three spaces'
That is why I loop.

     I also do not know how SQL Server handles multi-line values.  I
am assuming there is a CR (or UniCode equivalent) to delimit lines. In
case there could be LFs, I eliminate them in the line flagged
"--*****TRY".

     Test code follows.

     What do you think?

***** Start of Code *****
-- NiceString
-- This procedure makes a string "nice".  "nice" means that each line
of the
-- string has:
--   1) no occurrences of <tab> (each replaced with space), and
--   2) no leading spaces (removed) (optional),
--   3) no trailing spaces (removed) (optional),
--   4) no two spaces in a row (squeezed to one space).
-- The four points are applied in order 1-4.

-- "cLines": count of lines in the string.  This is defined as the
number of
-- line delimiters in the string + 1. e.g. (where <LD> is a line
delimiter):
--      N'' has one line.
--      N'kitty' has one line.
--      N'This is the first line,<LD>
--        and this is the last line.' has two lines.
--      N'This is the first line,<LD>
--        this is the middle line,<LD>
--        and this is the last line.' has three lines.

-- If mLines=0, returns cLines.
-- If mLines>0 and cLines<=mLines, returns cLines.
-- If mLines>0 and cLines>mLines, returns a value >mLines.

create procedure NiceString
 @strNiceMe nvarchar(max) output,  -- the string to make nice and the
result
 @mLines int=0,     -- maximum number of lines permitted in string
  -- If 0, the string can have any number of lines.  If >0 and the
string
  -- turns out to have more lines, processing will be aborted and the
string
  -- will not be changed.
 @fTrimLead bit=1,  -- trim the leading blanks on each line?  0: no,
1: yes
 @fTrimTrail bit=1  -- trim the trailing blanks on each line?  0: no,
1: yes
with recompile
as
   begin
   declare @strWork nvarchar(max)=@strNiceMe  -- working version of
string
   declare @strOut nvarchar(max)=N''  -- result string so far
   declare @cLines int=1  -- number of lines (always at least one)
   declare @strLine nvarchar(max)  -- the current line being processed
   declare @chrLineDelim nchar(1)=nchar(13)  -- CR
   declare @chrLF nchar(1)=nchar(10)  -- LF
   declare @chrTAB nchar(1)=nchar(9)  -- TAB

   declare @iLineDelim int     -- where next line delimiter is in
@strWork

   declare @cLeading int       -- how many leading spaces in line
   declare @cTrailing int      -- how many trailing spaces in line

   declare @strPrev nvarchar(max)     -- previous version of line

   while len(@strWork)>0 and (@mLines=0 or @cLines<=@mLines)
      begin
      -- Get the next line and remove it from strWork.
      select @iLineDelim=patindex(N'%'+@chrLineDelim+N'%',@strWork)
      if @iLineDelim>0
         begin
         select @strLine=left(@strWork,@iLineDelim-1)
         select
@strWork=substring(@strWork,@iLineDelim+1,len(@strWork))
         select @cLines=@cLines+1
         end
      else
         begin
         select @strLine=@strWork
         select @strWork=N''
         end

      -- Trimming and removal of tabs
      select @strLine=replace(@strLine,@chrLF,N'')     --*****TRY
      select @strLine=replace(@strLine,@chrTAB,N' ')
      -- If either end is not to be trimmed, then the leading and
trailing
      -- spaces will have to be restored after the line is squeezed.
      if @fTrimLead=1 and @fTrimTrail=1
         begin
         select @cLeading=0
         select @cTrailing=0
         end
      else
         begin
         select @cLeading=patindex(N'%[^ ]%',@strLine)
         if @cLeading=len(@strLine)  -- Line is only blanks.
            select @cTrailing=0
         else
            select @cTrailing=patindex(N'%[^ ]%',reverse(@strLine))
         end
      select @strLine=ltrim(rtrim(@strLine))

      -- Compression of consecutive spaces
      select @strPrev=N''
      while @strLine<>@strPrev
         begin
         select @strPrev=@strLine
         select @strLine=replace(@strLine,N'  ',N' ')
         end

      -- Add back any leading or trailing spaces being kept.
      if @fTrimLead=0
         select @strLine=space(@cLeading)+@strLine
      if @fTrimTrail=0
         select @strLine=@strLine+space(@cTrailing)

      -- Add modified line to @strOut.
      if len(@strOut)>0
         select @strOut=@strOut+@chrLineDelim
      select @strOut=@strOut+@strLine
      end

   if @mLines=0 or @cLines<=@mLines  -- Replace original string if no
error.
      select @strNiceMe=@strOut
   return @cLines
   end
***** End of Code *****

***** Start of Test Code *****
declare @strTestValue nvarchar(max)=
 N'     This is    an		example.     '
declare @strTest nvarchar(max)
declare @RetVal int

select N'raw string',N'>'+@strTestValue+N'<'

select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output
select 'defaults not given',@RetVal as N'Ret Val',N'>'+@strTest+N'<'

select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,1,1
select 'defaults given',@RetVal as N'Ret Val',N'>'+@strTest+N'<'

select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,0,0
select '0,0,0',@RetVal as N'Ret Val',N'>'+@strTest+N'<'

select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,1,0
select '0,1,0',@RetVal as N'Ret Val',N'>'+@strTest+N'<'

select @strTest=@strTestValue
execute @RetVal=NiceString @strTest output,0,0,1
select '0,0,1',@RetVal as N'Ret Val',N'>'+@strTest+N'<'

select @strTest=N''
execute @RetVal=NiceString @strTest output
select 'empty, defaults not given',@RetVal as N'Ret
Val',N'>'+@strTest+N'<'
select
 len(@strTest) as Length,
 patindex(N'%'+nchar(13)+N'%',@strTest) as N'First LD'

select @strTest=
 N'     This   is   a   three   line   string.   '+nchar(13)+
 N'     This is the   second line.   '+nchar(13)+
 N'     This is the   last line.   '
execute @RetVal=NiceString @strTest output
select 'defaults not given',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select
 len(@strTest) as Length,
 patindex(N'%'+nchar(13)+N'%',@strTest) as N'First LD'

select @strTest=
 N'     This   is   a   three   line   string.   '+nchar(13)+
 N'     This is the   second line.   '+nchar(13)+
 N'     This is the   last line.   '
execute @RetVal=NiceString @strTest output,3
select '3 rest default',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select
 len(@strTest) as Length,
 patindex(N'%'+nchar(13)+N'%',@strTest) as N'First LD'

select @strTest=
 N'     This   is   a   three   line   string.   '+nchar(13)+
 N'     This is the   second line.   '+nchar(13)+
 N'     This is the   last line.   '
execute @RetVal=NiceString @strTest output,2
select '2 rest default',@RetVal as N'Ret Val',N'>'+@strTest+N'<'
select
 len(@strTest) as Length,
 patindex(N'%'+nchar(13)+N'%',@strTest) as N'First LD'
***** End of Test Code *****

Sincerely,

Gene Wirchenko

[toc] | [next] | [standalone]

#128

Gene Wirchenko (genew@ocis.net) writes:
>      I have finished writing my first involved stored procedure.  It
> makes strings nice.  It works, but it might be better.
> 
>      Have I, for example, missed out on functions that could have made
> it easier?

Kind of. While you can write this stuff in T-SQL, this is exactly why
Microsoft added the capability to put code written in .Net in SQL Server.

>      I also do not know how SQL Server handles multi-line values.  I
> am assuming there is a CR (or UniCode equivalent) to delimit lines. In
> case there could be LFs, I eliminate them in the line flagged
> "--*****TRY".
 
SQL Server does not really care much about lines at all. If you put CR-LF at 
the end of the string, there's a CR-LF at the end of the string. If string 
only has LF, that's it. Or only CR. It all depends on where the data comes 
from. If you load a text file from Unix, there will be only LF. If you 
run examples from SSMS there will probably be CR-LF, since SSMS in a Windows
application.

I do not have the time to look at the code as such for now.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#131

On Wed, 6 Apr 2011 07:43:02 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      I have finished writing my first involved stored procedure.  It
>> makes strings nice.  It works, but it might be better.
>> 
>>      Have I, for example, missed out on functions that could have made
>> it easier?
>
>Kind of. While you can write this stuff in T-SQL, this is exactly why
>Microsoft added the capability to put code written in .Net in SQL Server.

     I have not gotten into .Net.  One at a time.

     What I am going to put on top of this is an insert/update trigger
to clean up strings.  Can I call .Net code from within a trigger?

>>      I also do not know how SQL Server handles multi-line values.  I
>> am assuming there is a CR (or UniCode equivalent) to delimit lines. In
>> case there could be LFs, I eliminate them in the line flagged
>> "--*****TRY".
> 
>SQL Server does not really care much about lines at all. If you put CR-LF at 
>the end of the string, there's a CR-LF at the end of the string. If string 
>only has LF, that's it. Or only CR. It all depends on where the data comes 
>from. If you load a text file from Unix, there will be only LF. If you 
>run examples from SSMS there will probably be CR-LF, since SSMS in a Windows
>application.

     Well, that could make things interesting in a cross-platform
situation.

     SSMS does not display the multi-line values as such.  There is
space between the lines as in:
          LINE ONE line two

>I do not have the time to look at the code as such for now.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#133

Gene Wirchenko (genew@ocis.net) writes:
>      What I am going to put on top of this is an insert/update trigger
> to clean up strings.  Can I call .Net code from within a trigger?
 
Yes. I think you would put this in a function, and calling CLR UDF is just 
the same as calling a T-SQL UDF. 

You can even write triggers directly in the CLR. I will have that I have
yet to see a use case for that.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#132

On 05-04-2011 23:26, Gene Wirchenko wrote:
> Dear SQLers:
>
>       I have finished writing my first involved stored procedure.  It
> makes strings nice.  It works, but it might be better.
>
One thing I recommend is to always use BEGIN/END after an IF/WHILE, even 
if there's only one statement to execute, see below.


>        -- Add back any leading or trailing spaces being kept.
>        if @fTrimLead=0
>           select @strLine=space(@cLeading)+@strLine
>        if @fTrimTrail=0
>           select @strLine=@strLine+space(@cTrailing)
>

[toc] | [prev] | [next] | [standalone]

#134

On Wed, 06 Apr 2011 21:15:48 +0200, Henk van den Berg
<hvandenberg@xs4all.nl> wrote:

>On 05-04-2011 23:26, Gene Wirchenko wrote:
>> Dear SQLers:
>>
>>       I have finished writing my first involved stored procedure.  It
>> makes strings nice.  It works, but it might be better.
>>
>One thing I recommend is to always use BEGIN/END after an IF/WHILE, even 
>if there's only one statement to execute, see below.

     I do not like the clutter.  To guard against the risk you imply
(and it is a valid concern), instead, I am VERY fussy with indentation
as in, in C:
          for (i=1; i<=10000; i++)
             ;     /* empty body */

>>        -- Add back any leading or trailing spaces being kept.
>>        if @fTrimLead=0
>>           select @strLine=space(@cLeading)+@strLine
>>        if @fTrimTrail=0
>>           select @strLine=@strLine+space(@cTrailing)

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#135

Gene Wirchenko (genew@ocis.net) writes:
>      I do not like the clutter.  To guard against the risk you imply
> (and it is a valid concern), instead, I am VERY fussy with indentation
> as in, in C:


My personal standard is that I don't have BEGIN END, if it is a single
line, but if there are more than one line, I have BEGIN END, even if
it's a single statement.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#165

I like the idea of white space replacement without loops presented
here:

http://www.itjungle.com/fhg/fhg101106-story02.html

replace(replace(replace(name,' ','<>'),'><',''),'<>',' ')

1) replace each white space with <>
2) when a white space follows an other, it looks like this: >< -
remove them
3) finally there is just <> left, replace it with a normal white space

brgds

Philipp Post

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web