Need help with sql server stored procedure

Hi,

I am new to writing stored procedure on sql server. I trying to write
a stored procedure for a shopping cart. I need to get the price for
dvd based on quantity from a price table.

select price from price where price.producttype =
shoppingcart.producttype AND
price.quantity = shoppingcart.quantity


When I execute the sql code, I get this error

The multi-part identifier "shoppingcart.producttype" could not be
bound.

I also get the same error for

shoppingcart.quantity"


How do I fix this error?

I appreciate any help. Thanks.


ALTER PROCEDURE [dbo].[sp_getShoppingCartItems]
(@CartID char(36))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Get the price based on producttype and quantity
DECLARE @price money
--select price from price where producttype =
shoppingcart.producttype

select price from price where price.producttype =
shoppingcart.producttype AND
price.quantity = shoppingcart.quantity
SELECT dvd.product_id, dvd.title, shoppingcart.quantity,
price.price * shoppingcart.quantity AS Subtotal
FROM shoppingcart INNER JOIN dvd
ON shoppingcart.product_id = dvd.product_id
WHERE shoppingcart.cart_id = @CartID
END

Re: Need help with sql server stored procedure

aboutjav.com@gmail.com wrote:
> Hi,
>
> I am new to writing stored procedure on sql server.

Please: always reveal the type _and version_ of database you are using. It
is relevant more often than you may know.

> I trying to write
> a stored procedure for a shopping cart. I need to get the price for
> dvd based on quantity from a price table.

When you get the price, what do you want to do with it? Return it to the
client? As an output parameter, or as a second resultset?

>
> select price from price where price.producttype =
> shoppingcart.producttype AND
> price.quantity = shoppingcart.quantity

This query will never work since you haven't included the shoppingcart table
(I assume this IS a table in your database?) in your FROM clause

>
>
> When I execute the sql code, I get this error
>
> The multi-part identifier "shoppingcart.producttype" could not be
> bound.
>
> I also get the same error for
>
> shoppingcart.quantity"

Assuming producttype and quantity are columns in the shoppingcart table (Are
they?), then I can only assume the problem is the same as I indicated above.

>
>
> How do I fix this error?
>
> I appreciate any help. Thanks.
>
>
> ALTER PROCEDURE [dbo].[sp_getShoppingCartItems]

Nothing to do with your problem but you should avoid using the "sp_" prefix
unless you are intending to create a system stored procedure. There is a
minor perfomance hit from using the prefix but the main problem will come if
you ever give one of your stored procedures the same name as an existing
system procedure - you will spend days tearing out your hair trying to
figure out what's wrong.

> (@CartID char(36))
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> -- Get the price based on producttype and quantity
> DECLARE @price money
> --select price from price where producttype =
> shoppingcart.producttype
>
> select price from price where price.producttype =
> shoppingcart.producttype AND
> price.quantity = shoppingcart.quantity
> SELECT dvd.product_id, dvd.title, shoppingcart.quantity,
> price.price * shoppingcart.quantity AS Subtotal
> FROM shoppingcart INNER JOIN dvd
> ON shoppingcart.product_id = dvd.product_id
> WHERE shoppingcart.cart_id = @CartID
> END

I'm guessing here without table names, column names and datatypes, a little
sample data and intended results. Please also clarify what you want to do
with that price value.



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"