Using Default Parameter in Stored Procedure call

Question

I have the following stored procedure

CREATE PROCEDURE [dbo].[usp_GetData]
@foo VARCHAR (20), @bar bit = 1
AS ...

This provides the correct result when called in SSMS.

EXEC dbo.usp_GetData @foo = 'Hellow World', @bar = 0

Although when calling within a C# application as per below

cmd.Parameters.Add(new SqlParameter("@foo", foo)); 
cmd.Parameters.Add(new SqlParameter("@bar", 0));

& is captured by the profiler as below.

exec dbo.usp_GetData @foo=N'Hello World',@bar=default

Does a parameter that overides the default have to be passed differently?

A bit is a boolean value what happens if you pass false instead of zero? — jcwrequests
– jcwrequests, Commented Sep 26, 2013 at 23:24
He is trying to pass 'false' for bit instead of the default. What happens if you try passing 'false' instead of '0'? — tofutim
– tofutim, Commented Sep 26, 2013 at 23:25
I want to provide a parameter & am in the above code but when the SQL is passed to the database it has default instead of 0 which is what I added for the @bar parameter @gunr2171 — ojhawkins
– ojhawkins, Commented Sep 26, 2013 at 23:26

John Saunders · Accepted Answer · 2013-09-27 00:14:10Z

4

Use

cmd.Parameters.AddWithValue("@bar", 0)

This way you know you actually passing the value.

edited Sep 27, 2013 at 0:14

John Saunders

162k26 gold badges252 silver badges403 bronze badges

answered Sep 26, 2013 at 23:32

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GSerg · Accepted Answer · 2013-09-26 23:26:21Z

2

new SqlParameter("@bar", 0) triggers the wrong overload - string parameterName, SqlDbType dbType. The value ends up being not set.

You want the one that sets the value, so it's ought to be new SqlParameter("@bar", (object)0).

This is why AddWithValue was introduced.

answered Sep 26, 2013 at 23:26

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

6 Comments

gunr2171 Over a year ago

How does 0 map to SqlDbType?

GSerg Over a year ago

@gunr2171 Nicely. It maps to SqlDbType BigInt, and it's a better match than to an object as far as the compiler concerned.

gunr2171 Over a year ago

I don't think I said it correctly. How does 0, an int, use the constructor form with a SqlDbType? Is there an implicit cast that allows this?

ojhawkins Over a year ago

This worked fine but so did passing false. Is there any disadvantage using false as it just seems cleaner. @GSerg

gunr2171 Over a year ago

@ojhawkins, your parameter is a bit, so providing the correct data type is always a good idea, instead of hoping sql will convert the integer correctly.

|

user1397832 · Accepted Answer · 2014-02-19 13:13:14Z

0

Better and safe :

cmd.Parameters.Add("@bar", SqlDbType.NVarChar, 16, "bar");

answered Feb 19, 2014 at 13:13

user1397832

Collectives™ on Stack Overflow

Using Default Parameter in Stored Procedure call

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related