SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 update on required column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

109 Posts

Posted - 08/04/2014 :  18:12:45  Show Profile  Reply with Quote
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

109 Posts

Posted - 08/04/2014 :  18:30:43  Show Profile  Reply with Quote
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

Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 08/04/2014 :  19:09:09  Show Profile  Reply with Quote
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
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 08/05/2014 :  00:06:47  Show Profile  Reply with Quote
Thank you Michael. I have a huge table so creating a temp table is not an answer for me.
Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 08/05/2014 :  06:04:29  Show Profile  Reply with Quote
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.
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 08/05/2014 :  21:52:53  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000