I have encryted field in my table. To decrypt the field i have to use the store procedure. I would like to decryt the field using view. If its possible can i call store procedure in view?
----Creating view, but i get "Incorrect syntax near the keyword 'OPEN'."
Create View User_SSN
As
OPEN SYMMETRIC KEY SSN_Key
DECRYPTION BY CERTIFICATE HumanResources;
SELECT CONVERT(nvarchar, DecryptByKey(SSN)) AS 'SSN' FROM tbl_userinfo;
---Store Procedure, It works here.---
Create Procedure User_SSN
As
OPEN SYMMETRIC KEY SSN_Key
DECRYPTION BY CERTIFICATE HumanResources;
SELECT CONVERT(nvarchar, DecryptByKey(SSN)) AS 'SSN' FROM tbl_userinfo;
spyxdaxworld
Member
431 Points
232 Posts
Call store procedure from view
May 04, 2012 05:28 PM|LINK
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Call store procedure from view
May 04, 2012 05:44 PM|LINK
I think maybe you can using OPENQUERY? Try it and let us know. :)
limno
All-Star
117326 Points
8003 Posts
Moderator
MVP
Re: Call store procedure from view
May 04, 2012 06:05 PM|LINK
Can you check this link to see whether it helps:
http://blogs.msdn.com/b/lcris/archive/2005/06/10/428178.aspx
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Call store procedure from view
May 04, 2012 06:17 PM|LINK
If you put your decryption logic in a User Defined Function instead of a Stored Procedure, then you can definitely include it in a view.
Chen Yu - MS...
All-Star
21584 Points
2493 Posts
Microsoft
Re: Call store procedure from view
May 09, 2012 02:56 AM|LINK
Hi spyxdaxworld,
You want to call stored procedure in view, right? If so, I found below thread which is similar to your issue, it may give you some tips.
Please see: http://stackoverflow.com/questions/349979/execute-a-sql-stored-proc-from-a-sql-view
Thanks.
Feedback to us
Develop and promote your apps in Windows Store