468,107 Members | 1,333 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

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
Jul 20 '05 #1
4 12879

"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
Jul 20 '05 #2

"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
Jul 20 '05 #3
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
Jul 20 '05 #4
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
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Peter Duniho | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.