Very Computer

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?

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?

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?

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

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?

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

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?

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.

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?

>.

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.

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.

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.

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

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.

>.

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

Very Computer

Data From INSERT In Stored Procedure Does Not Appear In Table