[dba-SQLServer] SQL Nightmares

Wow, that is cool.  Now for the questions:

1) Where do I store the functions?  It seems like many things are useful
across databases.
2) You mentioned placing a line below the function to test.  I assume that
you just run the function in the same manner that you would a query?
Everything in the window gets executed and the one line below the function
calls the function and thus returns a value?

More questions to follow.  ;-)

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 8:13 AM
To: Discussion concerning MS SQL Server
Subject: Re: [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, 
Name> 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