Simple Unique Identity Value

[Richard Andrew x64]

I need a way to return an identity value for a SELECT statement that is guaranteed to be unique. So I wrote the following stored procedure:

CREATE PROCEDURE agsp_UniqueDocnum
@DOCUMENTTYPE AS NVARCHAR(15),
@ORDERID AS INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO ags\_UniqueDocnum 
(	CreationDate,
	DocumentType,
	OrderId )
VALUES
(	GETDATE(),
	@DOCUMENTTYPE,
	@ORDERID );


RETURN SCOPE\_IDENTITY();

END

The ags_UniqueDocnum table contains an Identity column that I'm using as the unique identifier. But when I try:

SELECT agsp_UniqueDocnum('TEST', 0)

It tells me that that's not the name of a FUNCTION. So I'm stuck in a catch 22. You can't use an INSERT inside a function, and you can't SELECT a stored procedure! How can I get a unique identifier within a SELECT statement? It must be all digits, no alpha, and no more than 16 characters in length.

The difficult we do right away... ...the impossible takes slightly longer.

[Richard Deeming]

You'll probably want to look into sequences, which is what SQL Server uses behind the scenes to implement identity columns. Sequence Numbers - SQL Server | Microsoft Learn[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[CHill60]

You can "redirect" the output from the stored procedure into a table variable or temporary table and then select from that (or join the results if the select is meant to be more complex) - a bit of a kludge but works e.g.

Declare @Temp Table ([Id] [int])
Insert @Temp Exec agsp_UniqueDocnum 'TEST', 0
Select * from @Temp;

[Richard Andrew x64]

Thank you. I'll check it out.

The difficult we do right away... ...the impossible takes slightly longer.

[Richard Andrew x64]

Thank you.

The difficult we do right away... ...the impossible takes slightly longer.