Allocating Random "PassCode" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Allocating Random "PassCode"

Author

Topic

Kristen
Test

22859 Posts

Posted - 2014-03-24 : 13:41:25

Hi Folks :)

I have a column in a table called ShortCode. I need to allocate a random 4 character alpha-numeric code to it.

A ShortCode column appears in 3 different tables. The code must be unique across all three tables.

Ordinarily I would put the code in a Trigger and update the underlying table WHERE [Inserted].ShortCode IS NULL

I'm thinking I would prefer to call a Stored Procedure to centralised the PassCode generation code (in case a bug is found). That would mean


IF EXISTS (SELECT * FROM INSERTED AS I WHERE I.ShortCode IS NULL)
    EXEC MySProc

but MySProc is going to then have to update all ShortCode that are NULL in all three tables (no access available to [Inserted] as it is out of scope). And that update will cause recursion back to the Trigger

So at that point I think I should hard-wire the code into the Trigger ... but then I have to duplicate that code in all 3 triggers, one for each table, and chances are anyone fixing a bug will not realise to update the other 2 ]:(].

Or ... is there a better way please?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-24 : 14:06:45

You could use a function that assigned the code; the same function could be the default value for columns in all 3 tables.

Kristen
Test

22859 Posts

Posted - 2014-03-24 : 18:45:28

I did try that, but the code that generates the (random) PassCode uses NewID() - which, sadly, rules out a UDF. But other than that it would indeed be the easiest way.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-24 : 22:30:34

you can pass NEWID() into the function

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2014-03-25 : 08:11:56

My plan was that the Function would return a Table of multiple values, sufficient for all the row(s) that have a NULL PassCode.

I would then only have to call the Function a second time if one/several of the values it returned the first time were already in use (or I could take care of that in the Function itself)

Perhaps I should just have a table of "available values" that are deleted as they are used, and more are added "occasionally". There would be some risk that a large batch of new records were added, and the PassCode table ran out of values. I suppose in such a scenario the Trigger could call an Sproc to top-up the values.

Is there any mileage in this, or am I making it too complicated?

If Function just returns a single value (and I pass it NewID() as a parameter) would it be very inefficient if I had to add, say, 1,000 PassCodes in a loop (within the Trigger)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-25 : 11:10:40

Hmm, using a standard DEFAULT constraint would require a scalar function, as we've noted.

But if you used a trigger to assign the value, you could use an inline-table-valued function, which should allow the use of NEWID() and be fast.

I'm not sure NEWID() guarantees you 4 unique chars, but if it works for your needs in this case, that's all that matters.

uberman
Posting Yak Master

159 Posts

Posted - 2014-03-25 : 12:54:08

FYI, you can fudge a newid in a function by using view...

create view wibble as select newid() as wobble
go

create function wibbler()
returns varchar(40)
begin

declare @test as uniqueidentifier = (select wobble from wibble)
return cast(@test as varchar(36)) + 'blah';

end
go

select dbo.wibbler()
go
select dbo.wibbler()
go
select dbo.wibbler()
go

Kristen
Test

22859 Posts

Posted - 2014-03-25 : 15:59:17

quote:
Originally posted by uberman

FYI, you can fudge a newid in a function by using view...

Hahaha ... Excellent!

Makes a mockery of the whole scalar requirement

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-25 : 17:24:15

Assuming the 4-character code needs to be unique; Would it make more sense to create a table specific for this purpose with an IDENTITY column? And, perhaps, add a derived column that produces a code based on the IDENTITY Column?

Kristen
Test

22859 Posts

Posted - 2014-03-26 : 05:19:30

I woke up this morning with the thought, based on uberman's post, that I could move the code, in its entirety, from my proposed function to a VIEW. I'll experiment with that this morning and report back. That would be similar to your solution Lamprey, but without the need to actually "materialise" any intermediate data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources