CODEWITHSUNDEEP
sqlsql-server
Egor
Egor

Reputation: 1409

sp_executesql - incorrect syntax near parameter

I'm using a query with parameters to create a database user and SQL Server login. I have next procedure:

PROCEDURE [dbo].[regNewOfficial]
@name nvarchar(30),
@password nvarchar(30)
AS
DECLARE @sqlRequest NVARCHAR(1000);
SET @sqlRequest = 'CREATE LOGIN @nameP' +  
                    ' WITH PASSWORD = @passwordP' + 
                    ' , DEFAULT_DATABASE=[TreeBase]' +  
                    ' CREATE USER @nameP' + 
                    ' FOR LOGIN @nameP' +
                    ' ALTER ROLE official_role ADD MEMBER @nameP ;';
EXECUTE sp_executesql  @sqlRequest, N'@nameP nvarchar(30), @passwordP nvarchar(30)', @nameP = @name, @passwordP = @password;

And I get this error:

Incorrect syntax near "@nameP"

SOLUTION: Need to use QUOTENAME() when concatenating a string

Result of using parameters without QUOTENAME():

CREATE LOGIN @nameP WITH PASSWORD = @passwordP , DEFAULT_DATABASE=[TreeBase] CREATE USER @nameP FOR LOGIN @nameP ALTER ROLE official_role ADD MEMBER @nameP ;

With QUOTENAME():

CREATE LOGIN [aaaaa] WITH PASSWORD = 'bbbbb' , DEFAULT_DATABASE=[TreeBase]; CREATE USER [aaaaa] FOR LOGIN [aaaaa]; ALTER ROLE official_role ADD MEMBER [aaaaa];

Upvotes: 3

Views: 1328

Answers (1)

Thom A
Thom A

Reputation: 95554

CREATE LOGIN requires literal strings to work; you can't pass it variables. Therefore you need to do your Dynamic SQL in a different way.

ALTER PROCEDURE [dbo].[regNewOfficial]
    @name nvarchar(30),
    @password nvarchar(30)
AS

    DECLARE @sqlRequest NVARCHAR(1000);
    SET @sqlRequest = N'CREATE LOGIN ' + QUOTENAME(@name) +
                      N' WITH PASSWORD = ' + QUOTENAME(@password,'''') +
                      N' , DEFAULT_DATABASE=[TreeBase]' + N';' + NCHAR(10) +  
                      N' CREATE USER ' + QUOTENAME(@name) +
                      N' FOR LOGIN ' + QUOTENAME(@name) + N';' + NCHAR(10) + 
                      N' ALTER ROLE official_role ADD MEMBER ' + QUOTENAME(@name) + N';';
    PRINT @sqlRequest; --This is your friend for debugging
    EXECUTE sp_executesql @sqlRequest;
GO

Upvotes: 4

Related Questions