Hello all,
I am new to sql server 2005 and I am trying to write a stored
procedure that will do several things.
1) will check to see if a record exists. If it does it will throw an
error and return the error code to the program that called it.
2) if the record does not exist will check to see if the date
parameter is not null. If it is null it will fill it with getdate()
3) once all the above it done andit has not thrown the duplicate
record error will insert the new record
4)will return both the error code (zero if it was a success) and the
new id for the record.
Here is my problem.It will insert the new record and will return the
new id. However if there is already a category with the same name it
does not stop and return the error code.
Also it will not add the date if the parameter is null.
Once again I am new so please be gentle when slamming my code. It is
prolly sloppy.
Here is the SP
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Category_Create]
-- Parameters passed from the website
(
@CategoryName nvarchar(50) = NULL,
@PatentCategoryId int = NULL,
@description nvarchar(max) = NULL,
@Image1 image = NULL,
@Image2 image = NULL,
@CreateDate datetime = NULL,
@newid bigint Output,
@errorCode int Output
)
AS
BEGIN
--Set Default Value the Error Code
Set @errorCode = 0
--Check to see if the category already exists. If it does set
@errorCode to 20
If (Exists(
select CategoryName
from prdsrv_Category
where @CategoryName = CategoryName))
Begin
Set @errorCode = 20
End
Else
--Check to make sure error code is still 0. If it is continue with
the insert.
If @errorCode = 0
--Check to see is a create date was passed. If not set it to current
time.
Begin
If @CreateDate = Null
Set @CreateDate = getdate()
End
--Insert the values into the table.
Begin
Insert Into prdsrv_Category
( CategoryName,
ParentCategoryId,
CategoryDesc,
CategoryImage1,
CategoryImage2,
CreateDate )
Values
( @CategoryName,
@PatentCategoryId,
@description,
@Image1,
@Image2,
@CreateDate )
End
--Return the values we need to know if we succeeded and if so the id
number for the new row.
Select @newid = @@identity
Return @newid
Return @errorCode
End
Thanks in advance!!!
Help with a stored procedure
