Posted by Simon Hayes on 09/14/05 12:38

Try this:

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 uniqueidentifier OUTPUT
AS

set @addr_id = newid()

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

VALUES
(
@addr_id,
@ADDR_NAME_2,
@ADDR_NO_3,
@ADDR_ROAD_4,
@ADDR_DISTRICT_5,
@ADDR_TOWN_6,
@ADDR_BOROUGH_7,
@ADDR_PCODE_8)
GO

As per my previous post, SCOPE_IDENTITY() has nothing to do with GUIDs
and NEWID(). An IDENTITY column is an auto-incrementing numeric value,
which is usually implemented as an integer, and SCOPE_IDENTITY()
returns the last identity value generated in the current scope.

NEWID() on the other hand generates a binary GUID value of data type
uniqueidentifier. See "IDENTITY (Property)", SCOPE_IDENTITY(),
uniqueidentifier, NEWID() and CREATE TABLE in Books Online for more
information.

By the way, char with no length defaults to char(1), so your @addr_id
parameter wouldn't work correctly. You can use CAST() if you want to
return the new GUID as a character type.

Simon

[Back to original message]