Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Specify the Parameter Names for Stored Procedures

I just ran across a case where a script was failing. Here's the part that's failing:

 

EXEC sp_addrolemember 'MyDomain\MyGroup', 'SomeRole';
GO 


 Do you see the issue? If not, that's understandable. Because this is correct:

 

EXEC sp_addsrvrolemember 'MyDomain\MyGroup', 'SomeRole';
GO 


The problem with the first query is due to the default order of the parameters for the stored procedures. The one at the database level, sp_addrolemember, specifies the role first. The one at the server level, sp_addsrvrolemember, has the login name first. Unless you happen to know that little detail (either because you've been studying for a certification test or because you've been burned by this sort of thing before), you won't catch the problem with the first query until it fails. There's an easy way to handle this, and that's just to specify the parameter names in your queries. If we do that with respect to the first case, we get:

 

EXEC sp_addrolemember @membername = 'MyDomain\MyGroup', @rolename = 'SomeRole';
GO 


Not only does this avoid the type of syntax error produced by getting the order wrong, but it also makes the query very readable. I say the latter because if you're dealing with SQL Server-based logins, sometimes it's hard to tell what's the user and what's the role. By specifying the parameter, you know exactly which is which for that particular query. And while I'm using sp_addrolemember as an example here, it's true of any execution of a stored procedure, especially ones that have a lot of parameters.

 

Comments

Posted by karthik babu on 27 August 2013

it was an eye opener for me... thanks!

Posted by nicky.van.vroenhoven on 27 August 2013

Nice post Brian.

I'd like to add that you could also specify one parameter and just leave others blank (in case they have a default value).

So for example:

EXEC example @param2=1, @param4=1

executes the stored procedure example with parameters 2 and 4 with a value of 1 and parameters 1 and 3 with their default value.

You could also do this:

EXEC example @param2=1, @param4=1, 1

This executes the stored procedure example with parameters 1, 2 and 4 with the value 1.

Nicky

Leave a Comment

Please register or log in to leave a comment.