[dba-SQLServer] Dynamic SQL

Thanks for that Arthur. 


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: Saturday, March 29, 2008 9:10 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Dynamic SQL

JC,

I think Michael is quite right in his advice. I would add only one thing
(welcome to the world of SQL Server)... Back up master database every time
you touch it. There's nothing inherently wrong with touching it, but you
must be aware that there's a reason it's called master. So every time you
add to it or change something in it, do a backup.

There's another item I may have mentioned previously, but repetition is good
for the soul. If you have sprocs or UDFs or even tables that you want in
every db, add these objects to model. Model is so named because it is the
model for every new db you create, so anything in it is "inherited" by the
new db. I have played with this quite a bit, and found it to be one of the
under-used capabilities of SQL Server. A while back I posted some functions
that strip the time part from a DateTime column and the converse. I plonked
these (and several other such reusables) into model and presto, every new db
I create has them instantly.

A.

On 3/28/08, Michael Maddison <michael at ddisolutions.com.au> wrote:
>
> Hi John,
>
> Dynamic SQL seems like the obvious solution in your circumstance.
> If you are executing the strings from code then another option is to 
> build some generic global sprocs on your server.
>
> I would do something like...
>
> USE MASTER
> GO
> CREATE PROCEDURE dbo.DeleteFrom (
>         @DB nvarchar(100),
>         @table nvarchar(100)
> )
> AS
> DECLARE @SQL [nvarchar](4000)
>
> IF @DB <> N'' AND @table <> N''
>         BEGIN
>         SET @SQL = 'DELETE FROM ' + @DB + '.' + LTRIM(RTRIM(@table))
>         PRINT @SQL
>         EXEC sp_executesql @SQL
>         PRINT @@ROWCOUNT
>         END
>
>
> And call it like
> EXEC  [master].[dbo].[DeleteFrom] 'databasename', 'dbo.tablename'
>
> HTH
>
> Michael M
>
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com