This is the SQL SERVER 2005 stored procedure.
It contains the data from the two diff tables.
(1)Customer :
Here clpid and cardid are composite primary keys with the identity field.It has storeid as foreign key.
The cardid is to be autogenerated everytime as the new customer is inserted.
I have to put the validations for the
* joindt = currentdate/todaydate
* dob is not greater than today date
* anniverdt is not less than equal to dob (in terms of year)
(2)Store :
Here storeid is the primary key.
Expand|Select|Wrap|Line Numbers
- ALTER PROCEDURE CustomerStoredProcedure
- @cardid INT OUTPUT,
- @clpid INT OUTPUT,
- @storename varchar(20),
- @firstname varchar(20),
- @lastname varchar(20),
- @joindt datetime,
- @anniverdt datetime,
- @dob datetime,
- @frmErrorMessage AS VARCHAR(256) OUTPUT,
- @RETURNVALUE AS INT OUTPUT,
- @RETURNID AS INT OUTPUT
- AS
- DECLARE
- @storeid AS INT
- BEGIN
- SET NOCOUNT ON
- --validation...
- IF ( @firstname IS NULL OR @firstname = '' )
- BEGIN
- SET @RETURNVALUE = -9
- SET @frmErrorMessage = 'FName is empty'
- RETURN -9
- END
- IF ( @lastname IS NULL OR @lastname = '' )
- BEGIN
- SET @RETURNVALUE = -9
- SET @frmErrorMessage = 'LName is empty'
- RETURN -9
- END
- if @DOB<@AnniversaryDate
- begin
- Select @frmErrorMessage ="AnniversaryDate cannot be greater then DOB"
- return -9
- end
- if @DOB=@AnniversaryDate
- begin
- Select @frmErrorMessage ="AnniversaryDate cannot be equal to DOB"
- return -9
- end
- if @DOB>=getdate()
- begin
- Select @frmErrorMessage ="DOB cannot be greater then Today's Date"
- return -9
- end
- BEGIN
- IF EXISTS (select * from storemaster where storename = @storename)
- select @storeid = storeid from storemaster where storename = @storename
- IF EXISTS ( SELECT firstname,lastname,joindt,anniverdt,dob,
- FROM customermaster WHERE clpid = @clpid and cardid=cardid)
- BEGIN
- UPDATE customermaster
- SET firstname = @firstname,
- lastname = @lastname,
- storeid=@storeid,
- joindt=@joindt,
- anniverdt=@anniverdt,
- dob=@dob,
- WHERE clpid= @clpid and cardid=@cardid
- SET @frmErrorMessage = 'Name and other information has been updated'
- SET @RETURNVALUE = 2
- END
- END
- BEGIN
- IF EXISTS (select * from storemaster where storename = @storename)
- select @storeid = storeid from storemaster where storename = @storename
- IF NOT EXISTS(Select firstname,lastname,address
- from customermaster
- where firstname = @firstname,lastname=@lastname)
- BEGIN
- INSERT INTO customermaster (firstname,lastname,joindt,anniverdt,dob,introducedby)
- VALUES (@firstname,@lastname,@joindt,@anniverdt,@dob,@introducedby)
- SELECT @clpid = SCOPE_IDENTITY()
- SELECT @cardid= SCOPE_IDENTITY()
- END
- ELSE
- BEGIN
- Print 'Name already exists'
- END
- SET @frmErrorMessage = ' Name and other information has been Inserted'
- SET @RETURNVALUE = 1
- END
- SET NOCOUNT OFF
- END
It gives me the error but i can't rectify it.
It is too big but can anyone help then it's fine....
I have mentioned how i created my above procedure.
Can anyone also help me to check or put the validations for the date..
Thanxs in advance...waiting for solution
Hope to get a suitable reply