I have part of a stored proc that I need help with.
I need to figure out how to delete infro from the
tblManifest using the @.ssnDelete variable.
I Have multiple records ffor the given above variable.
Would this need to loop in order to work and that is the solution for this.
I get ther following error when I run the full stored proc.
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is the code fro part of the proceedure.
Thanks in advance.
Gene
-
CREATE TABLE dbo.tblTempSSN(
ssn varchar(11) null);
OPEN SYMMETRIC KEY SymKeySSN
DECRYPTION BY CERTIFICATE CertSSN;
INSERT INTO tblTempSSN(ssn)
SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN
FROM tblSoldier s, tblManifest m
WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn
CLOSE SYMMETRIC KEY SymKeySSN;
declare @.ssnDelete varchar(11);
set @.ssnDelete = (select (m.ssn) as ssn
from tblManifest m, tblTempSSN t
where t.ssn = m.ssn);
delete
from tblManifest
where ssn = '@.ssnDelete';
DROP TABLE dbo.tblTempSSN;
-
Try:
delete tblManifest
where exists (
select *
from dbo.tblTempSSN as a
where a.SSN = tblManifest.SSN
)
go
AMB
|||
Code Snippet
CREATE TABLE dbo.tblTempSSN(
ssn varchar(11) null);
OPEN SYMMETRIC KEY SymKeySSN
DECRYPTION BY CERTIFICATE CertSSN;
INSERT INTO tblTempSSN(ssn)
SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN
FROM tblSoldier s, tblManifest m
WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn
CLOSE SYMMETRIC KEY SymKeySSN;
delete
from tblManifest
from tblManifest
inner join tblTempSSN on tblTempSSN.ssn = tblManifest.ssn
DROP TABLE dbo.tblTempSSN;
