Use database dynamically

Question

This execution it is giving me an error!

Any hints of what am I missing?

declare @dbname varchar(500)
set @dbname='master'
Exec ('

Use ' + @dbname + '

go

create PROCEDURE [dbo].[krijo_database] @dbname nvarchar(2000), @Direktoria varchar(4000)
AS
BEGIN
    declare @stringu nvarchar(100)
    set @stringu = 
    ''CREATE DATABASE '' + @dbname 
    exec (@stringu)
End
')

Answear

declare @dbname varchar(500) set @dbname='kontabel'

Exec( 'Use ' + @dbname +'

Exec ('' create PROCEDURE [dbo].[krijo_database] @dbname nvarchar(2000), @Direktoria varchar(4000) AS BEGIN declare @stringu nvarchar(100) set @stringu = ''''create DATABASE '''' + @dbname exec (@stringu) End '')

')

Actually i tried like this and it worked but i i had to change quotes. The Real procedure that i would like to use it is a very big one, more dhan 50000 lines and i can't go on an changing the quotes to everything. Is there any other idea?

Answer 1

My mistake, I didn't notice something.

maybe it's the Exec inside the Exec that's causing the error?

or because your'e assigning a nvarchar(2000) to a nvarchar(100)

"Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'go'. Msg 111, Level 15, State 1, Line 11 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. " If i remove the "GO" it gives me just the second one.

try this without any use or go: create PROCEDURE '+@dbname+'.[dbo].[krijo_database] @dbname nvarchar(2000)

Answer 2

Sometime ago I had code which was updating database structure based on scripts. I end up with split file by 'go' and execute separately each fragment. Can you try this?

So, first exec use statement, and than exec createprocedure.

Be sure to verify that it is created in proper database

Answer 3

You can't use GO like that

1. It isn't a SQL command
2. It tells SSMS to split the batch

If you remove it, then you'll get "first in batch" error which is expected

In this case, why not just do this...

Use master
GO
create PROCEDURE [dbo].[krijo_database] @dbname nvarchar(2000), @Direktoria varchar(4000)
AS
BEGIN
    declare @stringu nvarchar(100)
    set @stringu = 'CREATE DATABASE ' + @dbname 
    exec (@stringu)
End
GO

Why do need dynamic SQL to create a stored procedure?

Answer 4

USE master
GO

CREATE PROCEDURE dbo.create_database @name nvarchar(100)
AS
    DECLARE @sql nvarchar(100)
    SET @sql = 'CREATE DATABASE ' + QUOTENAME(@name)
    EXEC (@sql) 
GO

Answer 5

The Real procedure that i would like to use it is a very big one, more dhan 50000 lines and i can't go on an changing the quotes to everything

Microsoft SQL Server has a maximum length of varchar of 8000 characters.

http://www.databasejournal.com/features/mssql/article.php/3788256/Data-Types-in-SQL-Server-2008.htm

Answer 6

What you're after can't be done I don't think.

See this article for reference

Answer 7

Two issues:

1. Using "GO" is incorrect... there is no SQL keyword called "GO"... that's just a hack that SQL Server Management Studio is performing for you.

2. You need to the CREATE PROCEDURE command in it's own context... simple.

Here's the slight modification to your script:

declare @dbname varchar(500)
set @dbname='master'
Exec ('

Use ' + @dbname + '

EXECUTE(''create PROCEDURE [dbo].[krijo_database] @dbname nvarchar(2000), @Direktoria varchar(4000)
AS
BEGIN
    declare @stringu nvarchar(100)
    set @stringu = 
    ''''CREATE DATABASE '''' + @dbname 
    exec (@stringu)
End'')
')

So the answer is to put another "EXECUTE" command inside the first EXECUTE command. I do this all the time, a lot of times in an "sp_msforeachdb". You can nest those bad boys as long as you want.

Answer 8

you should create stored procedure with that portion with variable.

Answer 9

I used SQl DMO! Great feature both for 32 and 64 bit,compatible with both SQL express and server!