February 18, 2010 at 5:20 pm
I have an ExtendedSP that returns an Encrypted Value of the CreditCard No.s. I would have to use an Update statement to replace the unencrypted value with the encrypted value from ESP.
In order to do that i used temp table #Enc which have 50 rows with ccno column (unencrypted) and i am having issues with the while loop. I'm new to sqp. It would be great if someone can help.
DECLARE @iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@ccno varchar(255)
-- opid is an identity column
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(opid)
FROM #Enc
SELECT @iCurrentRowId = opid,@ccno = ccno
From dbo.#Enc Where opid = @iNextRowId
WHILE (@iLoopControl <= 49)
BEGIN
BEGIN
DECLARE @ResultSet TABLE (
ReturnValue varchar(255) )
INSERT @ResultSet (ReturnValue)
EXEC master.dbo.xp_EncryptCreditCard @ccno
--select * from @ResultSet
UPDATE dbo.#Enc SET ccno = (select ReturnValue from @ResultSet) where opid = @icurrentRowId
End
-- Reset looping variable.
SELECT @iNextRowId = NULL
-- get the next opid
SELECT @iNextRowId = MIN(opid)
FROM dbo.#Encrypt
WHERE opid > @iCurrentRowId
-- get the next row.
SELECT @iCurrentRowId = opid,@ccno = ccno FROM dbo.#Encrypt WHERE opid = @iNextRowId
SET @iLoopControl = @iLoopControl + 1
END
RETURN
February 18, 2010 at 5:42 pm
Priya11 (2/18/2010)
I have an ExtendedSP that returns an Encrypted Value of the CreditCard No.s. I would have to use an Update statement to replace the unencrypted value with the encrypted value from ESP.In order to do that i used temp table #Enc which have 50 rows with ccno column (unencrypted) and i am having issues with the while loop. I'm new to sqp. It would be great if someone can help.
DECLARE @iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@ccno varchar(255)
-- opid is an identity column
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(opid)
FROM #Enc
SELECT @iCurrentRowId = opid
From dbo.#Enc Where opid = @iNextRowId
WHILE (@iLoopControl <= 49)
BEGIN
BEGIN
DECLARE @ResultSet TABLE (
ReturnValue varchar(255) )
INSERT @ResultSet (ReturnValue)
EXEC master.dbo.xp_EncryptCreditCard @ccno
--select * from @ResultSet
UPDATE dbo.#Encrypt SET oprefno = (select ReturnValue from @ResultSet) where ccno = @ccno
End
-- Reset looping variable.
SELECT @iNextRowId = NULL
-- get the next opid
SELECT @iNextRowId = MIN(opid)
FROM dbo.#Encrypt
WHERE opid > @iCurrentRowId
-- get the next row.
SELECT @iCurrentRowId = opid FROM dbo.#Encrypt WHERE opid = @iNextRowId
SET @iLoopControl = @iLoopControl + 1
END
RETURN
It would probably be helpful to the people who want to help you if you told us what issues you're having with the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2010 at 6:55 pm
Thanks Jeff for the reply.
The above code is only updating the 1st row with the encrypted value and i also get these errors:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 512, Level 16, State 1, Line 28
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(0 row(s) affected)
Msg 512, Level 16, State 1, Line 28
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(0 row(s) affected)
Msg 512, Level 16, State 1, Line 28
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
February 19, 2010 at 1:48 am
The issue is here....
BEGIN
DECLARE @ResultSet TABLE (
ReturnValue varchar(255) )
INSERT @ResultSet (ReturnValue)
EXEC master.dbo.xp_EncryptCreditCard @ccno
--select * from @ResultSet
UPDATE dbo.#Encrypt SET oprefno = (select ReturnValue from @ResultSet) where ccno = @ccno
End
The @ResultSet table is not a clean table on each loop
try this code for example
declare @i integer
select @i =0
while @i <10 begin
Declare @Table table
(
i integer
)
insert into @table(i) values (@i)
select * from @table
select @i = @i +1
end
so all you need to do is delete from @ResultSet at the start of each iteration and all will be good
February 19, 2010 at 6:48 am
Thanks Dave:-). The code is now working if i delete @ResultSet for each iteration.
I do have another problem. There are few Invalid CreditCard Numbers (i.e ESP returns error) in the table and they are updated to "NULL" if i run this code. I want to skip those cc no.s and not change to "NULL". can you please help me with this?
Thanks for all your help.
February 19, 2010 at 7:11 am
Did you try his ?
UPDATE E
SET oprefno = R.ReturnValue
from dbo.#Encrypt E
, @ResultSet R
where E.ccno = @ccno
and R.ReturnValue is not null
Maybe even better to avoid the update in the first place
Add :
if exists (select 1 from @ResultSet where ReturnValue is not null)
begin
PUT YOUR UPDATE HERE
End
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 19, 2010 at 10:30 am
Thanks :-)!!!!!!! i used the 2nd one and it works
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply