Jul 6, 2000
Since i am newbie to sqlserver and & stored procedure. I need some help to modify the existing stored procedure.
The values and parameters are passing to stored procedure from Asp page.
My question is in Stored procedure, there is one value is Company name (vchCompany Name) , I need to check the values
passed from the asp page, whether the company name is already exists in the table. If exists display the pop up message window.
Thanks
The code is below:
************************************************** ****
CREATE procedure wbospsiCompany
@iSiteId int,
@iCompanyId int = NULL OUTPUT,
@chLanguageCode char(4),
@vchAssignedId varchar(255),
@vchCompanyName varchar(255),
@vchAddress1 varchar(255),
@vchAddress2 varchar(255),
@vchAddress3 varchar(255),
@vchCity varchar(255),
@chRegionCode char(4),
@chCountryCode char(4),
@vchPostCode varchar(40),
@vchPhoneNumber varchar(40),
@vchEmailAddress varchar(255),
@vchURL varchar(255),
@iCompanyTypeCode int,
@iCompanySubTypeCode int,
@iFamilyId int,
@iParentId int,
@iPrimaryContactId int,
@vchContactFirstName varchar(255),
@vchContactLastName varchar(255),
@iDivisionCode int,
@iSICCode int,
@iMarketSector int,
@vchTaxId varchar(255),
@vchDunnsNumber varchar(255),
@iPhoneTypeId int,
@iAddressTypeId int,
@iSourceId int,
@iStatusId int,
@bValidAddress tinyint,
@iAccessCode int,
@bPrivate tinyint,
@vchUser1 varchar(255) = NULL,
@vchUser2 varchar(255) = NULL,
@vchUser3 varchar(255) = NULL,
@vchUser4 varchar(255) = NULL,
@vchUser5 varchar(255) = NULL,
@vchUser6 varchar(255) = NULL,
@vchUser7 varchar(255) = NULL,
@vchUser8 varchar(255) = NULL,
@vchUser9 varchar(255) = NULL,
@vchUser10 varchar(255) = NULL,
@chInsertBy char(10) = NULL,
@dtInsertDate datetime = NULL,
@tiLockRecord tinyint = 0,
@tiRecordStatus tinyint = 1,
@tiReturnType tinyint = 1
AS
/*
** ObjectName: wbospsiCompany
**
** Project: Apollo
** SubProject:
** FileName: Insert.sql
** Type: Production
**
** Description: Inserts a record into the Company table.
**
** Valid Values for @tiLockRecord Valid Values for @tiReturnType
** ------------------------------ ------------------------------
** 1 = Lock Record 1 = Result Set
** 0 = Don't Lock Record 0 = Output Only
**
** Revision History
** ----------------------------------------------------------------------------
** Date Name Description
** ----------------------------------------------------------------------------
** 1/26/99 RobertA Created
** 10/11/99 GregP Dion schema compatibility changes
**
*/
BEGIN
/*
** Declare & initialize Local Variables
*/
declare @iReturnCode int,
@iWBOCPExists int,
@iWBOCPReturn int
select @iReturnCode = 0,
@iWBOCPExists = 0,
@iWBOCPReturn = 0
/*
** Declare & initialize Local Constants
*/
declare @PRE_OPTION int,
@CORE_OPTION int,
@POST_OPTION int,
@INSERT_CODE char(1),
@chUpdateBy char(4),
@dtUpdateDate datetime,
@dtModifiedDate datetime
select @PRE_OPTION = 0,
@CORE_OPTION = 0,
@POST_OPTION = @tiLockRecord * 2, /* Lock if @tiLockRecord = 1 */
@INSERT_CODE = "I",
@chUpdateBy = NULL,
@dtUpdateDate = NULL,
@dtModifiedDate = NULL
exec @iReturnCode = ospsiCompanyPre @iSiteId OUTPUT,
@iCompanyId OUTPUT,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@PRE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
/*
** Determine if @vchAssignedId and @iIndividualId should be set by configs
*/
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsuCustomerIds @iSiteId,
"Company",
@chCountryCode,
@iCompanyId OUTPUT,
@vchAssignedId OUTPUT
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Determine if WBOCP configurable procedure exists
*/
exec @iWBOCPExists = ospObjectExists "wbocpscCompany", "p"
if @iReturnCode <= 0
begin
begin transaction
if @iWBOCPExists = 1
begin
exec @iWBOCPReturn = wbocpscCompany @INSERT_CODE,
@iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT
if @iWBOCPReturn <> 0
begin
exec @iReturnCode = ospCheckError "p", @iWBOCPReturn
end
if @iReturnCode <= 0
begin
exec @iReturnCode = espcpCheckCustomerId @iSiteId, @iCompanyId
end
end
if @iReturnCode <= 0
begin
exec @iReturnCode = ospsiCompanyCore @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@CORE_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
/*
** Transaction Management
*/
if @iReturnCode <=0
begin
commit transaction
end
else
begin
rollback transaction
end
if @iReturnCode <= 0
begin
/*
** Call post procedure
*/
exec @iReturnCode = ospsiCompanyPost @iSiteId,
@iCompanyId,
@chLanguageCode OUTPUT,
@vchAssignedId OUTPUT,
@vchCompanyName OUTPUT,
@vchAddress1 OUTPUT,
@vchAddress2 OUTPUT,
@vchAddress3 OUTPUT,
@vchCity OUTPUT,
@chRegionCode OUTPUT,
@chCountryCode OUTPUT,
@vchPostCode OUTPUT,
@vchPhoneNumber OUTPUT,
@vchEmailAddress OUTPUT,
@vchURL OUTPUT,
@iCompanyTypeCode OUTPUT,
@iCompanySubTypeCode OUTPUT,
@iFamilyId OUTPUT,
@iParentId OUTPUT,
@iPrimaryContactId OUTPUT,
@vchContactFirstName OUTPUT,
@vchContactLastName OUTPUT,
@iDivisionCode OUTPUT,
@iSICCode OUTPUT,
@iMarketSector OUTPUT,
@vchTaxId OUTPUT,
@vchDunnsNumber OUTPUT,
@iPhoneTypeId OUTPUT,
@iAddressTypeId OUTPUT,
@iSourceId OUTPUT,
@iStatusId OUTPUT,
@bValidAddress OUTPUT,
@iAccessCode OUTPUT,
@bPrivate OUTPUT,
@vchUser1 OUTPUT,
@vchUser2 OUTPUT,
@vchUser3 OUTPUT,
@vchUser4 OUTPUT,
@vchUser5 OUTPUT,
@vchUser6 OUTPUT,
@vchUser7 OUTPUT,
@vchUser8 OUTPUT,
@vchUser9 OUTPUT,
@vchUser10 OUTPUT,
@chInsertBy OUTPUT,
@dtInsertDate OUTPUT,
@chUpdateBy,
@dtUpdateDate,
@tiRecordStatus OUTPUT,
@dtModifiedDate,
@POST_OPTION
if @iReturnCode <> 0
begin
exec @iReturnCode = ospCheckError "p", @iReturnCode
end
end
end
/*
** Manage return: return ID of record based upon @ReturnType param
*/
exec @iReturnCOde = ospManageReturn @tiReturnType,
@iReturnCode,
@iSiteID,
@iCompanyId,
"Company",
"I"
if @iReturnCode = 0
begin
select @iReturnCode = @iWBOCPReturn
end
return @iReturnCode
END
************************************************** ****
View 1 Replies
View Related
