Can't pass a variable to "Create Database" statement in SQL 2000

[ToddR]

Trying to create a sp in SQL 2000

CREATE PROCEDURE sp_createProjectDB
@dbName varchar(15),
@dbDAT varchar(15),
@dbDATFILENAME varchar(100)
AS
exec ("create database " @dbName)

Have tried it a number of different ways but can not get it to work. Works in 6.5. Any ideas?

 

[Andel]

Try this...

CREATE PROCEDURE sp_createProjectDB
@dbName varchar(15),
@dbDAT varchar(15),
@dbDATFILENAME varchar(100)
AS
exec ('create database ' + @dbName)

also, you need to supply the 2nd and third parameter if you don't put a default, like:

sp_createProjectDB 'newdb', 'e:\newdb.mdf', 'e:\newdb.ldf'

hope this helps...

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[Andel]

sorry for my previous message, here's what you should try:

CREATE PROCEDURE sp_createProjectDB
@dbName varchar(15),
@dbDAT varchar(15),
@dbDATFILENAME varchar(100)
AS
exec ('create database ' + @dbName + ' on (NAME = ' + @dbDAT + ', FILENAME = ''' + @dbDATFILENAME + ''')')

then execute stored procedure as follows:

sp_createProjectDB 'TEST', 'TEST_DATA', 'E:\TEST_DATA.MDF'



Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[ToddR]

Yes, thank you. Apparently 6.5 was less concerned about " vs. ' (or perhaps that's just how our version was configured).

Thanks again