"Bruce" <sa*****@pacbell.net> wrote in message
news:59*************************@posting.google.co m...
I need to create a stored procedure in the master database that can
access info to dynamically create a view in another database. It
doesn't seem like it should be very hard, but I can't get it to work.
Here's an example of what I want to do.
CREATE PROCEDURE create_view @dbname sysname
AS
BEGIN
DECLARE @query varchar(1000)
SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'
EXEC(@query)
END
In this case, I get an error with the word "go". Without it, I get a
"CREATE VIEW must be the first statement in a batch" error. I tried a
semicolon in place of "GO" but that didn't help either.
Thanks
It would probably be easier to do this from a client-side script - it's easy
to pass the database name to osql.exe, for example. In addition, you may
want to rethink your approach slightly, as it would be better to implement a
controlled deployment process for your code, so you can take a view script
from your source control system and create it in any database you want. See
this link also:
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB
But if you really need to do it in TSQL, then this is one way:
CREATE PROCEDURE create_view @dbname sysname
AS
BEGIN
DECLARE @cmd varchar(1000)
set @cmd = 'osql -E -d ' + dbname + ' -Q "CREATE VIEW..."'
exec master..xp_cmdshell @cmd, NO_OUTPUT
END
Simon
