CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
Kyle Williamson
Kyle Williamson

Reputation: 2184

Proper usage of input parameters in a stored procedure

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.

Upvotes: 0

Views: 106

Answers (1)

Iver
Iver

Reputation: 180

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

Upvotes: 1

Related Questions