Posted by Simon Hayes on 10/16/97 11:26

vassone wrote:
> Dear All, I read through all the post and flipped through the books but I
> still can't find the answer to my problem.
>
> I'm inserting a new record via a stored procedure and want to return the id
> via scope_identity, which I thought would be preety straight forward.
>
> The code I'm using is below and this keeps giving me "Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available. No
> work was done."
>
> How do I pick the returning id value and could anyone see were I'm going
> wrong below.
>
> Many thanks for any help you can offer.
>
> CREATE PROCEDURE [sp_insert_address]
>
>
> @ADDR_NAME_2 [char](70),
> @ADDR_NO_3 [char](10),
> @ADDR_ROAD_4 [char](50),
> @ADDR_DISTRICT_5 [char](50),
> @ADDR_TOWN_6 [char](50),
> @ADDR_BOROUGH_7 [char](50),
> @ADDR_PCODE_8 [char](12),
> @addr_id [int] OUTPUT
>
>
> AS INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
> (
> [ADDR_NAME],
> [ADDR_NO],
> [ADDR_ROAD],
> [ADDR_DISTRICT],
> [ADDR_TOWN],
> [ADDR_BOROUGH],
> [ADDR_PCODE])
>
>
> VALUES
> (
> @ADDR_NAME_2,
> @ADDR_NO_3,
> @ADDR_ROAD_4,
> @ADDR_DISTRICT_5,
> @ADDR_TOWN_6,
> @ADDR_BOROUGH_7,
> @ADDR_PCODE_8)
>
> set @addr_id = scope_identity()
> GO
>
>

First, don't use the sp_ prefix for your procedure - it's reserved for
system procs, and MSSQL will look for it in the master database before
the current database.

Apart from that, your code looks basically correct - are you asking how
to use output parameters? If so, then this should work:

declare @new_id int

exec insert_address
@addr_name2 = 'Something',
-- etc.
@addr_id = @new_id OUTPUT

select @new_id

Simon

[Back to original message]