troubleshooting Question

SQL Server stored proc encryption question - complex

Avatar of roger v
roger vFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
5 Comments2 Solutions374 ViewsLast Modified:
I'm trying to create a stored proc that basically does 2 things. It takes a ssn as parameter and does a select query. If the ssn is present, it returns the associated ssnid back. If ssn is not present I need to do an insert row and insert the ssn and also encrypt it. Then I do a select query to return the newly generated ssnid (auto increment) as an output param. I'm having trouble getting the syntax right and I've never done any sql encryption so I'm lost. How do I go about doing this? thnx,

- roger
@ssn char(10)
AS

BEGIN

 

      SET NOCOUNT ON

 

    -- Insert statements for procedure here

      DECLARE @ssnid int --this is the ssnid that needs to be outputted

 

      SELECT @ssnid = ssnid

      from  dbo.SSN

      where ssn = @ssn

END

 

if @ssnid IS NULL

      BEGIN

            insert into dbo.SSN(ssn,date_created,created_by)--this is where I need to encrypt ssn as well as attach zeros to the left if the ssn is less than 9 characters

            values(@ssn,getDate(),'blah')

 

            select @ssnid = ssnid

            from dbo.SSN

            where ssn = @ssn

 

            RETURN @ssnid

      END

else

      BEGIN

            RETURN @ssnid

      END

GO
ASKER CERTIFIED SOLUTION
Goodangel Matope
Software Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros