December 17, 2007 at 12:53 pm
I started to put together (what I naively thought would be) a simple way to translate social security numbers (SSNs or SSNOs) to UIDs (unique, constant, but non-significant idenfitiers). One of my design requirements was accept an input list and return an output list after first populating a master list with any previously undefined SSNs.
The first things I was reminded of as I tried a couple of implementations, were:
1. User defined functions cannot do table inserts/updates.
2. Stored procedures cannot take table variables (or arrays) as arguments.
So my next attempts were to create a stored procedure that accepts a temp table (with a # prefix) as an input list of social security numbers, and returns a table of SSNs and corresponding UIDs. As I was trying to test this approach, I realized a couple of additional things:
3. The #temp tables can be referenced only by processes with the same process id (@@SPID)
4. Whenever you use SSMS to create a new query, that query is given a new and different @@SPID. Hence, when a naive user (like me) creates a quick query to inspect the contents of his #temp table, or tries to delete it, or tries to rerun the original query to create it, confusion is likely to set in quickly.
5. SQL Server restricts the names of #temp tables to 116 bytes, rather than the normal table limit of 128 bytes (not that I cared), but
6. The 11-character name appended to a #table in TempDB is not = the @@SPID of the creator. (Anybody know how to translate one to the other?)
At any rate, I finally built a query that (sort of) meets my needs:
/*
Insert list of SSNs to be converted into temp table #SSNIN
Temp table #SSNIN serves to pass SSN arguments to procedure
UIDFromSSNIN which returns a table of SSNs and UIDs.
*/
-- Initialize temp table #SSNIN to pass SSNs
--EXEC UIDStart
/* UIDStart dosen't work, even though it executes with the
callers (this) @@SPID. The code in the
procedure matches the following lines to (optionally) delete
and re-create the temp table #SSIN, but when #SSNIN is created
within procedure UIDStart, the INSERT into #SSNIN temp table
fails with "Invalid object name '#SSNIN' "
*/
if Object_Id('tempdb..#SSNIN') is not null
begin
drop table #SSNIN
print 'Dropped ' + cast(@@SPID as varchar(15)) -- debug
end
-- (Re)create temp table #SSNIN
create table #SSNIN(
ssn int not null
)
print 'Created ' + cast(@@SPID as varchar(15)) -- debug
insert into #SSNIN (SSN)
-- Begin query to populate the input table with list of SSNs
select TOP (6)
ssno
from hr_joined.dbo.[Joined All Employees]
-- END query to populate the input table
--Return input SSNs with corresponding UIDs
exec dbo.UIDFromSSNIN --Lookup after inserting prevously un-encountered SSNs
This raised some additional questions:
7. The procedure UIDStart, as noted in the comments, does not create a table (#SSNIN) that is useable later in the calling procedure. But it does execute using the same @@SPID as the caller, so I don't know why not!)
8. (Not expecting an answer) Why do end users think that it will take 11 characters to substitute for a 9-character SSN? And why are they offended when a list of non-significant code numbers starts with 1. (I refused to start at 10000000000 just to quiet them!)
All in all, I am unhappy with my solution, which raises additional questions for those of you who know better:
9. What approach might work better. (The input table strikes me as an ugly kludge!)
10. Why can't a procedure take a table variable argument?
11. Why can't a UDF do inserts?
12. MS Excel does a wonderful job of getting SQL Server output through several approaches (external data, external query, external pivot table source) why can't it send input data (other than scalar parameters) to the server (e.g. a range in a parameter)?
December 17, 2007 at 1:11 pm
Have you considered global temp tables (with "##" prefix) ? They can be accessed across SPIDs.
December 17, 2007 at 1:20 pm
My fear in using global ##temp tables was that multiple users could trip over each over. Is that unfounded?
December 17, 2007 at 1:30 pm
Thoughts - see if any of these "stick":
- instead of continuously creating #temp tables, what about using a "permanent" temp table? Meaning - dump all feeds into one single working temp table. Something like:
create table WorkingSSN (SSN INT, ProcID bigint,UUID int)
create clustered index pk_workingSSN on workingSSN(ssn,procid)
You can then use something like this to get your results
declare @processID bigint
select @processID=cast(replace(replace(replace(convert(char,getdate(),120),'-',''),':',''),' ','') as bigint)*100+@@spid
--do your insert
insert workingSSN (ssn, procID)
select ssno,@processID
from hr_joined.dbo.[Joined All Employees]
--insert the records into master table
--note - I'd do this right here, but if you don't want to - rebuild the exec dbo.UIDFromSSNIN to take @processID
insert myMasterTable (SSN)
select SSN
from
workingSSN w
where procID=@processID
and not exists (select [UID] from myMasterTable m where w.ssn=MyMasterTable.ssn)
update w
set UUID=m.[uid]
from
workingSSN w
inner join myMasterTable m on w.ssn=MyMasterTable.ssn
where w.procID=@processID
--return your results
select w.ssn,w.uuid
from workingSSN w
where w.procID=@processID
--do what you wish with the results
--do some more stuff with the results
--now clean up
delete from WorkingSSN where procid=@processID
You could avoid the update, and just do the direct join if you wish.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 17, 2007 at 1:32 pm
Jim Russell (12/17/2007)
My fear in using global ##temp tables was that multiple users could trip over each over. Is that unfounded?
Its not absolutely unfounded, but depending on your environment it is probably unlikely. If you are willing to use dynamic sql, you can avoid any chance by appending something to the table name like ##temp_exacttimecreated
You could avoid dynamic sql by having the procedure check for the existence of that temp table first and refusing to run or waiting until there is not one in a queue type fashion. Of course, that runs into its own problem of slowing people down.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 18, 2007 at 11:51 am
Thanks Matt, for taking the time to reply with such a complete example. I studied and learned from your approach, but I'm not sure what is to be gained v. just using a temp table. Speed? Storage? Capacity?
December 18, 2007 at 11:59 am
It was to give you an easy way to pass info between calling procedures. Because it's permanent - there's no visibility issue. Also - because it's indexed, the linking ought to be faster.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply