BorlandTalk.com

Gary Wardell · Guest

Hi,

Is there an easier way of doing the following, like passing a veriant array
of names and values so that the fields to be updated can be built on the
fly?

Using either MS Sql Server 7 or 2000.

Gary

CREATE PROCEDURE dbo.UpdateLinkRecord
(@Id_1 [int],
@Name_3 [varchar](50),
@Address_4 [varchar](50),
@City_5 [varchar](20),
@State_6 [varchar](5),
@Zip_7 [varchar](15),
@SalesContact_8 [varchar](50),
@SalesPhone_9 [varchar](20),
@SalesFax_10 [varchar](20),
@SalesEMail_11 [varchar](255),
@TechnicalContact_12 [varchar](50),
@TechnicalPhone_13 [varchar](20),
@TechnicalEMail_14 [varchar](255),
@LeadEMail_15 [varchar](255),
@Path_18 [varchar](50),
@LinkType_19 [smallint],
@fNoShare_20 [bit],
@fFullUnder_21 [bit],
@States_22 [varchar](250),
@fValid_23 [bit],
@fAdjBox_25 [bit],
@wLateBucketMax_26 [smallint],
@wDefaultGroup_27 [int],
@wResultsCountMax_28 [smallint],
@DocTypeNvp_29 [text])

AS

IF @Id_1 = 0
BEGIN
SET IDENTITY_INSERT T1 ON
INSERT [LoanData].[dbo].[Link]

(Name,Address,City,State,Zip,SalesContact,SalesPhone,SalesFax,SalesEMail,Tec
hnicalContact,

TechnicalPhone,TechnicalEMail,LeadEMail,Path,LinkType,fFullUnder,fAdjBox,wRe
sultsCountMax,
States,DocTypeNvp,fValid)
Values

(@Name_3,@Address_4,@City_5,@State_6,@Zip_7,@SalesContact_8,@SalesPhone_9,@S
alesFax_10,@SalesEMail_11,@TechnicalContact_12,

@TechnicalPhone_13,@TechnicalEMail_14,@LeadEMail_15,@Path_18,@LinkType_19,@f
FullUnder_21,@fAdjBox_25,@wResultsCountMax_28,
@States_22,@DocTypeNvp_29,@fValid_23)
END
ELSE
BEGIN
UPDATE [LoanData].[dbo].[Link]

SET [Name] = @Name_3,
[Address] = @Address_4,
[City] = @City_5,
[State] = @State_6,
[Zip] = @Zip_7,
[SalesContact] = @SalesContact_8,
[SalesPhone] = @SalesPhone_9,
[SalesFax] = @SalesFax_10,
[SalesEMail] = @SalesEMail_11,
[TechnicalContact] = @TechnicalContact_12,
[TechnicalPhone] = @TechnicalPhone_13,
[TechnicalEMail] = @TechnicalEMail_14,
[LeadEMail] = @LeadEMail_15,
[Path] = @Path_18,
[LinkType] = @LinkType_19,
[fNoShare] = @fNoShare_20,
[fFullUnder] = @fFullUnder_21,
[States] = @States_22,
[fValid] = @fValid_23,
[fAdjBox] = @fAdjBox_25,
[wLateBucketMax] = @wLateBucketMax_26,
[wDefaultGroup] = @wDefaultGroup_27,
[wResultsCountMax] = @wResultsCountMax_28,
[DocTypeNvp] = @DocTypeNvp_29

WHERE
( [Id] = @Id_1)
END
GO

Brian Hollister · Guest

You can pass delimited strings into sql server sp's and once inside the proc
parse your string and execute accordingly...