-
-------
create procedure sp_getKey( @key_name varchar(256), @key_count integer
, @first_key_value varchar(40) output
, @keys_allocated_count int = NULL output
, @data_type varchar(256)= NULL output
) with recompile
as
DECLARE
@err_msg nvarchar(255),
@err_num integer ,
@rows integer ,
@precision integer ,
@max_precision integer ,
@next_key_precision integer
SET @precision = 32
SET @keys_allocated_count = 0
if (@key_count is NULL or @key_count < 1)
begin
select @err_msg = 'Invalid key_count specified, key_count has to be
greater than zero'
select @err_num = 13002
goto error
end
begin tran
update keys
set last_key_value = CASE data_type
WHEN 'uniqueidentifier' THEN convert( varchar(36), newid())
ELSE convert( varchar(36) , convert( decimal( 32 , 0) , last_key_value )
+ @key_count )
END
where key_name = @key_name
select @err_num = @@error, @rows = @@rowcount
if @err_num <> 0
begin
select @err_msg = description, @err_num = 13002
from master.dbo.sysmessages
where error = @err_num
rollback tran
goto error
end
if @rows <> 1
begin
select @err_msg = 'No rows found', @err_num = 13002
rollback tran
goto error
end
select @first_key_value = CASE data_type
WHEN 'uniqueidentifier' THEN
last_key_value
ELSE
( right ( replicate('0',
max_precision)
+ convert(varchar(32),
( convert( decimal( 32
, 0) , last_key_value ) - @key_count + 1)
)
, max_precision
)
)
END
, @max_precision = max_precision
, @next_key_precision = CASE WHEN data_type <>
'uniqueidentifier'
THEN len( convert(varchar(32),
convert( decimal( 32 , 0) , last_key_value ) ) )
END
, @data_type = data_type
from keys
where key_name = @key_name
select @err_num = @@error
if @err_num <> 0
begin
select @err_msg = description, @err_num = 13002
from master.dbo.sysmessages
where error = @err_num
rollback tran
goto error
end
if @data_type <> 'uniqueidentifier'
begin
SET @keys_allocated_count = @key_count
if @next_key_precision > @max_precision
begin
select @err_msg = 'Maximum precision assigned to the key
(keys.max_precision) has been exceeded' , @err_num = 13002
rollback tran
goto error
end
end
else
begin
SET @keys_allocated_count = 1
end
commit tran
return 0
error:
raiserror @err_num @err_msg
return -1
grant execute on sp_getKey to videoEmail_admin
-------------------------------------------------------------------------