November 4, 2005 at 1:07 pm
...a SQL/T-SQL newbie question.
I'm struggling with a SELECT that I'm using in a VIEW... There's something about the CONVERT() function or expressions that I'm not getting.
Here's the essence of the SELECT I'm using in the view:
SELECT
ID, CONVERT(varchar(11), dbo.fn_columnDef('Employees', 'SSN')) AS SSN... FROM dbo.Employees
If the function (dbo.fn_columnDef) used in the CONVERT expression returns the value 'SSN', I want the SELECT to be performed as:
SELECT ID, CONVERT(varchar(11), SSN) AS SSN... FROM dbo.Employees
If the function used as the CONVERT expression returns 'DecryptByKeyAutoCert(Cert_ID('cert_sk_Employees'), NULL, SSN)', I want the SELECT to be performed as:
SELECT ID, CONVERT(varchar(11), DecryptByKeyAutoCert(Cert_ID('cert_sk_EmpSSN'), NULL, SSN))
AS SSN... FROM dbo.Employees
Right now, the result set is like this:
ID SSN
------ -----------
1 SSN
2 SSN
3 SSN
Instead of the value 'SSN' being returned for the SSN column on every row, I'm expecting the actual value stored in SSN field for each record to be returned... such as:
ID SSN
------ -----------
1 123-45-6789
2 234-56-7890
3 345-67-8901
I'm pretty sure I'm missing something easy to make this work...
TIA,
Tom
November 4, 2005 at 1:43 pm
You are sending the string SSN with the statement "SELECT ID, CONVERT(varchar(11), dbo.fn_columnDef('Employees', 'SSN')) AS SSN... FROM dbo.Employees"
Try it with the sending the column SSN (no single quotes) instead "SELECT ID, CONVERT(varchar(11), dbo.fn_columnDef('Employees', SSN)) AS SSN... FROM dbo.Employees"
November 4, 2005 at 3:08 pm
Ah... I see.
TOL... The SSN column could be clear-text (varchar) or cipher-text (varbinary)... So, if I can figure out how to determine the datatype of the SSN column, I think I can refactor the function to accept the cipher-text and return the clear-text.
Thanks,
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply