Creating a database in MSSQL using SQL procedures

Question

Hi i am trying to create a database using below SQL procedure

Create_SQL_Proc_Statement = """
CREATE PROCEDURE db_creation_script
           @DBName nvarchar(50),
           @data_path nvarchar(50) ='',
           @data_file_size nvarchar(50) = '5000KB',
           @data_file_maxsize nvarchar(50) = 'UNLIMITED',
           @data_file_filegrowth nvarchar(50) = '1024KB',
           @log_path nvarchar(50) ='',
           @log_file_size nvarchar(50) = '5000KB',
           @log_file_maxsize nvarchar(50) = '1048576KB',
           @log_file_filegrowth nvarchar(50) = '1024KB'
AS

begin

           declare @l_variable_data as nvarchar(500);
           declare @l_variable_log as nvarchar(500);
           declare @complete_command as nvarchar(1000);
           declare @print_message as nvarchar(1000);
           declare @error as int;

           --Fetch data path

           if (@data_path ='' OR @data_path is null )
                          SELECT @data_path=cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar);
           if (@data_path ='' OR @data_path is null )

           begin
                          set @error=@@ERROR
                          if (@error<>0)
                          begin
                                         set @print_message=N'Error '+ RTRIM(cast(@error as nvarchar(10))) + ' - Message: '+ ERROR_MESSAGE();
                                         print(@print_message);
                          end
                          else
                                         print('No error is generated');
           end

           --Fetch log path

           if (@log_path ='' OR @log_path is null )
                          SELECT @log_path=cast(SERVERPROPERTY('InstanceDefaultLogPath') as nvarchar);
           if (@log_path ='' OR @log_path is null )
           begin
                          set @error=@@ERROR
                          if (@error<>0)
                          begin
                                         set @print_message=N'Error '+ RTRIM(cast(@error as nvarchar(10))) + ' - Message: '+ ERROR_MESSAGE();
                                         print(@print_message);
                          end
                          else
                                         print('No error is generated');
           end


            set @l_variable_data = concat('NAME =', '''',@DBName, '_DATA','''', ', FILENAME = ', '''', @data_path,'\',@DBName, '.mdf', '''', ', SIZE = ', @data_file_size, ', MAXSIZE = ', @data_file_maxsize,', FILEGROWTH = ',@data_file_filegrowth)
            set @l_variable_log  = concat('NAME =', '''',@DBName, '_LOG', '''', ', FILENAME = ', '''', @log_path, '\',@DBName, '.ldf', '''', ', SIZE = ', @log_file_size, ', MAXSIZE = ',  @log_file_maxsize,', FILEGROWTH = ',@log_file_filegrowth)

           set @complete_command = concat(' CREATE DATABASE ', @DBName , ' CONTAINMENT = NONE ON PRIMARY (', @l_variable_data, ')' , ' LOG ON (' , @l_variable_log , ')');


           exec(@complete_command);

end
"""

and, below is how i am dealing with the proc:

cursor.execute(Create_SQL_Proc_Statement)
cursor.execute('Exec db_creation_script  ?, ?, ?, ?, ?, ?, ?, ?, ?',(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))

i also tried the below:

cursor.execute('{ call db_creation_script  (?, ?, ?, ?, ?, ?, ?, ?, ?)}',(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))
cursor.execute('exec db_creation_script  {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}'.format(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))

but no luck, i always get following exception:

 cursor.execute('exec db_creation_script  {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}'.format(DB_Name,Data_Path,Data_File_Size,Data_File_Maxsize,Data_File_Filegrowth,Log_Path,Log_File_Size,Log_File_Maxsize,Log_File_Filegrowth))
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")

Every time i run the above code, i get unsuccessful message, Could you please help me understand where exactly the issue is in my script? would be great if any one can let me know if there is any other way around to do this.

Note: i am using pyodbc python module to achieve the same

Answer 1

you need to make sure the string is valid to SQLServer

here i have made the nescessary changes, and the stored procedure is created on my instance, whether it works is another issue :-)

i have intentionally left out the 3 lines just above exec(complete command), because of the many quotes to be entered :-)

This script works in SSMS

       DECLARE @Create_SQL_Proc_Statement NVARCHAR(MAX);
       SELECT @Create_SQL_Proc_Statement = N'
       CREATE PROCEDURE db_creation_script
       @DBName nvarchar(50),
       @data_path nvarchar(50) ='''',
       @data_file_size nvarchar(50) = ''5000KB'',
       @data_file_maxsize nvarchar(50) = ''UNLIMITED'',
       @data_file_filegrowth nvarchar(50) = ''1024KB'',
       @log_path nvarchar(50) ='''',
       @log_file_size nvarchar(50) = ''5000KB'',
       @log_file_maxsize nvarchar(50) = ''1048576KB'',
       @log_file_filegrowth nvarchar(50) = ''1024KB''
       AS

       begin

       declare @l_variable_data as nvarchar(500);
       declare @l_variable_log as nvarchar(500);
       declare @complete_command as nvarchar(1000);
       declare @print_message as nvarchar(1000);
       declare @error as int;

       --Fetch data path

       if (@data_path ='''' OR @data_path is null )
                      SELECT @data_path=cast(SERVERPROPERTY(''InstanceDefaultDataPath'') as nvarchar);
       if (@data_path ='''' OR @data_path is null )

       begin
                      set @error=@@ERROR
                      if (@error<>0)
                      begin
                                     set @print_message=N''Error ''+ RTRIM(cast(@error as nvarchar(10))) + '' - Message: ''+ ERROR_MESSAGE();
                                     print(@print_message);
                      end
                      else
                                     print(''No error is generated'');
       end

       --Fetch log path

       if (@log_path ='''' OR @log_path is null )
                      SELECT @log_path=cast(SERVERPROPERTY(''InstanceDefaultLogPath'') as nvarchar);
       if (@log_path ='''' OR @log_path is null )
       begin
                      set @error=@@ERROR
                      if (@error<>0)
                      begin
                                     set @print_message=N''Error ''+ RTRIM(cast(@error as nvarchar(10))) + '' - Message: ''+ ERROR_MESSAGE();
                                     print(@print_message);
                      end
                      else
                                     print(''No error is generated'');
       end



       exec(@complete_command);

       end
       '           

       EXECUTE sp_executesql @Create_SQL_Proc_Statement