SQL Server Performance Forum – Threads Archive

Storing visits to offer_details web page

Hi, I have a web page where users publish offers to sell products, then other users interested in these products go to search page, and specifying some filters (such as price, offer date, region, city, certificates, etc…) and submitting a button, they access to results page (results_page.aspx). In that results page they navigate forward and backward through the results, and when they see some interesting offer title (in a record) they click the offer title link to access to complete offer page (for example: offer_detail.aspx?offer_id=2586). Now I want to store in the DB visits to every offer ‘offer_detail.aspx#%92 page. How can I do it? Perhaps sum/add a visit to Offers table in visits column of the specified record (in this case offer_id=2586) ? If so, how can I add a visit (sum 1) in a column? Thus?:
UPDATE Offers
SET visits = +1 Thanks

---

Nearly there: UPDATE Offers
SET visits = visits + 1
WHERE Offer_id = 2586 The fault in the SET part wasn’t so bad, but how can you forget to use a WHERE statement?

---

Your code it doesn’ t work, most probably is because the first time a visit is stored there isn’ t any number to sum in that field, and nothing is stored. Is it correct?

---

Either use this: UPDATE Offers
SET visits = ISNULL(visits, 0) + 1
WHERE Offer_id = 2586 … or add default constraint for zero to the visits column, and after that run this query: UPDATE Offers
SET visits = 0
WHERE Offer_id IS NULL … because the default is only applied to new rows, not to existing rows.

---

Cool [8D] Now it works. Thank you I prefer this system:
UPDATE Offers
SET visits = ISNULL(visits, 0) + 1
WHERE Offer_id = 2586 I saw in some web pages that instead of showing ‘0’ value when none visit is made, it shows ‘-‘ symbol instead. Do you know how to achive this sign instead of ‘0’ value?

---

Forget it, this must be done in the application Is it a good practice execute this SP: USE marketdb
GO
CREATE PROCEDURE sum_visits
@Offer_id bigint
As
UPDATE Offers
SET visits = ISNULL(visits, 0) + 1
WHERE Offer_id = 2586
GO
..only for this DB operation? Or it would be better include that operation in a bigger SP that the web page (offer_detail.aspx) also calls just before this one?

---

Well, if you’re writing it as an SP then I would seriously suggest you actually use that @offer_id parameter in the WHERE clause – you’ve hard-coded 2586, so that would be the only offer ever to get an increase on visits … USE marketdb
GO
CREATE PROCEDURE sum_visits (@Offer_id bigint)
As
UPDATE Offers
SET visits = ISNULL(visits, 0) + 1
WHERE Offer_id = @OfferId
GO

---

No idea why you would want to combine the update with something else – you’ll have to be more specific about the something else bit!

---

Ok, then I will call that SP separately from others in my web app.<br /><br />Thank you!<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> you’ll have to be more specific about the something else bit!<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />More specific..? You must be joking.. [<img src=’/community/emoticons/emotion-5.gif’ alt=’‘ />] <br />

---

]]>