Data From INSERT In Stored Procedure Does Not Appear In Table

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Leonar » Thu, 31 Jul 2003 16:54:30



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

AS


        DECLARE table_cursor CURSOR FOR
                SELECT Table1.ID FROM dbo.Table1        

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

        IF Cursor_Status('local', 'table_cursor') <= 0
        BEGIN
                INSERT INTO dbo.Table1 (Table1.nameData)


        END
        CLOSE table_cursor

In the query analyzer de* I execute the procedure



However, the new record is not present in Table1.  What is
going on?  Why?  How do I fix it?

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by David Brown » Thu, 31 Jul 2003 17:00:33



> 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

> AS


> DECLARE table_cursor CURSOR FOR
> SELECT Table1.ID FROM dbo.Table1

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

> IF Cursor_Status('local', 'table_cursor') <= 0
> BEGIN
> INSERT INTO dbo.Table1 (Table1.nameData)


> END
> CLOSE table_cursor

> In the query analyzer de* I execute the procedure



> However, the new record is not present in Table1.  What is
> going on?  Why?  How do I fix it?


 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Tom Morea » Thu, 31 Jul 2003 17:03:03


Get rid of the cursor:


AS




BEGIN


END


go

--
Tom

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

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:


AS


DECLARE table_cursor CURSOR FOR
SELECT Table1.ID FROM dbo.Table1

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

IF Cursor_Status('local', 'table_cursor') <= 0
BEGIN
INSERT INTO dbo.Table1 (Table1.nameData)


END
CLOSE table_cursor

In the query analyzer de* I execute the procedure



However, the new record is not present in Table1.  What is
going on?  Why?  How do I fix it?

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by David Brown » Thu, 31 Jul 2003 17:07:11



> 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

> AS


> DECLARE table_cursor CURSOR FOR
> SELECT Table1.ID FROM dbo.Table1

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

> IF Cursor_Status('local', 'table_cursor') <= 0
> BEGIN
> INSERT INTO dbo.Table1 (Table1.nameData)


> END
> CLOSE table_cursor

> In the query analyzer de* I execute the procedure



> However, the new record is not present in Table1.  What is
> going on?  Why?  How do I fix it?

Like this


AS


DECLARE table_cursor CURSOR FOR
SELECT Table1.ID FROM dbo.Table1

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


BEGIN
INSERT INTO dbo.Table1 (Table1.nameData)


END
CLOSE table_cursor
deallocate table_cursor
go

But better would be


AS

 if not exists (
    SELECT Table1.ID FROM dbo.Table1

    )
 BEGIN
  INSERT INTO dbo.Table1 (Table1.nameData)


 END

go

david

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Aaron Bertrand - MV » Thu, 31 Jul 2003 17:10:31


Why are you using a cursor and cursor status?  How about:




Or


BEGIN


END
ELSE


> 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

> AS


> DECLARE table_cursor CURSOR FOR
> SELECT Table1.ID FROM dbo.Table1

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

> IF Cursor_Status('local', 'table_cursor') <= 0
> BEGIN
> INSERT INTO dbo.Table1 (Table1.nameData)


> END
> CLOSE table_cursor

> In the query analyzer de* I execute the procedure



> However, the new record is not present in Table1.  What is
> going on?  Why?  How do I fix it?

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Leonar » Thu, 31 Jul 2003 18:01:31


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.

>-----Original Message-----
>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

>AS


>    DECLARE table_cursor CURSOR FOR
>            SELECT Table1.ID FROM dbo.Table1        

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

>    IF Cursor_Status('local', 'table_cursor') <= 0
>    BEGIN
>            INSERT INTO dbo.Table1 (Table1.nameData)


>    END
>    CLOSE table_cursor

>In the query analyzer de* I execute the procedure



>However, the new record is not present in Table1.  What
is
>going on?  Why?  How do I fix it?
>.

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Leonar » Thu, 31 Jul 2003 18:22:34


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

ALTER PROCEDURE up_CheckName

AS




        BEGIN
                INSERT INTO dbo.Table1 (Table1.nameData)


        END

Table1.

>-----Original Message-----
>Get rid of the cursor:

>ALTER PROCEDURE up_CheckName

>AS




>BEGIN


>END


>go

>--
>Tom

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




- Show quoted text -

>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

>AS


>DECLARE table_cursor CURSOR FOR
>SELECT Table1.ID FROM dbo.Table1

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

>IF Cursor_Status('local', 'table_cursor') <= 0
>BEGIN
>INSERT INTO dbo.Table1 (Table1.nameData)


>END
>CLOSE table_cursor

>In the query analyzer de* I execute the procedure



>However, the new record is not present in Table1.  What
is
>going on?  Why?  How do I fix it?

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Aaron Bertrand - MV » Thu, 31 Jul 2003 18:25:27


Does Table1 have a trigger, or a unique constraint / index, that might be
being violated?  Where are you running this procedure from?  Can you post
your table structure, sample data, and a "new" name that demonstrates the
problem?


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

> ALTER PROCEDURE up_CheckName

> AS




> BEGIN
> INSERT INTO dbo.Table1 (Table1.nameData)


> END

> Table1.

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Leonar » Thu, 31 Jul 2003 18:55:12


I tried:

IF NOT EXISTS

BEGIN


END


in the table.

>-----Original Message-----
>Why are you using a cursor and cursor status?  How about:


>    WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE nameData


>Or


>BEGIN


>END
>ELSE



>> 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

>> AS


>> DECLARE table_cursor CURSOR FOR
>> SELECT Table1.ID FROM dbo.Table1

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

>> IF Cursor_Status('local', 'table_cursor') <= 0
>> BEGIN
>> INSERT INTO dbo.Table1 (Table1.nameData)


>> END
>> CLOSE table_cursor

>> In the query analyzer de* I execute the procedure



>> However, the new record is not present in Table1.  What
is
>> going on?  Why?  How do I fix it?

>.

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Aaron Bertrand - MV » Thu, 31 Jul 2003 18:58:18


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


> I tried:

> IF NOT EXISTS

> BEGIN


> END


> in the table.

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Leonar » Thu, 31 Jul 2003 19:06:41


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

AS




        BEGIN
                INSERT INTO dbo.Table1 (Table1.nameData)


        END

Table1.

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Tom Morea » Thu, 31 Jul 2003 19:11:09


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

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.


AS




BEGIN
INSERT INTO dbo.Table1 (Table1.nameData)


END

Table1.

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by David Brown » Thu, 31 Jul 2003 19:13:59



> 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

> AS




> BEGIN
> INSERT INTO dbo.Table1 (Table1.nameData)


> END

> Table1.

The sp is correct.

Try the following in a different database.

David

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


AS




BEGIN
INSERT INTO Table1 (Table1.nameData)


END
go

exec up_CheckName 'someName'
exec up_CheckName 'someName'

select * from table1

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Leonar » Thu, 31 Jul 2003 19:31:34


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 analyzer

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.

Quote:>-----Original Message-----
>Does Table1 have a trigger, or a unique constraint /

index, that might be
>being violated?  Where are you running this procedure
from?  Can you post
>your table structure, sample data, and a "new" name that
demonstrates the
>problem?



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

>> ALTER PROCEDURE up_CheckName

>> AS




>> BEGIN
>> INSERT INTO dbo.Table1 (Table1.nameData)


>> END

in
>> Table1.

>.

 
 
 

Data From INSERT In Stored Procedure Does Not Appear In Table

Post by Aaron Bertrand - MV » Thu, 31 Jul 2003 19:38:41


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

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

Quote:> I am running the procedure in the query analyzer
> de*.

Have you tried running the procedure in Query Analyzer *without* the
de*?

A