Hi,

I'm looking for some advise concerning a design issue which involves VFP7 and SQL Server. Here's a quick overview of the situation:

We receive data from SAP. The data comes in specially formatted text files. We parse the data using VFP and then import the data into SQL Server tables. This is fairly easy stuff, but one of the tables (payment instructions) doesn't have a primary key. The primary key is the combination of each field. The corresponding SQL Server table contains an identity field. What I would like to do is the following:

- read/parse text file into a VFP cursor
- SCAN through cursor and send record to a SQL Server SPROC
- SPROC checks if record already exists. If yes, returns PK (identity field), otherwise the record is inserted and the PK is returned.
- The returned PK is stored in the VFP cursor and available for further processing of related tables.

I could write this using SPT, but then the record would be sent twice to the server if the record doesn't exist yet. I would rather like to send the record to a SPROC that takes care of this.

However, I have no experience writing SPROCS and am not sure how to write it. Here's a draft which doesn't work but should outline the idea:

CREATE PROCEDURE usp_Instructions_Insert
@Field1 char(10) ,
@Field2 char(3) ,
@FieldN char(3) ,
@PK int OUTPUT,
@IsNewRecord int OUTPUT

AS

SET NOCOUNT ON

/* check if record already exists */
SELECT pk FROM instructions
 WHERE Field1=@Field1
   AND Field2=@Field2
   AND FieldN=@FieldN

IF @@ROWCOUNT=1
      /* above select returned 1 row, so record already exists. Get PK  */
      SET @PK = pk /* causes syntax error */
      SET @NewRecord = 0
 ELSE
    IF @@ROWCOUNT = 0
         /* record doesn't exist, so we add it and return the PK  */
       BEGIN
         BEGIN TRANSACTION
         INSERT INTO instructions (Field1, Field2, FieldN) VALUES(@Field1, @Field2, @FieldN)
         SELECT  @pk = @@identity
         SET @NewRecord = 1
         COMMIT TRANSACTION
       END
    ELSE
      /* more than one record found. Shouldn't happen.  */     
      RAISERROR()

GO

Questions:
- does this strategy make sense?
- if yes, could someone help me with the SPROC?
- should I add a UNIQUE CONSTRAINT on all these fields in SQL Server?
- if yes is there a big performance penalty of adding such a complex CONSTRAINT?
- are there any other and better ways of doing this (I probably don't want to use DTS)

Any advise is greatly appreciated.