Professional Web Applications Themes

Data From INSERT In Stored Procedure Does Not Appear In Table - Microsoft SQL / MS SQL Server

I have a table (say, Table1) defined as: Column Type ------ --------------- ID indentity int(Primary key) nameData varchar(32) In a stored procedure I want to query for a record with a particular value for nameData. If it is there, I want to retrieve its ID. If not, insert a new record. The procedure is defined as: ALTER PROCEDURE up_CheckName nameIn varchar(32) AS DECLARE tableId int DECLARE nameNew varchar(32) DECLARE table_cursor CURSOR FOR SELECT Table1.ID FROM dbo.Table1 WHERE Table1.nameData = nameIn -- Look up name in Table1 or insert new name OPEN table_cursor FETCH NEXT FROM table_cursor INTO tableId IF Cursor_Status('local', ...

  1. #1

    Default Data From INSERT In Stored Procedure Does Not Appear In Table

    I have a table (say, Table1) defined as:

    Column Type
    ------ ---------------
    ID indentity int(Primary key)
    nameData varchar(32)

    In a stored procedure I want to query for a record with a
    particular value for nameData. If it is there, I want to
    retrieve its ID. If not, insert a new record.

    The procedure is defined as:

    ALTER PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int
    DECLARE nameNew varchar(32)
    DECLARE table_cursor CURSOR FOR
    SELECT Table1.ID FROM dbo.Table1
    WHERE Table1.nameData = nameIn

    -- Look up name in Table1 or insert new name
    OPEN table_cursor
    FETCH NEXT FROM table_cursor
    INTO tableId


    IF Cursor_Status('local', 'table_cursor') <= 0
    BEGIN
    INSERT INTO dbo.Table1 (Table1.nameData)
    VALUES (nameIn)
    SELECT tableId = IDENTITY
    END
    CLOSE table_cursor

    In the query yzer debugger I execute the procedure
    with nameIn parameter set to a value known NOT to be in
    Table1. Stepping through I see the value of tableId
    obtained from IDENTITY is incremented as expected.
    However, the new record is not present in Table1. What is
    going on? Why? How do I fix it?
    Leonard Guest

  2. #2

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table


    "Leonard" <com> wrote in message
    news:4c0101c356aa$7ad89830$gbl... 


    David Guest

  3. #3

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    Get rid of the cursor:

    ALTER PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int
    SELECT tableId = ID FROM dbo.Table1
    WHERE Table1.nameData = nameIn

    IF ROWCOUNT = 0
    BEGIN
    INSERT Table1 (nameData) VALUES (nameIn)
    SELECT tableId = SCOPE_IDENTITY()
    END

    SELECT tableId
    go

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Leonard" <com> wrote in message news:4c0101c356aa$7ad89830$gbl...
    I have a table (say, Table1) defined as:

    Column Type
    ------ ---------------
    ID indentity int(Primary key)
    nameData varchar(32)

    In a stored procedure I want to query for a record with a
    particular value for nameData. If it is there, I want to
    retrieve its ID. If not, insert a new record.

    The procedure is defined as:

    ALTER PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int
    DECLARE nameNew varchar(32)
    DECLARE table_cursor CURSOR FOR
    SELECT Table1.ID FROM dbo.Table1
    WHERE Table1.nameData = nameIn

    -- Look up name in Table1 or insert new name
    OPEN table_cursor
    FETCH NEXT FROM table_cursor
    INTO tableId


    IF Cursor_Status('local', 'table_cursor') <= 0
    BEGIN
    INSERT INTO dbo.Table1 (Table1.nameData)
    VALUES (nameIn)
    SELECT tableId = IDENTITY
    END
    CLOSE table_cursor

    In the query yzer debugger I execute the procedure
    with nameIn parameter set to a value known NOT to be in
    Table1. Stepping through I see the value of tableId
    obtained from IDENTITY is incremented as expected.
    However, the new record is not present in Table1. What is
    going on? Why? How do I fix it?

    Tom Guest

  4. #4

    Default Data From INSERT In Stored Procedure Does Not Appear In Table

    Thanks for the speedy response! Somewhere in the Books
    Online I saw a statement (that I have not been able to
    find again) that indicated I had to FETCH from a cursor to
    retrieve data from a result set in a stored procedure.
    This is how the cursor got in there.
     
    is 
    Leonard Guest

  5. #5

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table


    I tried the following and still the new record did not
    appear in the table.

    ALTER PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int

    SELECT tableId = ID FROM dbo.Table1
    WHERE nameData = nameIn

    IF ROWCOUNT = 0
    BEGIN
    INSERT INTO dbo.Table1 (Table1.nameData)
    VALUES (nameIn)
    SELECT tableId = IDENTITY
    END
    The tableId increments but the new record is still not in
    Table1. 
    ----- 
    news:4c0101c356aa$7ad89830$gbl... 
    is 
    Leonard Guest

  6. #6

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    Yes, I saw this response already, see my other reply.


    "Leonard" <com> wrote in message
    news:038001c356bb$577d04f0$gbl... 


    Aaron Guest

  7. #7

    Default Data From INSERT In Stored Procedure Does Not Appear In Table

    I am also having a problem with simple insertion in stored
    procedures. Could we be talking about the same problem?

    A sample from the thread, "Data From INSERT In Stored
    Procedure Does Not Appear In Table":

    I tried the following and still the new record did not
    appear in the table.

    ALTER PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int

    SELECT tableId = ID FROM dbo.Table1
    WHERE nameData = nameIn

    IF ROWCOUNT = 0
    BEGIN
    INSERT INTO dbo.Table1 (Table1.nameData)
    VALUES (nameIn)
    SELECT tableId = IDENTITY
    END
    The tableId increments but the new record is still not in
    Table1.

    Leonard Guest

  8. #8

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    As Aaron said, how about posting your DDL plus INSERT's of your data so we can try this ourselves?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Leonard" <com> wrote in message news:4e9b01c356bc$f23a6720$gbl...
    I am also having a problem with simple insertion in stored
    procedures. Could we be talking about the same problem?

    A sample from the thread, "Data From INSERT In Stored
    Procedure Does Not Appear In Table":

    I tried the following and still the new record did not
    appear in the table.

    ALTER PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int

    SELECT tableId = ID FROM dbo.Table1
    WHERE nameData = nameIn

    IF ROWCOUNT = 0
    BEGIN
    INSERT INTO dbo.Table1 (Table1.nameData)
    VALUES (nameIn)
    SELECT tableId = IDENTITY
    END
    The tableId increments but the new record is still not in
    Table1.


    Tom Guest

  9. #9

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table


    "Leonard" <com> wrote in message
    news:4e9b01c356bc$f23a6720$gbl... 

    The sp is correct.

    Try the following in a different database.

    David


    CREATE TABLE table1 (
    id int IDENTITY NOT NULL ,
    nameData varchar(32)
    )
    GO


    create PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int

    SELECT tableId = ID FROM Table1
    WHERE nameData = nameIn

    IF ROWCOUNT = 0
    BEGIN
    INSERT INTO Table1 (Table1.nameData)
    VALUES (nameIn)
    SELECT tableId = IDENTITY
    END
    go

    exec up_CheckName 'someName'
    exec up_CheckName 'someName'

    select * from table1


    David Guest

  10. #10

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    The table does not have any triggers. The structure is
    very simple. The table has no index or unique constraint
    other than the primary key. The plan is to set a unique
    index on nameData at some point, however.

    Column Type
    ------ ---------------
    ID indentity int(Primary key-autocounter)
    nameData varchar(32)

    I am running the procedure in the query yzer
    debugger. The behavior occurs when the nameIn parameter
    is a value that is not in the nameData field of any
    existing record in Table1.

    Sample data in Table1

    ID nameData
    --- --------------
    7 KalimbaMain
    8 Offerings
    9 Contacts
    15 LoyaltyDemo

    The new entry is "TransSelect".

    Note that the entries in ID are generated by an
    autocounter. The values 1-6 and 10-14 were used by
    attempts to insert records via the stored procedure. The
    existing record were inserted using ADO from a Windows
    client app.
     
    index, that might be 
    from? Can you post 
    demonstrates the [/ref]
    in 
    >
    >
    >.
    >[/ref]
    Leonard Guest

  11. #11

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    > Column Type 

    Can you please provide the DDL according to the second paragraph in
    http://www.aspfaq.com/5006?
     

    Have you tried running the procedure in Query yzer *without* the
    debugger?

    A


    Aaron Guest

  12. #12

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    Using your data and my original proc - shown below - everything works fine:

    CREATE PROCEDURE up_CheckName
    nameIn varchar(32)
    AS
    DECLARE tableId int
    SELECT tableId = ID FROM dbo.Table1
    WHERE Table1.nameData = nameIn

    IF ROWCOUNT = 0
    BEGIN
    INSERT Table1 (nameData) VALUES (nameIn)
    SELECT tableId = SCOPE_IDENTITY()
    END

    SELECT tableId
    go


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Leonard" <com> wrote in message news:06e701c356df$dcb1ee50$gbl...
    Here's the DDL:
    /* This SQL DDL script was generated by Microsoft
    Visual Studio (Release Date: LOCAL BUILD). */

    /* Driver Used : Microsoft Visual Studio - Microsoft
    SQL Server Driver. */
    /* Doent :
    D:\Projects\Kalimba\KalimbaSite\TestDB.vsd.
    */
    /* Time Created: July 29, 2003 2:32
    PM. */
    /* Operation : From Visio Generate
    Wizard. */
    /* Connected data source :
    TestDB
    */
    /* Connected server :
    SIMBA
    */
    /* Connected database :
    master
    */



    /* Create TestDB
    database.
    */
    use master

    go

    create database "TestDB"
    ON PRIMARY
    ( NAME = TestDB, FILENAME = 'C:\Program
    Files\Microsoft SQL Server\MSSQL\Data\TestDB.mdf', SIZE =
    3 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
    LOG ON
    ( NAME = TestDB, FILENAME = 'C:\Program
    Files\Microsoft SQL Server\MSSQL\Data\TestDB.ldf', SIZE =
    1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )

    go

    use "TestDB"

    go

    /* Create new
    table "Table1".
    */
    /* "Table1" : Table of
    Entity1
    */
    /* "ID" : ID identifies
    Entity1
    */
    /* "nameData" : nameData is of
    Entity1
    */
    create table "Table1" (
    "ID" int identity not null,
    "nameData" varchar(32) not null)

    go

    alter table "Table1"
    add constraint "Table1_PK" primary key ("ID")


    go


    /* This is the end of the Microsoft Visual Studio
    generated SQL DDL script. */

    The INSERTs via ADO were:

    INSERT INTO Table1 (nameData) VALUES ('KalimbaMain')
    INSERT INTO Table1 (nameData) VALUES ('Offerings')
    INSERT INTO Table1 (nameData) VALUES ('Contacts')
    INSERT INTO Table1 (nameData) VALUES ('LoyaltyDemo')
     
    of your data so we can try this ourselves? 
    ----- 
    news:4e9b01c356bc$f23a6720$gbl... 
    stored  
    in  

    Tom Guest

  13. #13

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    Per your suggestion I did the following in isql with
    success.
     

    What does that mean?
     [/ref]
    stored [/ref]
    in 
    >
    >The sp is correct.
    >
    >Try the following in a different database.
    >
    >David
    >
    >
    >CREATE TABLE table1 (
    > id int IDENTITY NOT NULL ,
    > nameData varchar(32)
    >)
    >GO
    >
    >
    >create PROCEDURE up_CheckName
    >nameIn varchar(32)
    >AS
    >DECLARE tableId int
    >
    >SELECT tableId = ID FROM Table1
    > WHERE nameData = nameIn
    >
    > IF ROWCOUNT = 0
    >BEGIN
    >INSERT INTO Table1 (Table1.nameData)
    > VALUES (nameIn)
    >SELECT tableId = IDENTITY
    >END
    >go
    >
    >exec up_CheckName 'someName'
    >exec up_CheckName 'someName'
    >
    >select * from table1
    >
    >
    >.
    >[/ref]
    Leonard Guest

  14. #14

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    I tried invoking the procedure from isql and it worked
    OK. I also ran it from an ASP.NET page and it worked.
    The only place it doesn't seem to work is in the Query
    yzer! That was the place I debug SPs. How can I deal
    with this?
     
    >
    >Can you please provide the DDL according to the second[/ref]
    paragraph in 
    >
    >Have you tried running the procedure in Query yzer[/ref]
    *without* the 
    Leonard Guest

  15. #15

    Default Re: Data From INSERT In Stored Procedure Does Not Appear In Table

    I will play with this. I am not fully aware of all the
    capabilities of QA. MOVING AHEAD! THANKS!
     [/ref]
    deal 
    >
    >Uh, make sure you're at the latest service pack, and if[/ref]
    that doesn't help, 
    Can't you just insert 
    doing for you in 
    Leonard Guest

  16. #16

    Default Data From INSERT In Stored Procedure Does Not Appear In Table


    I found my basic problem with INSERTs in the Query
    yzer debugger. When debugging there is checkbox with
    the caption, Auto roll back. It was checked. Therefore,
    any changes made to the database tables during the debug
    are rolled back at the end. This was may problem!

    In any case, the various comments were helpful for other
    things. Thanks.
     
    is 
    Leonard Guest

Similar Threads

  1. Stored Procedure and DB Table
    By the-african in forum Coldfusion Database Access
    Replies: 0
    Last Post: November 23rd, 04:56 PM
  2. Insert Recored and Stored Procedure
    By Speegs in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 6th, 06:24 PM
  3. ASP / Stored Procedure SQL Insert Help
    By Bill Kellaway in forum ASP
    Replies: 7
    Last Post: October 12th, 06:15 PM
  4. New to Stored Procedure my Insert SP is doubling
    By John in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 14th, 12:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139