I have the following stored procedure that increments a key in a table
called sitekeys and then what I'd like to do is have it return the value as
I need to grab it with a DTS lookup. I have it working by doing a select
statement but was wondering if there is an easier way to just return the
value without the darn sql select at the end.





CREATE PROCEDURE sysdba.SLXID
(
@NewKey nvarchar(12) output,
@Table nvarchar(32),
@SiteCode nvarchar(4) = 'XXXX')--optional
AS
SET NOCOUNT ON
declare @Key nvarchar(12)
declare @Int int
declare @Count int
declare @Break bit
declare @KeyType int
select @KeyType = case upper(@Table)
when 'ACCOUNT' then 1
when 'OPPORTUNITY' then 2
when 'CONTACT' then 3
when 'ACTIVITY' then 5
when 'HISTORY' then 6
when 'EVENT' then 7
when 'USERNOTIFICATION' then 8
when 'ADDRESS' then 22
when 'TICKET' then 32
when 'ASSOCIATION' then 4
when 'PLUGIN' then 29
when 'PROCESS' then 9
when 'PICKLIST' then 30
else 25
end
if @KeyType not in (1, 2, 3, 4,5, 6, 7, 8, 22, 30)
set @KeyType = 25
set transaction isolation level serializable
begin transaction
if @SiteCode = 'XXXX'
begin
select @SiteCode = (select sitecode from systeminfo where systeminfoid =
'PRIMARY')
end
select @Key = KeyValue from sitekeys (updlock) where SiteCode = @SiteCode
and KeyType =

@KeyType
if @Key is null
begin
rollback
return -1001
end
set @Count = 0
set @Break = 0
while (@Count < 7)
begin
set @Int = ascii(substring(@Key, len(@Key) - @Count, 1))
if @Int = 90
begin
set @Int = 48
end
else
begin
if @Int = 57
set @Int = 65
else
set @Int = @Int + 1
set @Break = 1
end

set @Count = @Count + 1
set @Key = left(@Key, len(@Key) - @Count) + char(@Int) + right(@Key,
@Count - 1)
if @Break = 1
break
end
update sitekeys set KeyValue = @Key where sitecode = @SiteCode and keytype
= @KeyType
if @@rowcount = 0
begin
rollback
return -1002
end
commit transaction
--create new key
select @NewKey = case @KeyType
when 1 then 'A'
when 2 then 'O'
when 4 then 'B'
when 3 then 'C'
when 5 then 'V'
when 6 then 'H'
when 7 then 'E'
when 8 then 'n'
when 22 then 'a'
when 30 then 'k'
when 32 then 't'
when 4 then 'B'
when 29 then 'p'
when 9 then 'P'
else 'Q'
end
---set @NewKey = @NewKey + left(@SiteCode, 4) + @Key
----select @NewKey + left(@SiteCode ,4) + RTRIM(keyvalue) KeyValue from
sitekeys where select keytype=@KeyType
select RTrim(@NewKey) + RTrim(sitecode) + RTrim(@Key) as KeyValue from
systeminfo where systeminfoid = 'PRIMARY')
return 0
GO

Is this going to be done for every row of the Source as that is what will
happen when using a lookup?
If you only need it once to use in your Transform then I would assign the
value up front in an ExecuteSQL task to a Global Variable. I would then
reuse the Global Variable.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Gregor Anton" <nospamplease (AT) hotmail (DOT) com> wrote