[dba-SQLServer] SQL Nightmares

My last line would typically look like this, after adding the comment:

-- SELECT col1, MyUDF( col2, col3) FROM someplace

Now, given that you've run it once, and thus created the function, you can't
comment out the line above and run the code again because you'll get an
error that the function already exists. You could change the CREATE line to
ALTER and re-run the code, or alternatively save the query to a text file.
On my machine and probably yours, the default directory is Projects. You can
create a new directory under that called GlobalFuncs or something, and save
the file there. Then whenever you need to include this function in a new
database, switch databases, then File | Open this text file and re-run the
query.

True, you can sidestep this by placing your re-usable UDFs in the master db,
but this is discouraged for reasons that ought to be obvious. Far better,
IMO, to save all these functions (and globally useful sprocs too) as text
files, then re-run them in each db that needs them. For one thing, I'm not a
big fan of touching the master under any circumstances. For another, I'm not
a big fan of code that gets loaded when it may never be used. The old
signal-to-noise ratio thing.

Finally, there definitely are funcs and sprocs that you would want to
include in every db you create. A cool way to do this is to add them to the
model db, which is used as the template for each new db you create. Thus
adding anything to model db means that you "inherit" it in all new dbs.

hth,
Arthur

On 3/20/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>