Changing Database within a stored procedure

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"Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0404122312.e0cf00f@.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|||"Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0404122312.e0cf00f@.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|||Hi Simon,

Thanks for the help. This stored procedure is installed by customers,
so I have no idea what databases they have or what databases they will
want to create this view on. I will give your solution a try.

Thanks,
Bruce|||Hi Simon,

Thanks for the help. This stored procedure is installed by customers,
so I have no idea what databases they have or what databases they will
want to create this view on. I will give your solution a try.

Thanks,
Bruce