CODEWITHSUNDEEP
linqsql-server-2008stored-proceduresoptional-parameters
jon3laze
jon3laze

Reputation: 3196

Optional parameters in SQL stored procedure

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.

Upvotes: 2

Views: 1358

Answers (3)

Mohammed A. Fadil
Mohammed A. Fadil

Reputation: 9377

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}

Upvotes: 4

marc_s
marc_s

Reputation: 754258

Well, yes, obviously your last attempt will fail.

Your stored proc expects two parameters, in that order:

  1. @id INT
  2. @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}

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 134933

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

Upvotes: 5

Related Questions