[dba-SQLServer] SQL Nightmares

I've written quite a few UDFs, JC. I'll let you in on a little secret:
templates. While you're in Management Studio, open the Template Viewer (from
the View menu, or press Ctrl+Alt+T). You'll get a long list of the templates
available. The one you want is the Scalar UDF (Create Scalar function from
new menu) template. Double-click it and presto, you'll have the skeleton of
a template.

You'll see a strange-looking bunch of markers in the template( <Author,,
Name> etc.). Don't bother trying to delete and replace these. Instead, from
the Query menu, select Specify Values for Template Parameters. A dialog will
pop up enabling you to fill in your name, creation date, name and type of
the parameters, and return type of the function. Fill these in and then
click OK and you're back in the templated function. It should be
straightforward from here on in. The important parts are the RETURNS and
SELECT lines. Put your processing instructions on the SELECT line, then
assign the results to @Result or whatever you changed its name to.

Typically, I add one line beneath the function body that tests it, so that I
can immediately see if it worked. As soon as it does, I comment that last
line out.

One last thing, not of immediate relevance but for general purposes. Another
item on the Functions node is for creating multi-statement table-valued
functions. This template creates a table and shows how to build a Common
Table Expression (CTE) then use it in a SELECT. You may not need this one
right now, but you will soon enough.

Recalling the old line of Picasso's. He showed Gertrude Stein his portrait
of her, and she said "It doesn't look like me." Picasso replied, "It will."

Arthur

On 3/20/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> I really need a method of updating a field using a udf or something.  That
> way I could tell it to run and just feed it field names to run it on.
> Unfortunately UDFs are something I have never done.
>
>
>
> 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: Wednesday, March 19, 2008 11:08 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] SQL Nightmares
>
>
> Hi John,
>
> For the house number...
>   select cast('000120' as int)
>
>
> for the streetname...
>   declare @col VARCHAR(25)
>   set @col = '00000000012st'
>   select right(@col,len(@col)-patindex ('%[^0]%', at col)+1)
>
> -Paul
>
>
>
>
>
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, March 19, 2008 5:29 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] SQL Nightmares
>
> I have another (different) database from hell.  This one has data fields
> like
>
> HOUSENUMBER             StreetName              StreetUnit
> 00000006                000012th                Place
>
> As you can see, if I do a simple append, then I get something like
>
> 00000006 000012th Place.
>
> I can tell you that won't fly.  So I need to strip off leading zeros,
> pretty
> much in ALL my fields, but at the very least in a fixed set of
> fields.  How
> do I do this in SQL, bearing in mind that this table has 90 million
> records
> and taking a week for one field is out of the question.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> __________ NOD32 2960 (20080319) 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
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>