Proper usage of input parameters in a stored procedure

Question

Problem: I am trying to create a stored procedure that creates a Login with a username passed in as a parameter. The parameter I pass in when I call the procedure is not used.

What I have tried: I have tried calling my procedure with exec dbo.proc_add_user 'TestLogin'.
I thought that calling it in this way would use "TestLogin" in place of @arg1.

Here is the stored procedure I am trying to create/alter:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_add_user]
(
    @arg1       varchar(255) = null
) AS

BEGIN
    CREATE LOGIN [@arg1] WITH PASSWORD=N'password' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

   GRANT ALTER ANY LOGIN TO [@arg1]
   GRANT CONNECT SQL TO [@arg1]
   GRANT VIEW ANY DEFINITION TO [@arg1]
   GRANT VIEW SERVER STATE TO [@arg1]

END

Instead of creating a Login named "TestLogin", it creates a Login named "@arg1". I have also tried setting @arg1 to a declared varchar named @userName:

declare @userName varchar(255) = @arg1
BEGIN
CREATE LOGIN [@userName] WITH PASSWORD=N'password' ...
...

I get the same results with this method. I will answer any questions as quick as possible, thanks.

well, you are trying to use dynamic SQL without actually making it dynamic — Lamak
– Lamak, Commented Apr 6, 2015 at 18:03
You'll have to create dynamic sql and run exec or sp_executesql — James Z
– James Z, Commented Apr 6, 2015 at 18:05
Thank you. I will research creating dynamic SQL procedures. I was creating this procedure by looking at a similar procedure that passes in a varchar variable. — Kyle Williamson
– Kyle Williamson, Commented Apr 6, 2015 at 18:06
The example you were looking at is probably correct because it was a DML query. What you have posted here is not. These statements are DDL and as such you can't use variables like that. — Sean Lange
– Sean Lange, Commented Apr 6, 2015 at 18:23

Kyle Williamson · Accepted Answer · 2015-04-06 19:59:50Z

1

Its important to know that brackets are a form of quoting. It's only necessary if the column name contains spaces or punctuation or conflicts with a reserved word.

And your query is a dynamic sql, so ... you can try:

DECLARE @SQLString VARCHAR(500);

SET @SQLString = 'CREATE LOGIN ' + @login_user + ' WITH PASSWORD=N''password'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON';

EXEC sp_executesql @SQLString, N'@login_user varchar(255)', @login_user=@arg1;

See this page for details.

Edit: Added a needed space before WITH

edited Apr 6, 2015 at 19:59

Kyle Williamson

2,2047 gold badges50 silver badges81 bronze badges

answered Apr 6, 2015 at 18:30

Iver

1808 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Proper usage of input parameters in a stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related