Easy SP question from Newbie - 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 2000 Forums

Transact-SQL (2000)

Easy SP question from Newbie

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2002-02-24 : 09:53:49

Hi,
Looking for help for combining two SPs. Currently I am using dynamic HTML to see if a record exists, than I either update or insert the record. I am trying to get it all done in the SP.
Any point in the right direction would be helpful.
Here are the two SPs as they are now.

Create PROCEDURE dbo.upreview_HTML
@product_id nvarchar(40), @sav_html ntext, @updated datetime
AS
UPDATE Review_HTML
SET sav_HTML=@sav_html, Last_Review=@updated
WHERE Product_ID=@product_id

Create PROCEDURE dbo.upreviewnew_HTML
@product_id nvarchar(40), @sav_html ntext, @updated datetime
AS
INSERT INTO Review_HTML (Product_ID, sav_HTML, Last_Review)
VALUES (@Product_ID, @sav_html, @updated)

Thanks in advance,
Chris

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-24 : 10:20:13

How about doing it in ine SP. Can get the server datetime instead of passing it in too.

Create PROCEDURE upreview_HTML
@product_id nvarchar(40),
@sav_html ntext
AS
if exists (select * from Review_HTML where Product_ID=@product_id)
begin
UPDATE Review_HTML
SET sav_HTML = @sav_html,
Last_Review = getdate()
WHERE Product_ID = @product_id
end
else
begin
INSERT Review_HTML (Product_ID, sav_HTML, Last_Review)
VALUES (@Product_ID, @sav_html, getdate())
end

could also (not keen on it though)
UPDATE Review_HTML
SET sav_HTML = @sav_html,
Last_Review = getdate()
WHERE Product_ID = @product_id

if @@rowcount = 0 and @@errror = 0
begin
INSERT Review_HTML (Product_ID, sav_HTML, Last_Review)
VALUES (@Product_ID, @sav_html, getdate())
end

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/24/2002 10:21:56

chrispy
Posting Yak Master

107 Posts

Posted - 2002-02-24 : 10:35:19

nr,
Thanks the first on is in use. The app is not to time sensitive. Just a made up time before it checks for any new reviews that have been aproved and posted.

one step closer to completion thanks to the SQLTEAM

Thanks again,
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources