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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

115 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

115 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
Constraint Violating Yak Guru

252 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

115 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
Constraint Violating Yak Guru

252 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

115 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  
 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