CREATE FUNCTION [dbo].[f_createUniqueID] 
(
	-- Add the parameters for the function here
	@tableName nvarchar(50) = null,
	@idField nvarchar(50) = null
)
RETURNS nchar(10)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @sql nvarchar(max)
			
	SET @sql = N'

	DECLARE @refId nchar(10)

	SELECT TOP 1 @refId=' + @idField + ' FROM ' + @tableName + ' ORDER BY NEWID()

	SET @refId = REPLACE(@refId,''1'',''2'')
	SET @refId = REPLACE(@refId,''3'',''4'')
	SET @refId = REPLACE(@refId,''5'',''6'')
	SET @refId = REPLACE(@refId,''7'',''8'')
	SET @refId = REPLACE(@refId,''9'',''0'')
	SET @refId = REPLACE(@refId,''A'',''B'')
	SET @refId = REPLACE(@refId,''C'',''D'')
	SET @refId = REPLACE(@refId,''E'',''F'')

	IF EXISTS (SELECT ' + @idField + ' FROM ' + @tableName + ' WHERE ' + @idField + '=@refId)

		BEGIN
			
			RETURN EXEC f_createUniqueID ' + @tableName + ', ' + @idField + '
		
END

ELSE

 RETURN @refId '


	EXECUTE sp_executesql @sql

	RETURN ''

END

quote:

---

Originally posted by X002548

use IDENTITY() and be done with it?



Brett

8-)

---

quote:

---

Originally posted by X002548

OK, please explain what you mean by a "unique" ID



Brett

---

SELECT LEFT(REPLACE(NEWID(), '-', ''), 10)

quote:

---

Originally posted by Lamprey

Are there any rules on what it ID looks like? Like the first five digits are numbers and the last five are alpha.? Can the digits be 0-9 and or A-Z?

Try this:

SELECT LEFT(REPLACE(NEWID(), '-', ''), 10)

---

quote:

---

Originally posted by X002548

ok, but how do you know if it's unique even after you generate it?

and what if 2 threads at the same time generate the unique id, AND both check to see that it is unique at the time, then try to insert the new row with the new "unique" id

What now

(Oh, I know...it's the old, "That'll never happen" response)

If it can, it will, and it will be a mystery to you when it does

---

quote:

---

Originally posted by akashenk
It is HIGHLY unlikely

---

quote:

---

Originally posted by X002548

quote:

---

Originally posted by akashenk
It is HIGHLY unlikely

---

See

OK, so you're stuck

to be simple is 9,999,999,999 unique id's enough?

Yes (if not take 999,999,999 and make the billionth digit an alpha, so you can have basically 26 billion unique ids)

OK, Lets start with

0000000000

Next

0000000001

Next

0000000002

So, my question to you is...are we just trying to be cute with this alpha numeric nonsense?

Unique is unique, no?



Brett

8-)

---

Alter PROCEDURE [dbo].[p_createId] 
	-- Add the parameters for the stored procedure here
	@tableName nvarchar(50) = null,
	@idField nvarchar(50) = null

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


		DECLARE @sql nvarchar(max)

		SET @sql = N'

			DECLARE @id nchar(10)

			SELECT @id = LEFT(REPLACE(NEWID(), ''-'', ''''), 10)

			IF EXISTS (SELECT ' + @idField + ' FROM ' + @tableName + ' WHERE ' + @idField + ' = @id)

				BEGIN
					EXEC p_createUniqueID ' + @tableName + ', ' + @idField + '
				END
			
			ELSE

				BEGIN
					SELECT @id
				END

		'

		EXECUTE sp_executesql @sql	
			

END

quote:

---

Originally posted by Lamprey

I'd probably use an OUTPUT parameter. Also, I'm not sure I'm following your proc. (may it's a cut-n-paste thing). But, Are you making a recursive call? If so, I'd change that to an iterative solution (WHILE loop) so you don't blow the stack looking for a non-existant ID.

---

quote:

---

Originally posted by Lamprey

And yes there is a difference between WHILE and IF. I'm going to leave it at that for now. If anyone else wants to tackle that question, feel free.

You might take a look in BOL for sp_executesql. That will let you return an OUTPUT parameter from a dynamic sql call. Also, if I have some time I can see if I can put together one possible way to code your stored proc.

---

CREATE PROCEDURE [dbo].[p_createUniqueID]
	@tableName nvarchar(50) = null,
	@idField nvarchar(50) = null,
	@newID nchar(10) OUTPUT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		DECLARE @sql nvarchar(max),
				@params nvarchar(max)
		
		SET @sql = N'

			SELECT @id = LEFT(REPLACE(NEWID(), ''-'', ''''), 10)

			WHILE (EXISTS (SELECT ' + @idField + ' FROM ' + @tableName + ' WHERE ' + @idField + ' = @id))

				BEGIN
					EXEC p_createUniqueID ' + @tableName + ', ' + @idField + ',  @id
				END
'
		SET @params = N'@id nchar(10) OUTPUT'

		EXECUTE sp_executesql @sql,	
							@params, 
							@id=@newId OUTPUT	

END

Alter PROCEDURE [dbo].[p_createId] 
	-- Add the parameters for the stored procedure here
DECLARE	@tableName nvarchar(50) ,
	@idField nvarchar(50) ,
	@NewID NCHAR(10) OUTPUT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	DECLARE @sql NVARCHAR(4000)
	DECLARE @id NCHAR(10)
	DECLARE @RowCount INT

	-- Build dynamic query
	SET @sql = 'SELECT @cnt = 1 FROM ' + @tableName + ' WHERE ' + @idField + ' = @id'

	-- Prime the loop
	SET @RoweCount = 1 -- Get into the loop

	WHILE @RowCount IS NOT NULL -- Duplicate found
	BEGIN
		
		-- Gen new id and check
		SET @id = LEFT(REPLACE(NEWID(), '-', ''), 10)
		
		EXEC sp_executesql @sql, N'@id NCHAR(10), @cnt INT OUTPUT', @id, @RowCount OUTPUT

	END
	
	SET @NewID = @ID		

END