CODEWITHSUNDEEP
sqlsql-server
mu88
mu88

Reputation: 5374

How to parameterize SQL Server script

Is it possible to convert MyUser, MyPassword and MyDatabase within the following SQL Server script into parameters?

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'MyUser')
BEGIN
    CREATE LOGIN MyUser WITH PASSWORD = 'MyPassword'
END

CREATE DATABASE MyDatabase;
GO

USE MyDatabase;

CREATE USER MyUser FOR LOGIN MyUser;  
GO   

EXEC sp_addrolemember 'db_owner', 'MyUser'

Upvotes: 0

Views: 1313

Answers (1)

Thom A
Thom A

Reputation: 95554

As you may have found out, you have to use literals for tasks like this. As a result you have to use dynamic SQL and safely inject the values For example, for the login (assuming the parameter values have been set in an SP):

DECLARE @SQL nvarchar(MAX);
IF NOT EXISTS (SELECT 1
               FROM sys.syslogins
               WHERE name = @MyLogin)
    SET @SQL = N'CREATE LOGIN ' + QUOTENAME(@MyLogin) + N' WITH PASSWORD = N' + QUOTENAME(@MyPassword,'''') + N';';

EXEC sp_executesql @SQL;

If the login already exists, then @SQL will have a value of NULL and won't be created.

As a result, your final SP will look something like this:

CREATE PROC dbo.SetupLogin @MyLogin sysname, @MyDatabase sysname, @MyPassword nvarchar(128) AS
BEGIN

    DECLARE @SQL nvarchar(MAX);
    IF NOT EXISTS (SELECT 1
                   FROM sys.syslogins
                   WHERE name = @MyLogin)
        SET @SQL = N'CREATE LOGIN ' + QUOTENAME(@MyLogin) + N' WITH PASSWORD = N' + QUOTENAME(@MyPassword,'''') + N';';


    EXEC sp_executesql @SQL;

    SET @SQL = N'CREATE DATABASE ' + QUOTENAME (@MyDatabase) + N';'

    EXEC sp_executesql @SQL;

    SET @SQL = N'USE ' + QUOTENAME (@MyDatabase) + N';' + NCHAR(13) + NCHAR(10) +
               N'CREATE USER ' + QUOTENAME(@MyLogin) + N' FOR LOGIN ' + QUOTENAME(@MyLogin) + N';' + NCHAR(13) + NCHAR(10) +
               N'ALTER ROLE db_owner ADD MEMBER ' + QUOTENAME(@MyLogin) + N';'; --sp_addrolemember is deprecated, stop using it.
    EXEC sp_executesql @SQL;

END;

Upvotes: 2

Related Questions