Stored Procedure help - 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

SQL Server Development (2000)

Stored Procedure help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-07 : 09:51:50

I'm creating a favorite items list. If the item is not on the list, insert it into the db. If the item is on the list, dont do anything...but can return something so i know it already exists? So I can inform the user
(I'm creating a web application)

CREATE Procedure FavoriteCheck
(
@webcategoryid varchar(50),
@regid varchar (50),
@webcategoryid varchar (50),
@description varchar (255)
)

AS

DECLARE @CountFav int

SELECT
@CountFav = Count(webcategoryid)
FROM
tblfavitems
WHERE
webcategoryid = @webcategoryid
AND
regid = @regid

IF @CountFav > 0
*/ This fav is already on their list */

ELSE
INSERT INTO tblfavitems
(
userid,
regid,
webcategoryid,
description
)
VALUES
(
@userid,
@regid,
@webcategoryid,
@description
)

Andraax
Aged Yak Warrior

790 Posts

Posted - 2005-02-07 : 10:00:59

Just do a select where the comment is now? like

select 'The item already existed' as message

or something.

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-07 : 10:07:27

hmmm, i think i know what you mean...i have not tried that, could you provide me with a code example? am i going about this the correct way combing the stored procedure like this instead of 2 seperate ones?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-07 : 10:55:22

you could do:


IF @CountFav > 0
select 'Item already in the list.'
ELSE
INSERT INTO tblfavitems
(
userid, 
regid, 
webcategoryid, 
description
)
VALUES
(
@userid, 
@regid, 
@webcategoryid, 
@description
)
select 'Item added to the list.'

are you using asp.net for this? if you are you might wan't to reconsider doing this check on the front end.

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-07 : 11:01:49

I'm actually using ColdFusion for this web app. Right now I have 2 seperate SPROCS.
The first one runs and checks to see if the item is on the user's list, if its not on the list it inserts into the db. If it is on the list, it returns an error message.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-07 : 11:06:27

so what is your problem then? seems to me you have it licked...

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-07 : 11:27:04

Ok, i will continue with what I have orignally. I'm converting all my queries to SPROCS and just trying to learn different ways in the process.

Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources