Posted by vassone on 09/14/05 12:28

Hi Simon,

I've taken your advice and changed the insert SP as below but I'm still not
getting my GUID back?

Any thoughts..

CREATE PROCEDURE heasvase.[usp_insert_address]

@ADDR_NAME_2 [char](70) = NULL,
@ADDR_NO_3 [char](10) = NULL,
@ADDR_ROAD_4 [char](50) = NULL,
@ADDR_DISTRICT_5 [char](50) = NULL,
@ADDR_TOWN_6 [char](50) = NULL,
@ADDR_BOROUGH_7 [char](50) = NULL,
@ADDR_PCODE_8 [char](12) = NULL,
@addr_id [char] OUTPUT

AS


INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]
(
[ADDR_ID],
[ADDR_NAME],
[ADDR_NO],
[ADDR_ROAD],
[ADDR_DISTRICT],
[ADDR_TOWN],
[ADDR_BOROUGH],
[ADDR_PCODE])


VALUES
(
NEWID(),
@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






"Simon Hayes" <sql@hayes.ch> wrote in message
news:1126687906.495874.71190@g44g2000cwa.googlegroups.com...
> You seem to be confusing two things - uniqueidentifer and identity.
> NEWID() generates a new uniqueidentifier value; SCOPE_IDENTITY()
> returns the last value generated by an IDENTITY column, which is
> usually an integer.
>
> I'm guessing (since you haven't provided a CREATE TABLE statement) that
> you're using NEWID() as a default on a column? If so, there is no
> function to retrieve the new value - typically you would use NEWID() in
> your proc to generate the value, then INSERT it; you can then return
> the new value as an output parameter (of data type uniqueidentifier,
> not integer).
>
> If this isn't helpful, or my guess is wrong, I suggest you post a
> CREATE TABLE script for your table, so that it's clear what data types,
> constraints etc you have. You should also clarify what you expect to
> get back from the procedure.
>
> Simon
>

[Back to original message]