January 21, 2009 at 8:58 am
i have a table test
field1 int pk
field2 int
field_encrypt varbinary(256) not null
initial inserted records
field1field2 field_encrypt
134null
2456null
356null
now i want to update the value of field_encrypt
declare @pass varchar(50)
SET @pass = 'WOhPorN5VIKZl$Eqcz4CR104O'
declare @pass_seed varchar(50)
while (select count(field1) from test)
select vai1 from test order by field1
set @pass_seed = @pass + cast(field1 as varchar)
update test
set field_encrypt=encryptByPassphrase(@pass_seed,cast(field1 as varchar))
it is giving foloowing error
An expression of non-boolean type specified in a context where a condition is expected, near 'select'.
i know iam doing wrong.. but dont know how to fix..
please help me.
January 21, 2009 at 9:24 am
The error is happening here
while (select count(field1) from test)
While requires Boolean expression
So is it
while (select count(field1) from test) > 0
January 21, 2009 at 9:57 am
declare @pass varchar(50)
SET @pass = 'WOhPorN5VIKZl$Eqcz4CR104O'
declare @pass_seed varchar(50)
declare @vai1 int
declare @val int
while (select count(vai1) from testvai )>0
begin
select @vai1=vai1 from testvai order by vai1,vai2
set @pass_seed = @pass + cast(@vai1 as varchar)
select @val=encryptByPassphrase(@pass_seed,cast(vai1 as varchar))
from testvai
update testvai
set vai_encrypt=@val
end
select * from testvai
i changed the while condition..
but it is continuly looping.. never ending.. where i am doing mistake..
please help me
January 21, 2009 at 10:08 am
What are you trying to do within your loop?
are you trying to update all the rows one at a time?
one specific row?
Please take your sample data and show us what you want the values to be after it is done.
It doesn't look like your loop is doing anything
Its staying in the loop forever because
(select count(vai1) from testvai )>0 is always true.
Within the loop this statement is selecting only the last row every time
select @vai1=vai1 from testvai order by vai1,vai2
I've made changes to your loop making assumptions about what your doing
but I'm thinking if you post clearly what you need, this can be done much simpler
declare @pass varchar(50)
SET @pass = 'WOhPorN5VIKZl$Eqcz4CR104O'
declare @pass_seed varchar(50)
Declare @x int
declare @y int
declare @vai1 int
declare @val int
Set @x = Min(vai1 from testvai)
set @y = Max(vai1 from testvai)
while @x <= @y
begin
select @vai1=vai1 from testvai Where vai1 = @x --order by vai1,vai2
set @pass_seed = @pass + cast(@vai1 as varchar)
select @val=encryptByPassphrase(@pass_seed,cast(vai1 as varchar))
from testvai
where vai1 = @x
update testvai
set vai_encrypt=@val
where vai1 = @x
Set @x = @x+ 1 --
end
select * from testvai
But I'm really thinking all you need to do is
update testvai
set vai_encrypt= dbo.encryptByPassphrase(@pass + cast(@vai1 as varchar), cast(vai1 as varchar))
January 21, 2009 at 10:23 am
this is a stored procedure..
i am inserting prizes_seeds table from table variable
so that pk value is inserted into prizes_seeds table
INSERT INTO prizes_seeds (seed_time,seed_time_encrypt)
SELECT distinct tvseed_time,tvencrypt_date from @tableseed_time order by tvseed_time
after this i want to update the encrypted prize_id...in prizes_seeds table
While(select count(prize_seed_id) from prizes_seeds)>0
begin
select @maxprize_seed_id = ps.prize_seed_id ,
@prize_id=tst.tvprize_id
from prizes_seeds as ps, @tableseed_time as tst
where ps.prize_seed_id=tst.tvmax
set @prize_id_pass = @pass +CAST(@maxprize_seed_id AS VARCHAR)
Set @prize_id_encrypt = EncryptByPassphrase(@prize_id_pass,Cast(@prize_id as varbinary))
update prize_seeds
set prize_id_encrypt =@prize_id_encrypt,
passkey = @prize_id_pass
where ps.prize_seed_id=@maxprize_seed_id
end
now if i write like this my stored proc is ginving following erro message
Incorrect syntax near 'END'.
January 21, 2009 at 10:49 am
Error is sloved but not it is going infinity loop
INSERT INTO prizes_seeds (seed_time,seed_time_encrypt)
SELECT distinct tvseed_time,tvencrypt_date from @tableseed_time order by tvseed_time
after this i want to update the encrypted prize_id...in prizes_seeds table
While(select count(prize_seed_id) from prizes_seeds)>0
begin
select @maxprize_seed_id = ps.prize_seed_id ,
@prize_id=tst.tvprize_id
from prizes_seeds as ps, @tableseed_time as tst
where ps.prize_seed_id=tst.tvmax
set @prize_id_pass = @pass +CAST(@maxprize_seed_id AS VARCHAR)
Set @prize_id_encrypt = EncryptByPassphrase(@prize_id_pass,Cast(@prize_id as varbinary))
update prize_seeds
set prize_id_encrypt =@prize_id_encrypt,
passkey = @prize_id_pass
where ps.prize_seed_id=@maxprize_seed_id
end
i want to update prize_id_encrypt as bluck..
i think my while condition is wrong..
please help me
January 21, 2009 at 12:57 pm
Once again this will always be true, so it will be an infinate loopl
While(select count(prize_seed_id) from prizes_seeds)>0
Based off your code I don't think you need a while statement
January 21, 2009 at 2:33 pm
Finally everything is working..
Thank you very much for your replies..
January 21, 2009 at 8:12 pm
venkatesh.kuppili (1/21/2009)
Finally everything is working..Thank you very much for your replies..
So, can we see your final code?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply