Optional parameters in SQL stored procedure

Question

I am trying to create a stored procedure that has optional parameters. I followed the instructions listed here. I also referenced this SO question. However I keep receiving the following error:

Error converting data type varchar to int.

It works when I execute it as

EXEC sp_get_user {id#}

or

EXEC sp_get_user NULL, {username}

but fails with

EXEC sp_get_user {username}

Stored Procedure

@id int = NULL,
@username nvarchar(50) = NULL

SELECT
    username = COALESCE(a.Username, b.Username),
    password = COALESCE(a.Password, b.Password),
    signup_date = COALESCE(a.SignedUpOn, b.Signup_Date)
FROM table1 a
FULL OUTER JOIN table 2 b
ON a.ID = b.ID
WHERE ((a.ID = @id OR @id IS NULL)
AND (a.Username = @username OR @username IS NULL)
OR (b.ID = @id OR @id IS NULL)
AND (b.Username = @username OR @username IS NULL))

I have tried adding the OPTION(RECOMPILE) and had no success. I want to make this dynamic so other developers can call this SP without having to specify all parameters each time. They will be connecting via LINQ if that makes a difference.

Old question found via google - why is this tagged as LINQ? "connecting via LINQ" is the only reference to LINQ — Dan, Jul 23 '14 at 09:43

score 5 · Accepted Answer · answered Apr 07 '11 at 20:55

5

use named parameters in that case

EXEC sp_get_user @username = {username}

if both parameters are optional, SQL server will go by position, so the first one you are passing in will map to the first one in the proc

answered Apr 07 '11 at 20:55

SQLMenace

132,095
25
206
225

score 4 · Answer 2 · answered Apr 07 '11 at 21:01

When executing stored procedures you have to conform for the parameters order as defined respectively, that is why the first and second statements works fine, in the first one EXEC sp_get_user {id#} you passed the id and ignored the user name, then it takes the defined default value. Moreover, in your second statement EXEC sp_get_user NULL, {username} you specified NULL for the id and you passed a value for the username parameter that is why it also works.

On the other hand, the third one EXEC sp_get_user {username} doesn't work because SQL Server treated your parameter {username} as the id value that is why it tries to convert it to integer and of course it will fail. Instead, you have to specify the paramer name while you are passing its value, see the following code:

EXEC sp_get_user @username = {username}

score 3 · Answer 3 · answered Apr 07 '11 at 20:56

Well, yes, obviously your last attempt will fail.

Your stored proc expects two parameters, in that order:

@id INT
@username NVARCHAR(50)

If you just simply call your stored procedure with a single parameter, then that parameter will be mapped to @id - and thus it needs to be an INT.

If you want to call your stored proc with just a single value for user name, you will need to used a named parameter - you cannot rely on the position (since the first and only parameter will always match to @id)

EXEC sp_get_user @username = {username}

Optional parameters in SQL stored procedure

3 Answers3