March 12, 2020 at 1:18 pm
Hi,
I have a number of parameters (see below ) that come into a Stored procedure. In the application that this is called from the user may have only some of these (or maybe just one of these) being updated at a given call. Now I could do this with a series of IF statements Like if first name is not NULL set first name and update. But that seems a bad way to do it is there a better way, one in which I can do it in one step.
My parameters ccomnming in:
@first_name [varchar](50),
@last_name [varchar](50),
@add1 [varchar](50),
@add2 [varchar](50),
@city [varchar](25),
@state [varchar](2),
@zip [varchar](9),
Thank you
March 12, 2020 at 1:43 pm
just a thought (might be a bad one)
build a table variable or temp table and put the values in there in a format that mimics the target table (as a single row)
then use a merge or update statement
update target set firstname=case when source.firstname is null then target.firstname else source.firstname,
lasttname=case when source.lastname is null then target.lastname else source.lastname, ......
from xxx as source inner join yyy as target on …...
you get the picture?
MVDBA
March 12, 2020 at 1:44 pm
Building the update query using dynamic SQL is one way. Alternatively, something like this should work:
UPDATE t
SET FirstName = IsNull(@first_name, FirstName),
LastName = IsNull(@last_name, LastName), etc etc
FROM table1 t
WHERE SomeId = @some_id
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 12, 2020 at 1:45 pm
or even better, rather than building up the table variable inside the proc , could you pass a TVP through?
MVDBA
March 12, 2020 at 1:57 pm
phil's idea is better than mine
MVDBA
March 12, 2020 at 2:27 pm
Thanks that looks far better than what I was thinking.
Thank you
March 12, 2020 at 3:07 pm
I know this might sound stupid, but check to see if all of your parameters are NULL - then don't update a row that is setting itself to itself.
MVDBA
March 12, 2020 at 3:57 pm
The problem I ma haveing with your solution is that while it updates waht I want it sets all the other fields to noull wiping out what they had.
I need them to stay and the ones I changed only to change.
any ideas?
March 12, 2020 at 3:58 pm
I do not see how you would do this. would you have an example?
Thank you
March 12, 2020 at 4:04 pm
I do not see how you would do this. would you have an example?
Thank you
nope - both phil and my solution set the value to itself if the parameter is null - only the fields where the parameter is not null will be changed
from phil's example
SET FirstName = IsNull(@first_name, FirstName),
if you set @firstname to null then it keeps it's value (isnull(@firstname,firstname) will evaluate to the existing value)
MVDBA
March 12, 2020 at 4:11 pm
Well this is what I have below, and if the parameter is NULL it is wiping it out.
Am I doing it wrong somehow?
UPDATE [dbo].[DebtorACH]
SET first_name = IsNull(@first_name, first_name),
...others here
FROM [dbo].[Debtor]
WHERE file_no = @file_no
March 12, 2020 at 4:13 pm
Well this is what I have below, and if the parameter is NULL it is wiping it out.
Am I doing it wrong somehow?
UPDATE [dbo].[DebtorACH]
SET first_name = IsNull(@first_name, first_name),
...others here
FROM [dbo].[Debtor]
WHERE file_no = @file_no
are you passing in NULL or 'NULL' ?
MVDBA
March 12, 2020 at 4:16 pm
give me 2 minutes and i'll get you a working example
MVDBA
March 12, 2020 at 4:22 pm
Okay, I am working in a VB application and am just, for now, I am just changing the first name. So the other fields would be left blank which if I am not mistaken would be a NULL, but maybe I am wrong?
March 12, 2020 at 4:25 pm
ok - here is an example that does not null out your columns
CREATE TABLE x (id int, firstname VARCHAR(50),lastname VARCHAR(50))
GO
INSERT INTO x SELECT 1,'mike','jones'
go
CREATE PROC upd @id int,@firstname varchar(50),@lastname varchar(50)
AS
UPDATE x SET firstname=ISNULL(@firstname,firstname),lastname=isnull(@lastname,lastname)
GO
SELECT * FROM x
GO
EXEC upd 1, NULL,NULL
go
SELECT * FROM x
i put a select statement at the end to show you that calling the proc with NULL values does not change the data
MVDBA
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply