Showing posts with label stored procedures. Show all posts
Showing posts with label stored procedures. Show all posts

Tuesday, September 08, 2015

Defaults with parameters in SQL Server stored procedures gotcha

I was talking with someone today and this person told me that the stored procedure doesn't work as expected

Here is what the simplified code looked like

@SomeParam int,
@SomeOtherParam int = 1,
@SomeThirdParam int
SELECT @SomeOtherParam

The person told me that when calling the proc and passing in NULL, the value would be NULL, he expected the value to be 1 since that is the default. Running the code like this will return the value NULL

EXEC prTest 1,null,3

In order to omit this parameter you can't be passing it in. If your parameter is the last one then you can just omit the value and pass in the first 2 parameters. If your parameter is not last,  then use named parameters instead of positional parameters. if you run the query like this, you will get back the value 1
EXEC prTest @SomeParam = 1,@SomeThirdParam = 3

Here is an example where the last parameter has a default
@SomeParam int,
@SomeOtherParam int ,
@SomeThirdParam int =3
SELECT @SomeThirdParam

Running that proc with just the first 2 values will return the default

EXEC prTest 1,2

So to recap..... a default will only be applied when the parameter is not passed in, if you pass in a NULL, which is technically the absence of a value, NULL will be used

Thursday, September 03, 2015

Did you know you can pass false and true to SQL Server bit parameters?

I noticed a stored procedure with a bit parameter and the default was false, I have never seen this before in SQL Server. You can do this in a language like c# but not in SQL Server right?

Let's take a look

Create this proc

create procedure prTest
@BitTrue bit = true,
@BitFalse bit = False,
@BitNotFalse bit = -2
Select @BitTrue as BitTrue, @BitFalse as BitFalse,
@BitNotFalse as BitNotFalse

Now execute the sored procedure
EXEC prTest

BitTrue BitFalse BitNotFalse
1 0 1

As you can see true gets converted to 1, false gets converted to 0 and any number that is not 0 will be converted to 1 as well

What happens when you pass in false for the parameter that had a default of true?

EXEC prTest @BitTrue = false

BitTrue BitFalse BitNotFalse
0 0 1

How about variables..can you use true and false with bit datatype variables?
Let's try it out

DECLARE @BitTrue bit 
SET @BitTrue = true

Msg 207, Level 16, State 1, Line 2
Invalid column name 'true'.

DECLARE @BitTrue bit 
SET @BitTrue = false

Msg 207, Level 16, State 1, Line 3
Invalid column name 'false'.

As you can see you can  use true and false with parameters in a proc but not with variables.