[dba-SQLServer] Diving off the deep end

We have ignition.  I want a function for a couple of reasons, first because
I want to be able to do this in row after row, in table after table.  I want
to be able to build update queries that can just call the function etc.

I am there, at least for this one function.

Thanks for your help.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul
Nielsen
Sent: Thursday, March 20, 2008 9:55 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Diving off the deep end

Keep it simple. you don't need to create a function for updating from a this
parsing, just put the expression in your select/update statement. 


Update table1
  Set col1 = right(t2.col2,len(t2.col2)-patindex('%[^0]%', t2.col2)+1)
    from table1 t1
      join table2 t2
        on t1.pk = t2.pk


- Paul
www.sqlserverbible.com 







-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, March 20, 2008 7:27 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Diving off the deep end

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
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Thursday, March 20, 2008 9:14 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Diving off the deep end

First problem is the double quotes, which SQL interprets as a reference to a
column. Second problem is that the statement must begin with the word
SELECT. After that you're good to go:

SELECT Right( 'John W. Colby', 3 )
==> 'lby'

Third issue: don't compile the query. Just run it.

hth,
A.

On 3/20/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> I THINK THAT WE NEED TO BACK UP A FEW STEPS.
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


__________ NOD32 2963 (20080320) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com