INSERT,UPDATEJOIN QUERY STORED PROCEDURE...HELP&CH

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

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT,UPDATEJOIN QUERY STORED PROCEDURE...HELP&CH

Author

Topic

sheena
Starting Member

45 Posts

Posted - 2007-05-05 : 15:42:26

Hello members,

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.


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

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<@anniverdt
				begin
					Select @frmErrorMessage ="AnniversaryDate cannot be greater then DOB"
					return -9
				end 
			if  @dob=@anniverdt
				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 and lastname=@lastname)
		BEGIN
           INSERT INTO customermaster (firstname,lastname,joindt,anniverdt,dob)
		       VALUES     (@firstname,@lastname,@joindt,@anniverdt,@dob)
           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

Pls can anyone checkout my stored procedure as i have created for the first time.
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 from the sql team........

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-06 : 03:22:30

What Error are you getting???

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-06 : 03:41:50

Here are some modification which i did.. check out if its works for you .. !!


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

	Select @frmErrorMessage  = Case 
									When IsNull(@firstname,'') = '' 
										Then 'FName is empty'
									When IsNull(@lastname,'')=''
										Then 'LName is empty' 
									Else 
										'0'
							   End 
	If (@frmErrorMessage <> '0') 
	Begin 
		SET  @RETURNVALUE = -9
		RETURN -9
	End 	
	Select @frmErrorMessage  = Case 
									When @dob<@anniverdt
										Then 'AnniversaryDate cannot be greater then DOB'
									When @dob=@anniverdt
										Then 'AnniversaryDate cannot be equal to DOB' 
									When @dob>=getdate()
										Then 'DOB cannot be greater then Today''s Date'
									Else 
										'0'
							   End 
	
	If (@frmErrorMessage <> '0') 
	Begin 
		SET  @RETURNVALUE = -9
		RETURN -9
	End 	
		
	IF EXISTS (select * from storemaster where storename = @storename)                                  
		   select @storeid = storeid from storemaster where storename = @storename                                          	                                       
	                    				

	IF EXISTS (
				 SELECT 1
				 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	             				

	  IF NOT EXISTS(
						Select 1
						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
		
	SET NOCOUNT OFF
END

Chirag

http://chirikworld.blogspot.com/

sheena
Starting Member

45 Posts

Posted - 2007-05-06 : 05:03:38

Thanxs...for solving the procedure.

At first it was giving me the error ::--
Cannot insert the value NULL into column 'cardid'INSERT fails.
The statement has been terminated.
No rows affected.
@RETURN_VALUE = -6

Now,also it gives me the error as
@RETURN_VALUE = -9

sheena
Starting Member

45 Posts

Posted - 2007-05-06 : 05:08:05

I think it does not allow to take the two id's as identity in sql.
So,i have incremented the id in other way as follows but then to it's not working..
It does not give me the error but the returns the value as


@RETURN_VALUE = -9

It does not inserts any rows..


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

	Select @frmErrorMessage  = Case 
									When IsNull(@firstname,'') = '' 
										Then 'FName is empty'
									When IsNull(@lastname,'')=''
										Then 'LName is empty' 
									Else 
										'0'
							   End 
	If (@frmErrorMessage <> '0') 
	Begin 
		SET  @RETURNVALUE = -9
		RETURN -9
	End 	
	Select @frmErrorMessage  = Case 
									When @dob<@anniverdt
										Then 'AnniversaryDate cannot be greater then DOB'
									When @dob=@anniverdt
										Then 'AnniversaryDate cannot be equal to DOB' 
									When @dob>=getdate()
										Then 'DOB cannot be greater then Today''s Date'
									Else 
										'0'
							   End 
	
	If (@frmErrorMessage <> '0') 
	Begin 
		SET  @RETURNVALUE = -9
		RETURN -9
	End 	
		
	IF EXISTS (select * from storemaster where storename = @storename)                                  
		   select @storeid = storeid from storemaster where storename = @storename                                          	                                       
	                    				

	IF EXISTS (
				 SELECT 1
				 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
	             				

	  IF NOT EXISTS(
						Select 1
						from customermaster
						where firstname = @firstname and lastname=@lastname
				  )
			BEGIN
			  select @cardid = isnull(max(cardid),0) + 1 from customermaster
					INSERT INTO customermaster (firstname,lastname,joindt,anniverdt,dob)
				    VALUES     (@firstname,@lastname,@joindt,@anniverdt,@dob)
					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
		
	SET NOCOUNT OFF
END

Pls help is needed...Can u test it

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-06 : 09:31:29

can you post the table structure with the and some sample data... !!

Chirag

http://chirikworld.blogspot.com/

sheena
Starting Member

45 Posts

Posted - 2007-05-06 : 10:26:25

Ya sure,


The first TABLE is customermaster ::
1) clpid  int(4), (P.K.) describes customer id
2) cardid int(4), (P.K.) autogenerates card id for each customer once
3) storeid int(4), (F.K.) 
4) firstname varchar(20), general info of customer
5) lastname varchar(20),
6) joindt datetime,
7) anniverdt datetime,
8) dob datetime

The second table is storemaster

1) storeid int(4),(P.K.)
2) storename varchar(20), details of the store for goods purchasing
3) storeaddress varchar(20)

I m working on the asp.net C# webapplication on the form i have the following controls...on CUSTOMER FORM
I can fill the following details for the customer....


clpid   ---------    101,102,103..........
cardid  ---------     1,  2,  3 ..........   
storename ---------   STATIONERY,LOCAL,......... 
firstname ---------   ABC,CDE,....... 
lastname ---------    XYZ,STU.......
joindt ---------      2/2/2005,12/5/2005........
anniverdt ---------   2/3/2004,12/12/1998  
dob ---------         4/6/1984,9/7/1970

As u asked for the sample data i have provided herewith hope this help to solve my problem...
I don't understand why it does not allows me to insert/update data ??Though there are not at all any errors in coding...
U can go thru at the top of the page where i have mentioned some details of the procedure...

Thanxs...

Hope to get now a suitable reply...as i m not able to solve it...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-06 : 15:17:44

Well, I got some got some doubt with your database design

1) Is CplID are autogenerated field (Identity), if so then why are you cardid??

If both CPLID and CARID form the composite key then, CLPID should not be any Identity.

Well, I have modified most of the query with the consideration that, CLPID is not an Identity field.. and here the table structure ...


