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
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 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy