NEED HELP WITH A QUERY 1

[bryant89]

I want to write an sql query that will essentially create a record in a table that has an id set as an autonumber. Then after the record is created (but within the same procedure) retrieve that autonumber. So like an insert and select all in one procedure. I tried to something like this but I cannot get it to work.

any help would be appreciated

 

[sndkick]

Try using the identity field in a stored procedure. i.e.

CREATE PROCEDURE dbo.sp_InsertAndGet

@val1 int,
@val2 int,
@val...

AS

DECLARE @itemID int

INSERT INTO tablename (val1, val2, ...)
VALUES ( @Val1, @Val2, ... )

SET @itemID = @@IDENTITY

SELECT @itemID newRecordID

I'm pretty sure that'll work Scott

 

[sndkick]

Try using the identity field in a stored procedure. i.e.

CREATE PROCEDURE dbo.sp_InsertAndGet

@val1 int,
@val2 int,
@val...

AS

DECLARE @itemID int

INSERT INTO tablename (val1, val2, ...)
VALUES ( @Val1, @Val2, ... )

SET @itemID = @@IDENTITY

SELECT @itemID newRecordID
GO

I'm pretty sure that'll work Scott

 

[bryant89]

Well I tried that out but I couldnt get it to work. So now I am trying to just get the insert and I cant even get that working.

my code looks like this:

Create Procedure stpCreateReturn


@copID int,
@copName varchar,
@copCity varchar,
@copProv char,
@copCountry varchar,
@copAddress varchar,
@copStatus varchar,
@copEstCost float


AS


INSERT INTO tblContProj (copID, copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copID, @copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)


return


Now copID is the autonumber so I dont know if I am supposed to leave that out of the insert and values parameters or not. when I execute the query I just leave the copID set to <DEFAULT>. All of these data types specified match the data types I specified in the table in SQL server so I am not sure why it doesnt work. The error I get is this

Running dbo.&quot;stpCreateReturn&quot; ( @copID = <DEFAULT>, @copName = test, @copCity = test, @copProv = te, @copCountry = test, @copAddress = test, @copStatus = <NULL>, @copEstCost = <NULL> ).


Application uses a value of the wrong type for the current operation.

No rows affected.
@RETURN_VALUE =
Finished running dbo.&quot;stpCreateReturn&quot;.

 

[sndkick]

If copID is an autonumber, do not include it in your insert statement. It will be automatically added. That could be your problem. Also make sure that in the table design that the values accepting <NULL> are set to allow nulls. Scott

 

[sndkick]

You may also have to declare a size for each of your varchar declarations, i.e.

@copName varchar(20),
@copCity varchar(20),
@copState varchar(2),
etc...

Make sure these sizes match those in the table definition. Scott

 

[bryant89]

yep they are set to accept nulls.
I also set everything except for copID (autonumber field)and copName to accept NULLS. Then I wrote a procedure to do this:

Alter Procedure stpCreateReturn

@copName varchar

AS

INSERT INTO tblContProj (copName)
VALUES (@copName)

return

Whenever I save the stored procedure it replaces the

CREATE Procedure stpCreateReturn
with
Alter Procedure stpCreateReturn.

I dont know if this has anything to do with it.
But I still get this error with this procedure


Running dbo.&quot;stpCreateReturn&quot; ( @copName = test ).

Application uses a value of the wrong type for the current operation.
No rows affected.
@RETURN_VALUE =
Finished running dbo.&quot;stpCreateReturn&quot;.

 

[bryant89]

ok I will try the sizes.
you beat me on the posts there.

 

[sndkick]

You also need quotes around strings that you are inputting... calling the stored procedure should look somethingl like this:

stpCreateReturn 'Bob', 'Los Angeles', 'CA', ... Scott

 

[bryant89]

Scott...
You the man

I got the inserts working now.

So how do I retrieve the id
I have done this

Alter Procedure stpCreateReturn

@copName varchar(70),
@copCity varchar(40),
@copProv char(2),
@copCountry varchar(30),
@copAddress varchar(70),
@copStatus varchar(70),
@copEstCost money


AS
DECLARE @copID int


INSERT INTO tblContProj (copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)

SET @copID = @@IDENTITY
SELECT @copID copID

return

So how do I retrieve the @copID value. Do I need to create a recordset on my asp page that has one of the parameters set to @copID?
Because If I just execute the query in interdev the select doesnt return any records

 

[bryant89]

This is what I get for results.. It still inserts ok but nothing is selected.

Running dbo.&quot;stpCreateReturn&quot; ( @copName = test, @copCity = test, @copProv = te, @copCountry = test, @copAddress = test, @copStatus = test, @copEstCost = <NULL> ).

Finished running dbo.&quot;stpCreateReturn&quot;.


It should also have something like this:

Running dbo.&quot;stpContProj&quot; ( @copID = 41 ).

copID
--------------------
41
(1 row(s) returned)
No more results.
@RETURN_VALUE = 0
Finished running dbo.&quot;stpContProj&quot;.

 

[sndkick]

You will need a recordset to retrieve the ID for your ASP page. But it should be pulling up when you execute the query. I've never run querys from InterDev, I usually just open up the SQL Server Enterprise Manager and do it all from there. See if the recorset will return it, it should. Maybe Interdev pulls it out a little strange. Or try changing the select statement to

SELECT @copID AS copID

Maybe Interdev needs the &quot;AS&quot; in there. I'm not sure Scott

 

[bryant89]

I dont know how to execute a stored procedure within enterprise manager. Although I dont think this will make any difference but I want to try it. I right clicked on the stored procedure in SEM (Server Enterprise Manager) but there is nothing that allows me to execute the procedure. My sql statement is now this


Alter Procedure stpCreateReturn

@copName varchar(70),
@copCity varchar(40),
@copProv char(2),
@copCountry varchar(30),
@copAddress varchar(70),
@copStatus varchar(70),
@copEstCost money


AS
DECLARE @copID int


INSERT INTO tblContProj (copName, copCity, copProv, copCountry, copAddress, copStatus, copEstCost)
VALUES (@copName, @copCity, @copProv, @copCountry, @copAddress, @copStatus, @copEstCost)

SET @copID = @@IDENTITY
SELECT @copID as copID

return



Still a no go though.

 

[sndkick]

I'm not quite sure why it's not returning anything, as far as I can tell you've done everything correctly. Maybe lose the &quot;return&quot; at the bottom, but i think even that is ok, i just never use it.

In SEM to test that your query works, if you double click the stored procedure it opens the code. There is a button in the bottom left that says &quot;Check Syntax&quot;. This usually gives pretty good messages about the stored procedure syntax. Or to run the statement try the following:

In SEM right click on any table, and select &quot;Open Table - Return All Rows&quot;. This displays all data for that table. If you then click on the &quot;SQL&quot; button on the top toolbar it gives you some space to type in sql queries. This is where i test my stored procedures. You type in the query and then click the ! (exclamation) button on the top toolbar. This executes the statement. So you would do something like this:

stpCreateReturn 'copname', 'copcity', 'copprov', etc...

and then click the execute button

The only problem with this is that it doesn't give you very good error messages. If something is wrong, it just doesn't return anything.

You could try those for now. I'll keep thinking Scott

 

[bryant89]

k sounds good Scott.
I thought about trying something like this:

select copID from tblContProj
Where copName=@copName and copCity=@copCity............

 

[bryant89]

What exactly does the @@IDENTITY do. I do not understand this. Is there an article I can read

 

[sndkick]

I'm sure there are articles out there. @@IDENTITY holds the identity field of the last record added. So it would grab the ID of the record you just added. Now that I think about it, you may not have set the &quot;copID&quot; field as an identity field. Check to see if that has been done. If it has already, maybe copID has to be set as the Primary Key in order to be returned as the identity. Try checking those. Scott

 

[bryant89]

copID is the primary key and the identity is set you yes

the column properties are as follows

Identity yes
Identity Seed 1
Identity Increment 1

K I get it what the @@IDENTITY Field is supposed to do.
Should I try selecting from the table

I have this right now

SET @copID = @@IDENTITY
Select @copID copID

but I thought I would try

Select @copID copID from tblContProj

 

[sndkick]

I'm not sure what else to tell ya. I just created a sample stored procedure that inserts and pulls the ID, just like yours and it returned the id. Here is my exact SP:

CREATE PROCEDURE sp_InsertPartInstance

@intPartID int,
@intNsnInstanceID int

AS

DECLARE @intPartInstanceID int

INSERT INTO tbl_part_instance (part_id, nsn_instance_id)
VALUES (@intPartID, @intNsnInstanceID)

SET @intPartInstanceID = @@IDENTITY

SELECT @intPartInstanceID as part_instance_id
GO
Scott

 

[bryant89]

K I forgot the word GO but when I try to put it in I get an error. ADO error.

So I am not sure.

the: part_instance_id is the field name in your table right?