October 23, 2007 at 12:16 pm
I am tasked with creating a generic stored procedure to insert a record. sounds simple but I am hitting a road block.
CREATE PROCEDURE usp_table1_ins
@Param0,
@Param1 = 0,
@Param2 = 0,
@Param3 = 0,
@Param4 int output
AS
INSERT INTO Table1 (Col0, Col1, Col2, Col3)
VALUES (@Param0, @Param1, @Param2, @Param3)
IF @@ROWCOUNT > 0
@Param4 = 1
The table is defined with defaults for Col1,2 & 3. I understand that when adding records through a proc like this, those defaults are ignored. That is why I added them to the Parameter list in the proc definition. Nulls are not allowed for Col1, 2 & 3.
PROBLEM: When calling the proc (EXEC usp_table1_ins 1, null, null, null, @Param4 output) I get an error stating that Col3 does not allow nulls. Well, I know that! I defaulted the param to "0" so that the passed null for the param value would be changed to "0".
Why is the stored procedure ignoring my defualt value?
October 23, 2007 at 12:35 pm
Defaults in SQL Server do not convert NULL into a default value, they convert empty parameters into the default value. This is the same as setting a default for a table column. If you try to insert NULL into a field that has a default on a table, you will get the null put into the field. If you insert and either skip the field in your insert statement altogether, or use the DEFAULT keyword, it will use the default.
INSERT MyTable (Col1, Col2) VALUES (1,NULL) --puts NULL into Col2 always
INSERT MyTable (Col1) VALUES (1) --uses default for Col2
INSERT MyTable (Col1, Col2) VALUES (1,DEFAULT) --use default for Col2
To get your procedure to use the default, you need to exclude the parameter in your parameter list:
CREATE PROC MyProc @Param1 INT, @Param2 INT = 0
EXEC MyProd @Param1=0 --don't specify parameter 2 and it uses the default
If you have to specify the NULL when you call it, you need to handle the NULL in your procedure:
CREATE PROC MyProc @Param1 INT, @Param2 INT
AS
SET @Param2 = COALESCE(@Param2,0)
...
October 23, 2007 at 1:57 pm
In addition :
IF @@ROWCOUNT > 0
@Param4 = 1
should be:
IF @@ROWCOUNT > 0
SET @Param4 = 1
* Noel
October 23, 2007 at 2:24 pm
Thank you for the response. Unfortunately, I am using an output parameter and do not have the luxury of just excluding the column in the call to the stored proc. I also need to use the proc to insert a record regardless if I have the values for Columns 1 - 3 or not. If values are passed, great. If not, I need to use the defaults set in the table definition. I was trying to avoid the step of checking each parameters for null values and then handling them as a seperate step. I would have hoped that between the defaults set on the table and the defaults set in the proc that one of them would handle the null. Apparently that is not how SQL operates and I will need to do the evaluation in the proc.
Thank you again...
October 23, 2007 at 2:36 pm
I think the easiest fix to your current code would be the following. Don't try to convert the input parameters, but do check the values as they are being inserted into the table and replace them there with their default values.
CREATE PROCEDURE usp_table1_ins
@Param0,
@Param1 int,
@Param2 int,
@Param3 int,
@Param4 int output
AS
INSERT INTO Table1 (Col0, Col1, Col2, Col3)
VALUES (@Param0, isnull(@Param1,0), isnull(@Param2,0), isnull(@Param3,0))
IF @@ROWCOUNT > 0
@Param4 = 1
October 24, 2007 at 6:31 am
Tami D (10/23/2007)
Thank you for the response. Unfortunately, I am using an output parameter and do not have the luxury of just excluding the column in the call to the stored proc. I also need to use the proc to insert a record regardless if I have the values for Columns 1 - 3 or not. If values are passed, great. If not, I need to use the defaults set in the table definition. I was trying to avoid the step of checking each parameters for null values and then handling them as a seperate step. I would have hoped that between the defaults set on the table and the defaults set in the proc that one of them would handle the null. Apparently that is not how SQL operates and I will need to do the evaluation in the proc.Thank you again...
You must be calling the proc by doing this:
EXEC proc 1,2,3,@Output OUTPUT
If you called the proc by doing this:
EXEC proc @Param1 = 1, @Param2 = 2, @Param3 = 3, @Param4 =4, @Output OUTPUT
You can then make the call this way:
EXEC proc @Param4 = @Output OUTPUT, @Param4 = 4, @Param1 = 1
The order and whether or not you included defaulted values won't matter if you name the parameters. If you're counting on the order of the parameters, you have to pass all of them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply