helo needed with stored proc...

[raaj820]

Hi guys,
I am new to sql server 2000...
I am having an excel attachment which i need to import into my database (i have done this part by using import/export wizard)...
so now i am having a tbl in my database named 'processingtbl' having 5 columns named col1,col2,col3,col4 and cardnumber as follows..
col1 col2 col3 col4 cardnumber
1001 24653 10 298
1002 74839 20 733
1003 34690 11 988
Now, I need to write a stored proc which when i call should generate the cardnumber(by concatenating col1,col2,col3,col4) and shud encrypt it along with the import.
this is wht i tried...

create proc sp_getcardnumber
as begin
update processingtbl
set cardnumber = col1+col2+col3+col4
select * from processingtbl
end
--exec sp_getcardnumber

when i call the above stored proc, i can generate the cardnumbers successfully..but i am not able to understand how to encrypt them along with the import..(i was also asked to think about secure passphrase to use to encrypt the card numbers)...
so whts this passphrase and how to encrypt this cardnumber?????
any help is really appreciable....
thnx in advance....
raaj.....

[deepuv04]

Hi guys,
I am new to sql server 2000...
I am having an excel attachment which i need to import into my database (i have done this part by using import/export wizard)...
so now i am having a tbl in my database named 'processingtbl' having 5 columns named col1,col2,col3,col4 and cardnumber as follows..
col1 col2 col3 col4 cardnumber
1001 24653 10 298
1002 74839 20 733
1003 34690 11 988
Now, I need to write a stored proc which when i call should generate the cardnumber(by concatenating col1,col2,col3,col4) and shud encrypt it along with the import.
this is wht i tried...

create proc sp_getcardnumber
as begin
update processingtbl
set cardnumber = col1+col2+col3+col4
select * from processingtbl
end
--exec sp_getcardnumber

when i call the above stored proc, i can generate the cardnumbers successfully..but i am not able to understand how to encrypt them along with the import..(i was also asked to think about secure passphrase to use to encrypt the card numbers)...
so whts this passphrase and how to encrypt this cardnumber?????
any help is really appreciable....
thnx in advance....
raaj.....

hi

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.


for example


to encrypt
password is the key...
select EncryptByPassPhrase('MAK', '123456789')

to decrypt

select convert(varchar(100),DecryptByPassPhrase('MAK'
, 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598 DC56364D106B05D47B930093))


for your procedure use the following

create proc sp_getcardnumber
as begin
update processingtbl
set cardnumber = EncryptByPassPhrase('Password', col1+col2+col3+col4)
select * from processingtbl
end


thanks

[raaj820]

hey hi
thnx for ur reply..
i tried tht stored proc which u have posted....
i am getting this error...
'EncryptByPassPhrase' is not a recognized function name.

i am using sql server 2000...is tht the reason it is showing tht error???

[ck9663]

hey hi
thnx for ur reply..
i tried tht stored proc which u have posted....
i am getting this error...
'EncryptByPassPhrase' is not a recognized function name.

i am using sql server 2000...is tht the reason it is showing tht error???

i think so...EncryptByPassPhrase, i think (read: am not sure), is available in SQL 2005

-- ck