does this SP add look correct? 2

[checkai]

This is accepting values from a form...there is a index on the client firstname, searchname combo...not sure if it is set up correctly....


Create proc VKFORMS_SPAddCLIENT @fName varchar(50),@sName varchar(50),@address varchar(8000),@city varchar(20),@state varchar(20),@country varchar(20),
@zip varchar(20),@phone varchar(25),@fax varchar(25),@email varchar(50),@industrycode int, @PIC int,@RM int,@CGroup int,@Notes varchar(8000),@URL varchar(50),
@createdBy int,@CrdLimit float(8),@xRisk bit, @xDevel bit,@xFin bit,@xChg bit,@xClient bit,@xBill money,@xVK bit,@xCIVIC bit,@xCFS bit,@xEB bit,
@xWM bit,@xCap bit,@xFileS bit,@xCase bit,@xProFX bit,@xScope bit
as
--Gets next available ID

declare @doubles as int;

set @doubles = (select count(*) from tblclient where searchname = @sName and firstname = @fName)

if @doubles = 0
begin
declare @id as int;
begin tran
set @id = (select nextkey from tblPrimaryKeyiD with (updlock) where TableName='tblClient')

INSERT INTO tblClient (clientID,clientRef,firstName,searchName,addressDetail,city,county,country,postcode,telephone,fax,email,industrycode,
PartnerID,managerID,clientgroupid,notes,createDate,suspended,xclient,url,createdby, TotalCreditLimit)
VALUES (@id,@id,@fName,@sName,@address,@city,@state,@country,@zip,@phone,@fax,@email,@industrycode, @PIC,@RM,@CGroup,@Notes,
convert(datetime,convert(char(10),getdate(),101)),0,0,@URL,@createdBy,@CrdLimit)

INSERT INTO tblClientExtraDetails (clientID,CARisk,CADevelopment,CAFinancial,CAChanges,SECClient,EstimatedAnnualBillings,FinChgVK,FinChgCivic,
FinChgCFS,FinChgEB,FinChgWM,FinChgVKCAP,CHGFileStorage,CHGCaseware,CHGProfx,CHGScope)
VALUES (@id,@xRisk, @xDevel,@xFin,@xChg,@xClient,@xBill,@xVK,@xCIVIC,@xCFS,@xEB,@xWM,@xCap,@xFileS,@xCase,@xProFX,@xScope)

commit tran

if @@ERROR <> 0
begin
Rollback TRAN
SELECT -1 as CODE
end
else
begin
SELECT @ID as CODE
update tblPrimaryKeyID set nextkey = nextkey + 1 where TableName='tblClient'
end
end
else
begin
SELECT -1 as CODE
end
--if trans goes through return clientid otherwise return -1......




THANKS,

DLC

 

[gfjzr]

checkai, the first thing I noticed was your address and notes variable are 8000 characters each. SQL has a limit of 8060 bytes per row for a table. If the insert exceeds this limit it will fail. Since the error you are getting is not supplied I can not offer other suggestions.

Gary

 

[SonOfEmidec1100]

Had a quick look - three comments: -

If you are using ADO to execute the sp you need SET NOCOUNT ON and OFF at the appropriate places. SET NOCOUNT ON at the start of the sp and SET NOCOUNT OFF after the commit trans and also before the final SELECT -1 as CODE

Your error handling wont work as @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later. In your example I would probably put an error handler after each INSERT.

If your handler was changed to work on a saved variable you would still have probs as you are doing the rollback after a commit, you should move the commit to before SELECT @ID as CODE.

Hope this helps

 

[checkai]

SonOfEmidec1100,
thanks for all the tips, could you post how to update this sp with your suggestions...I'm not familiar with SET NOCOUNT ON and OFF...what does that do?

Thanks,
DLC

 

[checkai]

I am still struggling with this statement. I am unsure of exactly where things need to be...any help would be great!

Thanks,
DLC

 

[SQLSister]

One problem I see is that you commit the transaction before checking for errors. Also you should assign @@Error to a variable before checking it. And I would check for errors after each insert, no point in wasting time trying the second insert if the first one failed.

BUt I would like to know what problem you are having and what error you are getting. THis woudl help us diagnose your difficulty.

 

[checkai]

the problem i have is the first insert errors out because of an index on the table where the concantenation of firstname and searchname must be unique, but the second insert statement continues...which we don't want...we also don't want the incrementing of the next key to happen...this stmt -> update tblPrimaryKeyID set nextkey = nextkey + 1 where TableName='tblClient'


please help me!! i'm going crazy!!

 

[SQLSister]

Ok your problem is in the order in which you do things.


What you do now is
first insert
Second insert
Commit transaction
Check error (which will be 0 if the second transaction committed)
If not 0 - rollback
If zero - increment ID

What you need to do is:
CHeck error and set to a variable
If Zero - do first insert
check error and set to a variable
If Zero - Second insert
CHeck error and set to a variable
Check status of variable
If zero - increment ID
CHeck error and set to a variable
Check status of variable
If ZEro - Commit Transactions
If not - Rollback transactions

 

[SonOfEmidec1100]

BOL explains it better than I can search for &quot;set nocount&quot;. Heres you sp modified with my suggestions, not tested. The error routines are a bit thin - you should be passing back specific error messages to help identify any unexpected errors. Refer BOL RAISERROR

Create proc VKFORMS_SPAddCLIENT @fName varchar(50),@sName varchar(50),@address varchar(8000),@city varchar(20),@state varchar(20),@country varchar(20),
@zip varchar(20),@phone varchar(25),@fax varchar(25),@email varchar(50),@industrycode int, @PIC int,@RM int,@CGroup int,@Notes varchar(8000),@URL varchar(50),
@createdBy int,@CrdLimit float(8),@xRisk bit, @xDevel bit,@xFin bit,@xChg bit,@xClient bit,@xBill money,@xVK bit,@xCIVIC bit,@xCFS bit,@xEB bit,
@xWM bit,@xCap bit,@xFileS bit,@xCase bit,@xProFX bit,@xScope bit
as
--Gets next available ID

declare @doubles as int;

SET NOCOUNT ON

set @doubles = (select count(*) from tblclient where searchname = @sName and firstname = @fName)

if @doubles = 0
begin
declare @id as int;
begin tran
set @id = (select nextkey from tblPrimaryKeyiD with (updlock) where TableName='tblClient')

INSERT INTO tblClient (clientID,clientRef,firstName,searchName,addressDetail,city,county,country,postcode,telephone,fax,email,industrycode,
PartnerID,managerID,clientgroupid,notes,createDate,suspended,xclient,url,createdby, TotalCreditLimit)
VALUES (@id,@id,@fName,@sName,@address,@city,@state,@country,@zip,@phone,@fax,@email,@industrycode, @PIC,@RM,@CGroup,@Notes,
convert(datetime,convert(char(10),getdate(),101)),0,0,@URL,@createdBy,@CrdLimit)
if @@ERROR <> 0
begin
Rollback TRAN
SET NOCOUNT OFF
SELECT -1 as CODE
RETURN
end

INSERT INTO tblClientExtraDetails (clientID,CARisk,CADevelopment,CAFinancial,CAChanges,SECClient,EstimatedAnnualBillings,FinChgVK,FinChgCivic,
FinChgCFS,FinChgEB,FinChgWM,FinChgVKCAP,CHGFileStorage,CHGCaseware,CHGProfx,CHGScope)
VALUES (@id,@xRisk, @xDevel,@xFin,@xChg,@xClient,@xBill,@xVK,@xCIVIC,@xCFS,@xEB,@xWM,@xCap,@xFileS,@xCase,@xProFX,@xScope)

if @@ERROR <> 0
begin
Rollback TRAN
SET NOCOUNT OFF
SELECT -1 as CODE
RETURN
end

SELECT @ID as CODE
update tblPrimaryKeyID set nextkey = nextkey + 1 where TableName='tblClient'
if @@ERROR <> 0
begin
Rollback TRAN
SET NOCOUNT OFF
SELECT -1 as CODE
RETURN
end
commit tran
end
else
begin
SET NOCOUNT OFF
SELECT -1 as CODE
end
--if trans goes through return clientid otherwise return -1......