Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: implement logic for update or insert

    I have a stored procedure that I need to either perform an update if a record exists or an insert if the record doesn't exist.

    Here is my procedure:

    CREATE PROCEDURE dbo.Insert_Temp_ContactInfo

    @sessionid varchar(50),
    @FirstName varchar(50),
    @LastName varchar(50),
    @SchoolName varchar(50),
    @address varchar(50),
    @City varchar(50),
    @State int,
    @Zip varchar(5),
    @Phone varchar(10),
    @Email varchar(50),
    @CurrentCustomer varchar(3),
    @ImplementationType int,
    @ProductType int = null,
    @Comment varchar(500)

    AS

    --check if a current record exists

    SET NOCOUNT ON

    SELECT FirstName, LastName, SchoolName, Address, City, State, Zip, Phone, Email, CurrentCustomer, ImplementationType, ProductType, Comment
    FROM dbo.Temp_ContactInfo
    WHERE sessionid = @sessionid


    SET NOCOUNT OFF


    --if exists update the record

    UPDATE dbo.Temp_ContactInfo
    SET
    FirstName = @FirstName,
    LastName = @LastName,
    SchoolName = @SchoolName,
    Address = @address,
    City = @City,
    State = @State,
    Zip = @Zip,
    Phone = @Phone,
    Email = @Email,
    CurrentCustomer = @CurrentCustomer,
    ImplementationType = @ImplementationType,
    ProductType = @ProductType,
    Comment = @Comment
    WHERE sessionid = @sessionid

    --if the record does not exist, then insert a new record

    INSERT INTO dbo.Temp_ContactInfo (sessionid, FirstName, LastName, SchoolName, address, City, State, Zip, Phone, Email, CurrentCustomer, ImplementationType, ProductType, Comment)
    VALUES (@sessionid, @FirstName, @LastName, @SchoolName, @address, @City, @State, @Zip, @Phone, @Email, @CurrentCustomer, @ImplementationType, @ProductType, @Comment)

    GO

    I think I can use an if statement. Can anyone help me out with this?

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    Code:
    CREATE PROCEDURE dbo.Insert_Temp_ContactInfo
    @sessionid varchar(50),
    @FirstName varchar(50),
    @LastName varchar(50),
    @SchoolName varchar(50),
    @address varchar(50),
    @City varchar(50),
    @State int,
    @Zip varchar(5),
    @Phone varchar(10),
    @Email varchar(50),
    @CurrentCustomer varchar(3),
    @ImplementationType int,
    @ProductType int = null,
    @Comment varchar(500)
    AS
    --check if a current record exists
    SET NOCOUNT ON
    if exists(select null from Temp_ContactInfo WHERE sessionid = @sessionid) 
    begin
     UPDATE dbo.Temp_ContactInfo
     SET 
      FirstName = @FirstName,
      LastName = @LastName,
      SchoolName = @SchoolName,
      Address = @address,
      City = @City,
      State = @State,
      Zip = @Zip,
      Phone = @Phone,
      Email = @Email,
      CurrentCustomer = @CurrentCustomer,
      ImplementationType = @ImplementationType,
      ProductType = @ProductType,
      Comment = @Comment
     WHERE 
      sessionid = @sessionid
    end 
    else
    begin
     INSERT INTO dbo.Temp_ContactInfo 
     (
      sessionid, 
      FirstName, 
      LastName, 
      SchoolName, 
      address, 
      City, 
      State, 
      Zip, 
      Phone, 
      Email, 
      CurrentCustomer, 
      ImplementationType, 
      ProductType, 
      Comment
     )
     VALUES 
     (
      @sessionid, 
      @FirstName, 
      @LastName, 
      @SchoolName, 
      @address, 
      @City, 
      @State, 
      @Zip, 
      @Phone, 
      @Email, 
      @CurrentCustomer, 
      @ImplementationType, 
      @ProductType, 
      @Comment
     )
    end
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One less call:
    Code:
    CREATE PROCEDURE dbo.Insert_Temp_ContactInfo
    @sessionid varchar(50),
    @FirstName varchar(50),
    @LastName varchar(50),
    @SchoolName varchar(50),
    @address varchar(50),
    @City varchar(50),
    @State int,
    @Zip varchar(5),
    @Phone varchar(10),
    @Email varchar(50),
    @CurrentCustomer varchar(3),
    @ImplementationType int,
    @ProductType int = null,
    @Comment varchar(500)
    AS
    --check if a current record exists
    SET NOCOUNT ON
    begin
     UPDATE dbo.Temp_ContactInfo
     SET 
      FirstName = @FirstName,
      LastName = @LastName,
      SchoolName = @SchoolName,
      Address = @address,
      City = @City,
      State = @State,
      Zip = @Zip,
      Phone = @Phone,
      Email = @Email,
      CurrentCustomer = @CurrentCustomer,
      ImplementationType = @ImplementationType,
      ProductType = @ProductType,
      Comment = @Comment
     WHERE 
      sessionid = @sessionid
    
    If @@Rowcount = 0
     INSERT INTO dbo.Temp_ContactInfo 
     (sessionid, 
      FirstName, 
      LastName, 
      SchoolName, 
      address, 
      City, 
      State, 
      Zip, 
      Phone, 
      Email, 
      CurrentCustomer, 
      ImplementationType, 
      ProductType, 
      Comment)
     VALUES 
     (@sessionid, 
      @FirstName, 
      @LastName, 
      @SchoolName, 
      @address, 
      @City, 
      @State, 
      @Zip, 
      @Phone, 
      @Email, 
      @CurrentCustomer, 
      @ImplementationType, 
      @ProductType, 
      @Comment)
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2004
    Posts
    124
    Thanks for the help! I appreciate it.

    If I may ask one additional question. I have several parameters that are integer datatypes.

    In my application, when I call the procedure, there are instances when the value passed to the parameter is null rather than being an integer value.

    I get "application uses a value of the wrong type for the current operation"

    I call the procedure like so:

    Code:
    set InsertTempInfo = Server.CreateObject("ADODB.Command")
    With InsertTempInfo
    .ActiveConnection = MM_DBConn_STRING
    .CommandText = "dbo.Insert_Temp_ContactInfo"
    .CommandType = 4
    .CommandTimeout = 0
    .Prepared = true
    .Parameters.Append .CreateParameter("@sessionid", 200, 1,50, usrid)
    .Parameters.Append .CreateParameter("@FirstName", 200, 1,50,fname)
    .Parameters.Append .CreateParameter("@LastName", 200, 1,50,lname)
    .Parameters.Append .CreateParameter("@SchoolName", 200, 1,50,schoolname)
    .Parameters.Append .CreateParameter("@address", 200, 1,50,address)
    .Parameters.Append .CreateParameter("@City", 200, 1,50,city)
    .Parameters.Append .CreateParameter("@State", 3, 1,4,state)
    .Parameters.Append .CreateParameter("@Zip", 200, 1,5,zip)
    .Parameters.Append .CreateParameter("@Phone", 200, 1,10,phone)
    .Parameters.Append .CreateParameter("@Email", 200, 1,50,email)
    .Parameters.Append .CreateParameter("@CurrentCustomer", 200, 1,3,currentcustomer)
    .Parameters.Append .CreateParameter("@ImplementationType", 3, 1,4,implementationtype)
    .Parameters.Append .CreateParameter("@ProductType", 3, 1,4,producttype)
    .Parameters.Append .CreateParameter("@Comment", 200, 1,500,comment)
    .Execute()
    End With
    the table allows null values, but how can I set the procedure to allow nulls for integer datatypes in the event that a null value is passed in?

    Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •