Help with creating procedures.

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

General SQL Server Forums

New to SQL Server Programming

Help with creating procedures.

Author

Topic

Arend
Starting Member

17 Posts

Posted - 2007-01-31 : 04:18:05

Hi,

I have to create 5 procedures and the study material I have dont explaine procedures enough.

My 5 procedures are as follows:

*sp_ProductInsert - Allows the user to add a record to the PRODUCT and PRICE tables.

*sp_PriceUpdate - Allows the user to change the price of a specified product. The start and end dates (columns) of the price must also be updated. Arguments: ProductID, new price, start date, end date.

*sp_DeleteProduct - Allows the user to delete a specified product. Records related to the product must be removed from the PRICE table but if the product appears on an invoice that is not more than 60days old, it may not be deleted. Argument: Product ID.

*sp_DeleteClient - Allows the user to delete a specified client. Argument: ClientID.

*sp_displayInvoice - Displays the details and the total cost of a specific invoice.

****************************************
Please id appreciate any help!!

my tables that I have:

CLIENT with id, name, address, tel

INVOICE with id, date, clientID

PRODUCT with code, description

PRICE with productCode, startDate, endDate, price
*********
I will also be greatful for extra reading regarding procedures.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 04:46:46

before we go into stored procedure, do you know how to do this ?

quote:
Allows the user to add a record to the PRODUCT and PRICE tables

Post your query here. Once you can get the query out, converting to stored procedure will be fairly easy

Arend
Starting Member

17 Posts

Posted - 2007-01-31 : 05:17:26

hi KH, thanx for your previous help. I know very little, its all new to me. All the examples i have are easy to understand. but its not enough for me to apply it to my work.

Arend
Starting Member

17 Posts

Posted - 2007-02-01 : 07:22:38

use testdb1
go

create procedure sp_ProductInsert
@product_code varchar(15),
@price_startDate varchar(10),
@price_endDate varchar(10),
@price_price varchar(10),
@product_description varchar(30)
as

insert into PRICE (product_code,price_startdate,price_enddate,price_price)
values (@product_code,@price_startDate,@price_endDate,@price_price)

insert into PRODUCT (product_code,product_description)
values (@product_code,@product_description)
go

************
i done this by myself, please have a look and tel me if its good and help me with the other procedures please.

thanx

Arend
Starting Member

17 Posts

Posted - 2007-02-01 : 07:24:09

please tell me if the data types are not correct.

Kristen
Test

22859 Posts

Posted - 2007-02-01 : 07:41:43

Good effort!

I would expect there to be a Foreign Key between PRICE and PRODUCT (such that before you can entered a price for a given product then the Product must already exist - if that makes sense?)

So ... on that basis I would move the PRODUCT insert above the PRICE insert.

The sizes and data types of the @ParameterVariables should match the columns in the database tables which they refer to.

I dunno how much "fluff" you need for course work, so these may be over-the-top, but I would add:

SET NOCOUNT ON (right under "AS")

Check for @@ERROR = 0 and @@ROWCOUNT = 1 after each insert, and appropriate error handling if the values are wrong.

Also I would add RETURN 0 for success, and return some non-zero value for any error. (You may also want to use RAISERROR to handle errors)

You could also use a transaction block - this would ensure that either a Product and Price are BOTH successfully added (i.e. without error) or NEITHER of them are (rather than inserting a Product and then getting an error inserting the Price and so on)

But as I say, they are a bit irrelevant if they are "beyond" what you are supposed to have learnt so far

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 07:41:47

quote:
Originally posted by Arend

please tell me if the data types are not correct.

Your data tyep should follow the data type in the table structure

Arend
Starting Member

17 Posts

Posted - 2007-02-01 : 08:01:23

Thanx ppl.

I know you are doing me a huge fave but please help me with the remaining 4 procedures. I am very new to SQL and it takes time for me figure out how to do these things. I am making an efford to learn more but time is against me. So i ask very nicely, PLEASE help.

Later.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 08:07:51

i will help you out with the syntax for this

quote:
sp_PriceUpdate - Allows the user to change the price of a specified product. The start and end dates (columns) of the price must also be updated. Arguments: ProductID, new price, start date, end date.


update <table_name>
set    col1 = @para1,
       col2 = @para2,
       . . .  
where  pk   = @pk

You have already go the Stored Procedure out. So writting a stored procedure should not be a problem now. It should be the update syntax.

Give it a try. If you are stuck or have any question, just post back here and ask. I am sure many people will be willing to answer that. But writing the stored procedure for someone's assignment is not the norm here.

Arend
Starting Member

17 Posts

Posted - 2007-02-01 : 08:11:57

Thanx KH,

I will do as you say. Thanx for the help.

Arend
Starting Member

17 Posts

Posted - 2007-02-01 : 08:13:57

Thanx KH,

I will try my best. Chat later then.

Arend
Starting Member

17 Posts

Posted - 2007-02-02 : 08:46:05

CREATE PROCEDURE sp_PriceUpdate

@product_code VARCHAR(15),
@price_startdate VARCHAR(10),
@price_enddate VARCHAR(10),
@price_price VARCHAR(10)

AS
UPDATE PRICE
SET

product_code = @product_code,
price_startdate = @price_startdate,
price_enddate = @price_enddate,
price_price = @price_price

WHERE product_code = @product_code
GO

CREATE PROCEDURE sp_DeleteClient
@client_id VARCHAR(10)

AS
DELETE CLIENT

WHERE client_id = @client_id
GO

CREATE PROCEDURE sp_DeleteProduct
@product_code VARCHAR(15)

AS
DELETE PRODUCT

WHERE product_code = @product_code

DELETE PRICE

WHERE product_code = @product_code
GO

************************

these are four of the procedures, ive done them to the best of my knowledge. please advice on corrections. i need help with the DeleteProduct, i dont know how to code for the 60day time limit. and can you please help me with the syntax for displayInvoice then i can go and figure it out.

Thanx

Kristen
Test

22859 Posts

Posted - 2007-02-02 : 13:08:03

You shouldn't prefix your Stored Procedures names with "sp_" because that has performance implications (again, probably not relevant to your course as such)

The comments I made before about error checking apply, of course, but I think for course work what you have done looks fine.

For the DeleteProduct you need something like:


CREATE PROCEDURE sp_DeleteProduct
@product_code VARCHAR(15)

AS
IF NOT EXISTS
(
    SELECT * 
    FROM MyInvoiceItemsTable 
    WHERE     InvoiceItemProductCode = @product_code 
          AND InvoiceDate >= DATEADD(Day, -60, GetDate())
)
BEGIN
    DELETE PRODUCT

    WHERE product_code = @product_code

    DELETE PRICE

    WHERE product_code = @product_code
END
GO

"syntax for displayInvoice"

Its just a Select statement for the invoice items and (probably a second) select statement for the SUM() of the Invoice Items values

Kristen

Arend
Starting Member

17 Posts

Posted - 2007-02-02 : 14:30:18

Thanks a lot for the help. I'll post another query if I get stuck again.

Arend

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources