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, Sep 26 '13 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, Sep 26 '13 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, Sep 26 '13 at 23:26

score 4 · Answer 1 · edited Sep 27 '13 at 00:14

4

Use

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

This way you know you actually passing the value.

edited Sep 27 '13 at 00:14

John Saunders

answered Sep 26 '13 at 23:32

Yuriy Galanter

score 2 · Accepted Answer · answered Sep 26 '13 at 23:26

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 '13 at 23:26

GSerg

How does `0` map to `SqlDbType`? – gunr2171 Sep 26 '13 at 23:29
@gunr2171 Nicely. It maps to `SqlDbType BigInt`, and it's a better match than to an `object` as far as the compiler concerned. – GSerg Sep 26 '13 at 23:33
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? – gunr2171 Sep 26 '13 at 23:35
This worked fine but so did passing `false`. Is there any disadvantage using false as it just seems cleaner. @GSerg – ojhawkins Sep 26 '13 at 23:38
2

@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. – gunr2171 Sep 26 '13 at 23:42
@gunr2171 Yes, there is an [implicit cast that allows this](http://stackoverflow.com/q/16960428/11683). – GSerg Sep 27 '13 at 09:43

score 0 · Answer 3 · answered Feb 19 '14 at 13:13

0

Better and safe :

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

answered Feb 19 '14 at 13:13

3 Answers3