Table Creation script.. 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_customermaster_storemaster]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[customermaster] DROP CONSTRAINT FK_customermaster_storemaster
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[customermaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[customermaster]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storemaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[storemaster]
GO

CREATE TABLE [dbo].[customermaster] (
	[clpid] [int] NOT NULL ,
	[cardid] [int] NOT NULL ,
	[storeid] [int] NULL ,
	[firstname] [varchar] (20) COLLATE Arabic_CI_AS NULL ,
	[lastname] [varchar] (20) COLLATE Arabic_CI_AS NULL ,
	[joindt] [smalldatetime] NULL ,
	[anniverdt] [smalldatetime] NULL ,
	[dob] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[storemaster] (
	[storeid] [int] NOT NULL ,
	[storename] [varchar] (20) COLLATE Arabic_CI_AS NULL ,
	[storeaddress] [varchar] (20) COLLATE Arabic_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[customermaster] WITH NOCHECK ADD 
	CONSTRAINT [PK_customermaster] PRIMARY KEY  CLUSTERED 
	(
		[clpid],
		[cardid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[storemaster] WITH NOCHECK ADD 
	CONSTRAINT [PK_storemaster] PRIMARY KEY  CLUSTERED 
	(
		[storeid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[customermaster] ADD 
	CONSTRAINT [FK_customermaster_storemaster] FOREIGN KEY 
	(
		[storeid]
	) REFERENCES [dbo].[storemaster] (
		[storeid]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

-- Now Insert the Records in the Table storemaster
INSERT INTO StoreMaster ( [storeid], [storename], [storeaddress])VALUES ( 1,'STATIONERY','Dubai')
INSERT INTO StoreMaster ( [storeid], [storename], [storeaddress])VALUES ( 2,'LOCAL','INDIA')

--Now Insert the Script for your Stored procedure.. !!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerStoredProcedure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CustomerStoredProcedure]
GO
CREATE 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
-- First Less start with All the validations over here... 
	Select @frmErrorMessage  = Case 
					When IsNull(@firstname,'') = '' 
						Then 'FName is empty'
					When IsNull(@lastname,'')=''
						Then 'LName is empty' 
					When datediff(year,@dob,@anniverdt) < 0
						Then 'AnniversaryDate cannot be Greater then DOB'
					When @dob=@anniverdt
						Then 'AnniversaryDate cannot be equal to DOB' 
					When @dob>=getdate()
						Then 'DOB cannot be greater then Today''s Date'
					Else 
						'0'
				    End 
	
-- Check if there is any error..			
	If (@frmErrorMessage <> '0') 
	Begin 
		SET  @RETURNVALUE = -9
		RETURN -9
	End 	
-- Now lets start with all the Calculations..!!! 		
	IF EXISTS (select * from storemaster where storename = @storename)                                  
		   select @storeid = storeid from storemaster where storename = @storename                                          	                                       
	Else 
	Begin 
		SET @frmErrorMessage = 'StoreID Not found for the Specfied Store ID'
		Return -9
	End 
	                    
--Now update the records if the specified clpid and cardid are already there in the database!!
	IF EXISTS 
		(
			 SELECT 1
			 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
			Return 2
		END
	             
--Now check for only clpid if cpid is already there in the database then insert  the record with incrementing CardId
	IF EXISTS 
		(
			 SELECT 1
			 FROM customermaster WHERE clpid = @clpid 
		) 
		BEGIN
			INSERT INTO customermaster (firstname,lastname,joindt,anniverdt,dob,storeid)
		          VALUES     (@firstname,@lastname,@joindt,@anniverdt,@dob,@storeid)

			SET  @frmErrorMessage = 'Name and other information has been Inserted'
			SET @RETURNVALUE = 2
			Return @RETURNVALUE
		END
--Now Finally Insert the record in the Database for the CardId
	  IF NOT EXISTS
		(
			Select 1
			from customermaster
			where firstname = @firstname and lastname=@lastname
  		)
			BEGIN
			  select @clpid = isnull(max(clpid),0) + 1 from customermaster
		          INSERT INTO customermaster (clpid,CardID,firstname,lastname,joindt,anniverdt,dob,storeid)
		          VALUES     (@clpid,@CardID,@firstname,@lastname,@joindt,@anniverdt,@dob,@storeID)
	--SELECT @cardid= SCOPE_IDENTITY()
			END
	    ELSE
		        BEGIN
			   Print 'Name already exists'  
		        END
			
	SET  @frmErrorMessage = ' Name and other information has been Inserted'
	SET @RETURNVALUE = 1
		
	SET NOCOUNT OFF
END
GO

--Now finally Testing 

Declare @FrmError Varchar(100),@ReturnError int
EXEC CustomerStoredProcedure 101,1,'STATIONERY','ABC','XYZ','20050202','20040302','19840604', 
			     @FrmError Out,@ReturnError Out, 0

--Select @FrmError,@ReturnError
Select * From customermaster  
EXEC CustomerStoredProcedure 101,2,'STATIONERY','PQR','XYZ','20050202','20040302','19840604', 
			     @FrmError Out,@ReturnError Out, 0
Select * From customermaster 

-- Output 
clpid       cardid      storeid     firstname            lastname             joindt                                                 anniverdt                                              dob                                                    
----------- ----------- ----------- -------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 
1.00        101.00      1.00        ABC                  XYZ                  02/02/2005 12:00:00 AM                                 02/03/2004 12:00:00 AM                                 04/06/1984 12:00:00 AM

(1 row(s) affected)

clpid       cardid      storeid     firstname            lastname             joindt                                                 anniverdt                                              dob                                                    
----------- ----------- ----------- -------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 
1.00        101.00      1.00        ABC                  XYZ                  02/02/2005 12:00:00 AM                                 02/03/2004 12:00:00 AM                                 04/06/1984 12:00:00 AM
2.00        101.00      1.00        PQR                  XYZ                  02/02/2005 12:00:00 AM                                 02/03/2004 12:00:00 AM                                 04/06/1984 12:00:00 AM

(2 row(s) affected)

I hope this solves your issues...

Chirag

http://chirikworld.blogspot.com/

ujjaval
Posting Yak Master

108 Posts

Posted - 2007-05-06 : 20:23:48

Just out of curiousity, the validations that gets done over here, are they part of some data transfer job on sql server or its coming from an application?

The reason I am asking is if it is coming from application, wouldn't it be easy to do that in application? or is it a good practice to validate at both bend, application and database?

-Ujjaval

sheena
Starting Member

45 Posts

Posted - 2007-05-07 : 00:45:23

thanxs...very much for ur help and solution..

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 01:34:10

Welcome!! Hope it worked for you

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources