--start customer_setcreate procedure [dbo].[customer_set] @name as varchar(50), @address as varchar(250), @mobile as varchar(50)asbegin set nocount on --validation if @name is null begin raiserror ('name cannot be empty.',16,1) end if len(@name)<3 begin raiserror ('name cannot be less than 3 characters.',16,1) end --data insertion begin try insert into [dbo].[customer] ([name] ,[address] ,[mobile]) values (@name ,@address ,@mobile) end try begin catch return (0) end catch return (1)end--end customer_set--start supplier_setcreate procedure [dbo].[supplier_set]@name as varchar(50),@address as varchar(250), @mobile as varchar(50)asbegin set nocount on --validation if @name is null begin raiserror ('please enter suppiler name.',16,1) end if len(@name)<3 begin raiserror ('supplier name cannot be less than 3 characters.',16,1) end --data insertion begin try insert into [dbo].[supplier] ([name] ,[address] ,[mobile]) values (@name ,@address ,@mobile) end try begin catch return (-1) end catch return (1)end--end supplier_set--start getvalidationconstraintcraete procedure [dbo].[getvalidationconstraint] --output values @emptycheck int output, @lencheck int =null output, @nodataexist int =null output, @true bit =null output, @false bit =null outputasbegin select @emptycheck=1 select @lencheck =2 select @nodataexist =3 select @true=1 select @false=0end--end getvalidationconstraint--start returnmessagecreate procedure [dbo].[returnmessage] --success, fail is the order of output parameter @success int output, @fail int output asset nocount onbegin select @fail=0 select @success=1 end--end returnmessage--start messagehelpercreate procedure [dbo].[messagehelper] --input values @field varchar(200) =null, @minlenght int =null, @validationconstraint int, --output values @validationmessage varchar(200) output asbegin --variables declare @empty_message varchar(50), @minimum_lenght_message varchar(50), @no_data_exist_message varchar(50) declare @empty int, @len int, @no_data_exist int declare @successed int, @failed int --message constraint set @empty_message = 'cannot be empty.' set @minimum_lenght_message ='cannot be less than' set @no_data_exist_message = 'no record found.' --get global values exec returnmessage @successed output, @failed output exec getvalidationconstraint @empty output, @len output, @no_data_exist output --set message if @validationconstraint = @empty begin if len(@field)<=0 begin raiserror('field name cannot be empty. storeprocedure/messagehelper',16,1) return @failed end select @validationmessage = @field + ' ' + @empty_message end if @validationconstraint = @len begin if @minlenght is null or @minlenght <=0 begin raiserror('minimum length cannot be empty. storeprocedure/messagehelper',16,1) return @failed end else begin select @validationmessage = @field + ' ' + @minimum_lenght_message + ' ' + convert(varchar, @minlenght) end end if @validationconstraint = @no_data_exist begin select @validationmessage = @no_data_exist_message endend--end messagehelper--start customer_setcreate procedure [dbo].[customer_set] --input values @name as varchar(50), @address as varchar(250), @mobile as varchar(50), --output values @lastid bigint output, @message varchar(200) =null outputasset nocount onbegin --constraint variables for readable return value declare @successed int, @failed int --constraint variables for readable validation operation declare @empty int, @len int begin try --get constraint value for successed and failed exec returnmessage @successed output, @failed output --get constraint value for validation. @empty is for empty check and @len is for length check common messaging system. exec getvalidationconstraint @empty output, @len output --validation if len(@name)=0 begin exec messagehelper 'name', @empty,@message output --it will generate a common empty message. return @failed-- readable failed return end if len(@name)<3 begin exec messagehelper 'name',3, @len,@message output --it will generate a common length check message. return @failed-- readable failed return end --data insertion insert into [dbo].[customer] ([name] ,[address] ,[mobile]) values (@name ,@address ,@mobile) select @lastid=scope_identity() end try begin catch -- error traping section declare @errormessage nvarchar(4000); declare @errorseverity int; declare @errorstate int; select @errormessage = error_message(), @errorseverity = error_severity(), @errorstate = error_state(); raiserror (@errormessage,@errorseverity,@errorstate); return @failed -- readable failed return end catch return @successed -- readable successed returnend--end customer_set--start customer_getcreate procedure [dbo].[customer_get] --output values @total_rows bigint output, @message varchar(200) =null output asbegin set nocount on --variables declare @successed int, @failed int declare @empty int, @len int, @no_data_exist int begin try --get constraint value exec returnmessage @successed output, @failed output exec getvalidationconstraint @empty output, @len output,@no_data_exist output --validation if (select count(customerid) from customer )<= 0 begin exec messagehelper '', @no_data_exist,@message output --it will generate common no data exist message. select @total_rows=0 return @successed end --data retrival select [customerid] ,[name] ,[address] ,[mobile] from [dbo].[customer] --get total rows select @total_rows=@@rowcount end try begin catch declare @errormessage nvarchar(4000); declare @errorseverity int; declare @errorstate int; select @errormessage = error_message(), @errorseverity = error_severity(), @errorstate = error_state(); raiserror (@errormessage,@errorseverity,@errorstate); return @failed end catch return @successedend--end customer_get--start customer_deletebyidcreate procedure [dbo].[customer_deletebyid] --input values @customerid bigint, @message varchar(200) =null outputasbegin --variables declare @successed int, @failed int declare @empty int, @len int begin try --get constraint value exec returnmessage @successed output, @failed output exec getvalidationconstraint @empty output, @len output --validation if @@customerid <=0 begin exec messagehelper 'customer id', @empty,@message output return @failed end --data deletion delete from [dbo].[customer] where (customerid = @customerid) end try begin catch declare @errormessage nvarchar(4000); declare @errorseverity int; declare @errorstate int; select @errormessage = error_message(), @errorseverity = error_severity(), @errorstate = error_state(); raiserror (@errormessage,@errorseverity,@errorstate); return @failed end catch return @successedend--end customer_deletebyid