May 21, 2012 at 8:25 pm
I want to setup a stored procedure for a sql server 2008 r2 that can update between 0 to 8 columns on one table. The stored procedure will take the 8 parameters and compare the parameter values to the actual values for the corresponding columns in the table.
If there is a difference beween the value(s) in the table and the value(s) in the parameter, the value(s) in the table will be updated. However the problem is when there are null values in the parameters, I do not want to update the acutal column(s) with null values.
Thus can you suggest t-sql statements I can use to solve this problem?
May 21, 2012 at 10:00 pm
ISNULL()
COALESCE()
May 21, 2012 at 11:40 pm
niall.baird (5/21/2012)
ISNULL()COALESCE()
Correct! But in case the OP needs a little further guidance, I offer this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Update_MyTable
@keyINT= NULL
,@col1VARCHAR(50)= NULL
,@col2VARCHAR(50)= NULL
,@col3VARCHAR(50)= NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @rc INT
IF @key IS NULL RETURN -1
BEGIN TRY
UPDATE #MyTable
SET col1 = ISNULL(@col1, col1)
,col2 = ISNULL(@col2, col2)
,col3 = ISNULL(@col3, col3)
WHERE @key =
SET @rc = @@ROWCOUNT
END TRY
BEGIN CATCH
SET @rc = -ERROR_NUMBER()
END CATCH
RETURN @rc
END
GO
CREATE TABLE #MyTable
( INT, col1 VARCHAR(50), col2 VARCHAR(50), col3 VARCHAR(50))
INSERT INTO #MyTable
SELECT 1, 'Dwain', 'X', 'C'
SELECT * FROM #MyTable
EXEC dbo.Update_MyTable
1-- @key
,@col1 = 'Dwaine'
SELECT * FROM #MyTable
DROP TABLE #MyTable
DROP PROCEDURE dbo.Update_MyTable
Returns:
### - Number of rows updated (if positive)
0 - If no rows were updated (no errors)
-1 - If no key value was passed in (to avoid updating unintended rows)
-### - Negative of SQL Error number if TRY failed
Note that the above will not set a non-NULL column value to NULL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2012 at 11:51 pm
dwain.c (5/21/2012)
niall.baird (5/21/2012)
ISNULL()COALESCE()
Correct! But in case the OP needs a little further guidance, I offer this:
I'm just lazy... 😀
May 22, 2012 at 12:01 am
niall.baird (5/21/2012)
dwain.c (5/21/2012)
niall.baird (5/21/2012)
ISNULL()COALESCE()
Correct! But in case the OP needs a little further guidance, I offer this:
I'm just lazy... 😀
I had actually posted something quite similar recently so it was easy to find it, make a couple of minor mods and go with it.
You might be surprised how few people take the time to add in exception handling and return codes when they're new to the game. It is so easy it's ridiculous, but just as easy to omit I guess.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 12:58 am
dwain.c (5/22/2012)
niall.baird (5/21/2012)
dwain.c (5/21/2012)
niall.baird (5/21/2012)
ISNULL()COALESCE()
Correct! But in case the OP needs a little further guidance, I offer this:
I'm just lazy... 😀
You might be surprised how few people take the time to add in exception handling and return codes when they're new to the game. It is so easy it's ridiculous, but just as easy to omit I guess.
+ 1
Using Try->Catch blocks is so much easier than
SET @err = @@error
IF @err <> 0
BEGIN
...
END
May 22, 2012 at 4:57 am
niall.baird (5/22/2012)
SET @err = @@errorIF @err <> 0
BEGIN
...
END
The really funny thing is that I used to use this form too. Until I realized the SET statement would never get executed if there was a fatal error in the prior SQL statement!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 12:36 pm
thanks all the suggestions work!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply