Create database in stored procedure

Question

How can I create a database via a stored procedure?

CREATE PROCEDURE CreateDataBase 
    @strDBPath nvarchar(MAX),
    @strFileName nvarchar(50),
    @strLOGPath nvarchar(MAX),
    @strLOGFileName nvarchar(50)
AS
BEGIN
    CREATE DATABASE [APPLICATION]
    CONTAINMENT = NONE
    ON  PRIMARY 
    ( NAME = strFileName, FILENAME = strDBPath, SIZE = 5120KB , FILEGROWTH = 1024KB )
    LOG ON 
    ( NAME = strFileName, FILENAME = strLOGPath , SIZE = 2048KB , FILEGROWTH = 10%)
END
GO

In SQL Server Management Studio everything seems alright. But in my Visual Studio database project the compiler complains about strDBPath and strLOGPath.

Error:

Incorrect Syntax near strDBPath

score 0 · Answer 1 · answered Apr 02 '16 at 10:58

0

Because you try to use them as variables NOT USING THE PREFIX @ in your statement. I am pretty sure, if you would READ the error message (quite telling you do not include it in your question here, you know) it would hint you in this direction.

answered Apr 02 '16 at 10:58

TomTom

61,059
10
88
148

Well its saying incorrect syntax near strDBPath... – Patrick Apr 02 '16 at 11:02
I need the @ Prefix when i define a variable, I CANT use the @ prefix, if i use the variable. – Patrick Apr 02 '16 at 11:11
@GejekoPatrick - Petty distinction: "@" is not a prefix, like dereferencing a pointer in some languages, it is the initial character of a variable or parameter identifier as documented [here](https://msdn.microsoft.com/en-us/library/ms175874.aspx), (Except when it is the first of two "@" characters used in some T-SQL function names.) It doesn't come and go depending on where the variable or parameter is being used. – HABO Apr 02 '16 at 14:11

Create database in stored procedure

1 Answers1