Printed 2017/02/23 04:47PM

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';

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


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

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';

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.


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.