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_addsrvrolemember '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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: