Stored Proc Auto Increment

[igrummett]

I have a stored proc that inserts data into the resource table. The key to the table is resource_id. The stored proc is set up to pass all but the resource_id as this is automatically generated.

Initially I had a problem where the auto increment was not working as it could not set the resource_id to null. I have rectified this by by setting the Identity to Yes. The stored proc now inserts to the table buy when I select from the table 'where resource_id = 2' it does not return any rows yet it exists in the database. Apart from the resource_id all other data is the same.

I cant seem to solve this issue. Any ideas?

 

[hmckillop]

If what you are trying to do is to return the value in the identity at same time as inserting a row, use an output parameter in your stored procs and then in this field return the valuye of function scope_identity()

"I'm living so far beyond my income that we may almost be said to be living apart

 

[JackVam]

Is the INSERT and SELECT 2 separate statements , are they part of a separate transaction. ?
Where are you trying to return the recordset , is it back to Query Analyzer or are you using some other client?
Could you post your code?

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[igrummett]

Hi, if I run the following query:-

Yes the insert and select are separate stored procs. The insert appears to work OK.

If i run the following SQL :

SELECT resource.* FROM resource

I get the following results: -

Resource_id EntityID Name Email

1 1 Test w@gm.com

2 1 Test1 test1@gmail.com

3 1 Test2 test2@gmail.com

Therefore there are 3 valid rows on the table. When I run the following Stored Proc it only works for the Resource-id= 1 . If i enter Resource_id 2 or 3 it returns no rows: -

USE [timesheetportal]
GO

/****** Object: StoredProcedure [dbo].[usp_view_resource] Script Date: 01/14/2008 22:50:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

CREATE PROCEDURE [dbo].[usp_view_resource]

-- =============================================

-- Variables

-- =============================================

@resource_id int
AS SELECT resource.resource_id, resource.entity_id, resource.name, resource.email

FROM resource

WHERE resource.resource_id = @resource_id

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

END

I find it very strange this works for the 1st record in the table but not the others.

Any help would be great.

 

[igrummett]

Hi - I have managed to solve this so thanks for your help.