problem in creating store procedure

Question

0.00/5 (No votes)

See more:

i want to implement two queries using store procedure

first i want to use count query to check wheather the product already exits or not. then if the product doesn't exit. then i want to insert a data. other wise throw an error that record already exits.

i want to implement such kind of thing in storeprocedure.. but don't know how to do..

C#

query= Select Count * from ShoppingCart where Productid='"+@productid+"'

int count= obj.ohh(query)
if(Count>)

{
Response.Redirect("Error Page.aspx")

}
else

INSERT INTO SHOPPINGCART
	(CUSTOMERID,PRODUCTID,QUANTITY) 
	VALUES(@CustomerID,@ProductID,@Quantity

)

Posted 23-Sep-11 9:47am

codegeekalpha

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2011-09-23T09:54:00

Solution 1

Something like this (probably needs tweaking):

C#

query = "declare count int;set count = select count (*) from table where blah=blah; if (count = 0) insert into table blah blah;"
SqlCommand cmd = new SqlCommand(query, conn);
int recsAffected = sqlCommand.ExecuteNonQuery();
if (recsAffected == 0)
{
    Response.Redirect("Errorpage.aspx");
}

Posted 23-Sep-11 9:54am

#realJSOP

Comments

codegeekalpha 23-Sep-11 16:07pm

i want to do it using store procedures..

codegeekalpha 23-Sep-11 16:14pm

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddToCart]
@CustomerID varchar(50),
@ProductID int,
@Quantity int,
@Count int
@Count=select count (*) from table where ProductID=@ProductID;
If(@Count==0)
AS
BEGIN
INSERT INTO ShoppingCart
(CustomerID,Quantity,ProductID)
VALUES(@CustomerID,@Quantity,@ProductID)
END

codegeekalpha 23-Sep-11 16:14pm

i wrote it like this but getting error

Rajesh Anuhya 23-Sep-11 23:23pm

can you give me what error you are getting??

kris444 · Accepted Answer · 2011-09-23T17:10:00

Hi
You can simply do

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddToCart] 
	@CustomerID varchar(50),
	@ProductID int,
	@Quantity int
As
If Not Exists (Select 1 From table where ProductID = @ProductId)
 INSERT INTO ShoppingCart	(CustomerID,Quantity,ProductID) VALUES(@CustomerID,@Quantity,@ProductID)

Deepthi Aravind · Accepted Answer · 2011-09-23T19:31:00

Solution 3

CREATE PROCEDURE [PROCEDURENAME]
@PRODUCTID INT,
AS

DECLARE @COUNT INT,
SELECT @COUNT = SELECT * FROM SHOPPINGCART WHERE PRODUCTID=@PRODUCTID
IF(@COUNT>0)
RAISERROR('YOUR MSG', 16, 1)
ELSE
INSERT INTO SHOPPINGCART
(CUSTOMERID,PRODUCTID,QUANTITY)
VALUES(@CustomerID,@ProductID,@Quantity)

Posted 23-Sep-11 19:31pm

Deepthi Aravind

Comments

P.Salini 24-Sep-11 2:54am

I think in 1st query you need to specify count(*) instead of *