Whats Wrong with this procedure? 1

[Skittle]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[usp_AddMISUser]
@LoginName VarChar(20),
@Password   VarChar(20)
AS
DECLARE @err INT
CREATE LOGIN @LoginName WITH PASSWORD = @Password,  CHECK_POLICY = OFF
EXEC sp_grantdbaccess @loginName = @LoginName, @name_in_db = @LoginName
EXEC sp_addrolemember 'MyRole', @LoginName 
Print 'Complete'

The ALTER is rejected with :-

Msg 102, Level 15, State 1, Procedure usp_AddMISUser, Line 8
Incorrect syntax near '@LoginName'.
Msg 319, Level 15, State 1, Procedure usp_AddMISUser, Line 8
Incorrect syntax near the keyword 'with'.

The procedure does not like the VarChar variable for @LoginName. How can I set the parameter login name on a CREATE LOGIN command?

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[yelworcm]

Presumably, you need to use Dynamic SQL for the login creation:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[usp_AddMISUser]
@LoginName VarChar(20),
@Password   VarChar(20)
AS
DECLARE @err INT
DECLARE @sql varchar(1000)
set @sql = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = ''' + @Password = ''',  CHECK_POLICY = OFF'
EXEC (@sql)
EXEC sp_grantdbaccess @loginName = @LoginName, @name_in_db = @LoginName
EXEC sp_addrolemember 'MyRole', @LoginName
Print 'Complete'

 

[Skittle]

Thanks dude.

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.