update on required column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

update on required column

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2014-08-04 : 18:12:45

Hello,
I have 3 image url columns and all are required. When updating the record, I can get the url of only the uploaded image. If have have uploaded only 1 image while updating, when I pass in the parameters, the other 2 image urls will be null, but in the database there is an entry for both these image urls from previous uploads. My stored proc looks like this but not working: I want to update the imageurl when the passed in url is not null.

ImageUrl1 = CASE WHEN @ImageUrl1 <> null THEN @ImageUrl1 END,
ImageUrl2 = CASE WHEN @ImageUrl2 <> null THEN @ImageUrl2 END,
ImageUrl3 =CASE WHEN @ImageUrl3 <> null THEN @ImageUrl3 END,

Thank you.

rama108
Posting Yak Master

115 Posts

Posted - 2014-08-04 : 18:30:43

My work around was to get the image urls from the select statement before doing upate and the update is: There has to be a better solution than this.

Select @img3 = ImageUrl3
FROM Products
WHERE ProductID = @ProductID

ImageFolder3 =CASE WHEN @ImageFolder3 IS NOT null THEN @ImageUrl3 ELSE @img3 END

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-04 : 19:09:09

CREATE TABLE #URL
(
URLID INT ,
ImageUrl1 varchar(200),
ImageUrl2 varchar(200),
ImageUrl3 varchar(200),
)

INSERT INTO #URL
VALUES(1,'../images/some.jpg',NULL,'../images/some.gif')

SELECT * FROM #URL

DECLARE @URLID int = 1, @ImageUrl1 varchar(200) =NULL , @ImageUrl2 varchar(200) ='../images/another.jpg', @ImageUrl3 varchar(200) = '.../images/Updated.gif'

;With Input
AS
(
SELECT
@URLID URLID,
@ImageUrl1 ImageUrl1 ,
@ImageUrl2 ImageUrl2 ,
@ImageUrl3 ImageUrl3
)

UPDATE U
SET ImageUrl1 = ISNULL(i.ImageUrl1,u.ImageUrl1)
,ImageUrl2 = ISNULL(i.ImageUrl2,u.ImageUrl2)
,ImageUrl3 = ISNULL(i.ImageUrl3,u.ImageUrl3)
FROM Input I
INNER JOIN #URL U ON I.UrlID = U.URLID

SELECT * FROM #URL

rama108
Posting Yak Master

115 Posts

Posted - 2014-08-05 : 00:06:47

Thank you Michael. I have a huge table so creating a temp table is not an answer for me.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-05 : 06:04:29

The temp table was just used to demonstrate the methodology to demonstrate - you would not actually create a temp table. The code being demonstrated is from the declaration of the parameters onward.

rama108
Posting Yak Master

115 Posts

Posted - 2014-08-05 : 21:52:53

Thanks again Michael. In a hurry I scanned and saw the temp table but after reading properly now, makes sense. I changed my code accordingly. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources