Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: Help stored procedures

    Hello ,

    I am in a state of conflict with my c# asp.net web app. Basically i have a stored procedure that updates my customer table with the param supplied, and generates the primary key incrementally. My problem is returning the pk to my asp.net.

    Procedure looks like so:

    ALTER Procedure newUser (
    /* Param List */

    @fname varchar(20),
    @lname varchar(20),
    @address1 varchar(20),
    @address2 varchar(20),
    @city varchar(20),
    @province varchar(20),
    @postalCode varchar(7),
    @country varchar(20),
    @phone varchar(10),
    @email varchar(30),
    @receiveNews bit,
    @archive bit,
    @business varchar(10),
    @fax varchar(10)
    )
    AS

    BEGIN TRANSACTION
    INSERT INTO customer (
    fname,
    lname,
    address1,
    address2,
    city,
    province,
    postalCode,
    country,
    phone,
    email,
    receiveNews,
    archive,
    business,
    fax
    )
    VALUES (
    @fname,
    @lname,
    @address1,
    @address2,
    @city,
    @province,
    @postalCode,
    @country,
    @phone,
    @email,
    @receiveNews,
    @archive,
    @business,
    @fax
    )
    COMMIT
    RETURN

    The syntax has been verified correct and does populate my customers table with valid data.

    the column that i wish to return is called "pk_customerId".
    I tried using "RETURN pk_customerId" but it gives me an error that the column does not exist even thogh it does.

    Any ideas.

    Thanks in advance

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    in short, you can do this:

    return (scope_identity())

    but i'd declare a @variable and an @error before your begin tran, then right before your commit do this:

    select @error=@@error, @variable=scope_identity()
    if @error != 0 begin
    raiserror ('jkshdk fskjhdf kjshdf jkshdfh', 10, 1)
    rollback tran
    end
    commit
    return @variable

Posting Permissions

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