[dba-SQLServer] Diving off the deep end

John,

Try this:

CREATE FUNCTION StripLeadingZeros
(
@StrToStrip varchar(200)
)
RETURNS varchar(200)
AS
BEGIN
         -- Declare the return variable here
         DECLARE @strTemp varchar(200)

         -- Add the T-SQL statements to compute the return value here
      set @strTemp = right(@StrToStrip,len(@StrToStrip)-patindex
('%[^0]%', at StrToStrip)+1)
         -- Return the result of the function
         RETURN @strTemp

END

Robert

At 08:26 AM 3/20/2008, you wrote:
>Date: Thu, 20 Mar 2008 09:26:32 -0400
>From: "jwcolby" <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] Diving off the deep end
>To: "'Discussion concerning MS SQL Server'"
>         <dba-sqlserver at databaseadvisors.com>
>Message-ID: <001001c88a8e$03e97600$0201a8c0 at M90>
>Content-Type: text/plain;       charset="us-ascii"
>
>Right, figured that out.  By "compile" I meant "parse".
>
>Now...
>
>CREATE FUNCTION StripLeadingZeros
>(
>@StrToStrip varchar(200)
>)
>RETURNS varchar(200)
>AS
>BEGIN
>         -- Declare the return variable here
>         DECLARE @strTemp varchar(200)
>
>         -- Add the T-SQL statements to compute the return value here
>      select right(@StrToStrip,len(@StrToStrip)-patindex
>('%[^0]%', at StrToStrip)+1)
>         -- Return the result of the function
>         RETURN @strTemp
>
>END
>
>Parses correctly but returns an error:
>
>Msg 444, Level 16, State 3, Procedure StripLeadingZeros, Line 17
>Select statements included within a function cannot return data to a client.
>
>So it seems that the statement:
>
>      select right(@StrToStrip,len(@StrToStrip)-patindex
>('%[^0]%', at StrToStrip)+1)
>
>Needs to "return" the data to a variable.  However if I modify that to:
>
>         @strTemp = select right(@StrToStrip,len(@StrToStrip)-patindex
>('%[^0]%', at StrToStrip)+1)
>
>Then it no longer parses, returning:
>
>Msg 102, Level 15, State 1, Procedure StripLeadingZeros, Line 18
>Incorrect syntax near '@strTemp'.
>Msg 137, Level 15, State 2, Procedure StripLeadingZeros, Line 20
>Must declare the scalar variable "@strTemp".
>
>Modifying it to:
>
>         @strTemp = right(@StrToStrip,len(@StrToStrip)-patindex
>('%[^0]%', at StrToStrip)+1)
>
>Returns a parse error:
>
>Msg 102, Level 15, State 1, Procedure StripLeadingZeros, Line 18
>Incorrect syntax near '@strTemp'.
>Msg 137, Level 15, State 2, Procedure StripLeadingZeros, Line 20
>Must declare the scalar variable "@strTemp".
>
>I need to not only get the data out of the string, but place it in a
>variable and return that variable which is what I am trying to do here.
>
>John W. Colby