what am I doing wrong - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

what am I doing wrong

Author

Topic

syren
Starting Member

1 Post

Posted - 2006-10-16 : 14:47:44

hi all,
i'm trying to write a simple stored procedure with 2 parameters (username & password) to crate a Login for the specified user. But i must be doing sth wrong because i always get an error when i try to create the stored procedure. Here it is:


CREATE PROCEDURE [dbo].[AddLogin]
	@IsEdit  bit,
@usernamename varchar(256),
@pw varchar(128)

AS

BEGIN
	
CREATE LOGIN @username WITH PASSWORD = N'@pw'  , 

END

this are the errors


Msg 102, Level 15, State 1, Procedure AddLogin, Line 10
Incorrect syntax near '@username'.
Msg 319, Level 15, State 1, Procedure AddLogin, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

obviously it's because the @name parameter. but I don't have any idea how to change. Can anybody help me?? Thanks in advance

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-16 : 14:53:32

You need dynamic sql for this

DECLARE@sql varchar(8000)
SELECT @sql='CREATE LOGIN '+@username+' WITH PASSWORD = N'+'''''+@pw+''''+' ,'
EXEC(@sql)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-16 : 16:12:19

MS wrote it for you, and it should still work : sp_addlogin

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources