Use database dynamically

Question

This execution gives me the following error:

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 delete "GO", he gives me only the second.

Any hints that I'm 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 ') 

Answer

 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 it this way, and it worked, but I had to change the quotes. The actual procedure that I would like to use is more than 50,000 lines, and I can not go and manually change the quotes to everything.

Is there a better way?

Answer 1

Two questions:

• Using "GO" is wrong ... there is no SQL keyword called "GO" ... that just hacked SQL Server Management Studio for you.

• You need to execute the CREATE PROCEDURE command in your own context ... simple.

Here is a small 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, many times in "sp_msforeachdb". You can invest these bad boys as much as you want.

Answer 2

Once upon a time, I had code that updated a script-based database structure. I end up with a broken go file and execute each snippet separately. Can you try this?

So, the first instruction is using exec, not exec createprocedure.

Make sure it is created in the appropriate database.

Answer 3

My mistake, I did not notice anything.

maybe it's exec inside exec causing an error?

or because you assigned nvarchar (2000) to nvarchar (100)

"Msg 102, level 15, state 1, line 5 Invalid syntax near 'go'. Msg 111, level 15, state 1, line 11 'CREATE / ALTER PROCEDURE' must be the first statement in the query packet." If I delete "GO", he will give me only the second.

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

Answer 4

You cannot use GO as

• This is not a SQL command.
• It tells SSMS about packet splitting

If you delete it, you will get a “batch first” error, which is expected

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

 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 we need dynamic SQL to create a stored procedure?

Answer 5

 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 6

The real procedure that I would like to use is a very large one, there are more than dhan 50,000 lines, and I can not continue to change quotes for everything

Microsoft SQL Server has a maximum varchar of 8000 characters.

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

Answer 7

What you need cannot be done. I do not think.

See this article for reference.

Answer 8

You must create a stored procedure with this part with a variable.

Answer 9

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