Writing a Stored Procedure

[blackbirdMIT]

It's my first time to create sprocs. I need a mentor. Please help!

If you have read the book 'Beginning MS SQL Server 2000 Programming by Riordon', then you must know about the Aromatherapy database.

My question is...
How can I write a sproc that would allow me to add a caution for an oil? The sprocs should accept input parameters - the oil ID and the caution ID.

I tried creating the sproc using the wizard, but I don't know where to go from there. Can you guide me please?

 

[eramgarden]

Have you looked at Books OnLine for stored proc info?

 

[BogdanMBM]

Well, I did'nt read that book, so I need to know the structure of your table to be abe to help you. So post that table structure and we will see...

 

[blackbirdMIT]

Tables Properties:

CAUTIONS
CautionID -PK - Int, 4 Length
CautionNum - Int, 4 Length
Caution - nvarchar, 50
Description - ntext, 16, allow null

OILCAUTIONS
OilID
CautionID

OIL
OilID
OilName
LatinName
---------

 

[BogdanMBM]

Create Procedure MyProcedure (
@argintOilID int,
@argintCautionID int
) AS

insert into OilCautions (OilID,CautionID) Values @argintOilID, @argintCautionID
GO



If you wanna create a Caution and assign it to an oil in the same time, then:

Create Procedure MyProcedure (
@argintOilID int,
@argintCautionID int,
@argintCautionNum int,
@argvchrCaution nvarchar(50),
@argtextDescription text
) AS
declare @varvchrError varchar(100) -- keeps the error message.

begin transaction MyTransaction
insert into Cautions (CautionID, CautionNum, Caution, Description)
values (@argintCautionID, @argintCautionNum, @argvchrCaution, @argtextDescription)
if @@error <> 0
begin
select @varvchrError = 'Error creating the caution.'
goto ErrOut
end
insert into OilCautions (OilID,CautionID) Values @argintOilID, @argintCautionID
if @@error <> 0
begin
select @varvchrError = 'Error assigning the caution to oil.'
goto ErrOut
end
commit transaction MyTransaction

return 1

ErrOut:
rollback transaction
raiserror (50099, 16, 1, @varvchrError, 'MyProcedure')
return 0
GO


You also have to declare on the server the error No. 16, atherways you'll get an error on the raiserror line.
That's all, if I anderstand your problem.
If this is not what you want, please tell me!

 

[BogdanMBM]

I've forgot:

before the insert into OilCautions statement you should check for valid OilID, as fallows:

if exist(select * from Oil where OilID = @argintOilID)
begin
insert into OilCautions (OilID,CautionID) Values @argintOilID, @argintCautionID
if @@error <> 0
begin
select @varvchrError = 'Error assigning the caution to oil.'
goto ErrOut
end
end
else
begin
select @varvchrError = 'That oid das not exist.'
goto ErrOut
end

Replace that peace of code with this one, please!
Doing so, you will be shure you do not insert a Coution for an Oil thea has been deleted by someone else.

 

[blackbirdMIT]

BogdanMBM:
Thanks for your quick reply. Let me show you my first sproc, then tell me what you think.

Stored Procedure
----------
CREATE Proc InsertCaution_VIEW
@OilID int,
@CautionID int
as
INSERT INTO OilCautions (OilID, CautionID) VALUES (@OilID,@CautionID)
SELECT *
FROM Cautions_View

GO
----------


Cautions_View
-----------
CREATE VIEW dbo.Cautions_VIEW
AS
SELECT dbils.OilName AS [Oil Name], dbo.Cautions.Description AS Caution
FROM dbo.Cautions INNER JOIN
dbilCautions ON dbo.Cautions.CautionID = dbilCautions.CautionID INNER JOIN
dbils ON dbilCautions.OilID = dbils.OilID
------------

I'm trying to make it simple for now.
1) Does my sproc make any sense to you?
2) How can I alter it where I can insert cautions (example: Skin Irritant) for an oil (example: Jasmine) item?
3) How am I going to execute my procedure to allow input of parameters and caution text?

 

[BogdanMBM]

1.)
Well, it works, isn't it? So, if thet's what you want it to do, it's O.K.
My objections:
From what I immagine, OilCautions is a &quot;relations table&quot; (if I may say so). So, every OilID in OilCautions should exist in Oil and every CautionID in OilCautions should exist in Cautions. This can be done eather by my SP, or by foreign key constraints on your table design (see Books OnLine about that). If you are not (but I think You should) interested in keeping this constfaints, then your SP is a good one...

2.)
I don't understand the question. Daz not my SP do that ?!?

3.)
Why do you need the caution text in your variant of SP sence you are inserting just a record in OilCautions table (which daz not contain a CautionText column)?

 

[blackbirdMIT]

Thanks for the input!

One more thing:

Can you show me how to create a function that accept integer parameters then display the names of the oils that have that number of cautions? So that means I have to use the view I created (see above).