CREATE PROCEDURE [sysdba].[SLXNEWID](
@TABLE VARCHAR(64) ,
@ID VARCHAR(12) OUTPUT,
@USER VARCHAR(20) = 'SYSDBA',
@SYSID VARCHAR(64) = 'PRIMARY'
) AS
SET NOCOUNT ON
DECLARE @SITE CHAR(4)
DECLARE @VALUE CHAR(7)
DECLARE @CHR CHAR(37)
DECLARE @F INT
DECLARE @C CHAR
DECLARE @TYPE INT
DECLARE @IDFIELD VARCHAR(64)
DECLARE @SQL NVARCHAR(1000)
SELECT @SITE = SITECODE
FROM sysdba.SYSTEMINFO
WHERE SYSTEMINFOID = @SYSID
IF (@SITE IS NULL) BEGIN
RETURN 'WRONG SYSTEMINFOID!'
END
SELECT @VALUE = KEYVALUE,@TYPE=KEYTYPE
FROM sysdba.SITEKEYS
WHERE KEYDESC=@TABLE AND SITECODE=@SITE
IF (@VALUE IS NULL) BEGIN
SELECT @VALUE = KEYVALUE, @TYPE = KEYTYPE
FROM sysdba.SITEKEYS
WHERE KEYDESC = 'OTHER' AND SITECODE=@SITE
END
SET @CHR='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_'
SET @F=7
SET @VALUE=LEFT(@VALUE,6)+SUBSTRING(@CHR,CHARINDEX(RIGHT(@VALUE,1),@CHR)+1,1)
SET @F = CHARINDEX('_',@VALUE)
WHILE @F>0 BEGIN
SET @F=@F-1
IF (@F=0) BEGIN
RETURN 'NO MORE INDEXES!'
END
SET @VALUE=REPLACE(@VALUE,'_','0')
SET @VALUE=LEFT(@VALUE,@F-1)+SUBSTRING(@CHR,CHARINDEX(SUBSTRING(@VALUE,@F,1),@CHR)+1,1)+RIGHT(@VALUE,7-@F)
SET @F = CHARINDEX('_',@VALUE)
END
EXECUTE('UPDATE SYSDBA.SITEKEYS SET KEYVALUE = '''+@VALUE+''' WHERE SITECODE = '''+@SITE+''' AND KEYTYPE = '+@TYPE)
SELECT @IDFIELD=KEYFIELD
FROM sysdba.RESYNCTABLEDEFS
WHERE TABLENAME=@TABLE
IF @IDFIELD IS NOT NULL BEGIN
SET @ID = NULL
SET @ID=
CASE @TABLE
WHEN 'ACCOUNT' THEN 'A'
WHEN 'CONTACT' THEN 'C'
WHEN 'OPPORTUNITY' THEN 'O'
WHEN 'PRODUCT' THEN 'P'
WHEN 'ADDRESS' THEN 'a'
WHEN 'ASSOCIATION' THEN 'B'
WHEN 'ACTIVITY' THEN 'V'
WHEN 'HISTORY' THEN 'H'
WHEN 'EVENT' THEN 'E'
WHEN 'PROCESS' THEN 'P'
WHEN 'LEADSOURCE' THEN 'L'
WHEN 'COMPETITOR' THEN 'Z'
END
IF @ID IS NULL
BEGIN
SET @SQL=N'SELECT TOP 1 @ID = LEFT('+@IDFIELD+',1) FROM '+@USER+'.'+@TABLE+' ORDER BY '+@IDFIELD+' DESC'
EXECUTE sp_executesql @SQL,
N'@ID VARCHAR(12) OUT',
@ID=@ID OUT
END
END
IF (@ID IS NULL) BEGIN
SET @ID='Q'
END
SET @ID=RTRIM(@ID)+@SITE+@VALUE
GO
Using:
DECLARE @MYID VARCHAR(12)
exec sysdba.SLXNEWID 'ADDRESS', @MYID OUTPUT
If you have no remote clients or remote office you can use any unique id |