passing key decryption password to Stored procedure

I try to pass the key used to decrypt symmetric key to a stored procedure as a parameter like this:

OPEN SYMMETRIC KEY MyKey
DECRYPTION BY PASSWORD=@. keypassword;

I get an error message saying "Incorrect syntax near "@.keypassword".
Is there something that I am missing?

Unfortunately the syntax doesn't allow a variable as a password. You will have to either use dynamic SQL to use the password (make sure you escape the user input properly to prevent SQL injection) or, if it is possible in your particular scenario, consider using the key hierarchy available in SQL Server 2005.

-Raul Garcia

SDE/T

SQL Server Engine

pass variable to identity function

is it possible to pass a variable to an identity funtion

example

declare @.max_note int

select @.max_note = max(key ) from notes

select m_key = identity( int, @.max_note, 1),
name

into #prod_note

from prod_note

this one challeged me a lot. heres a solution

insert into #temp

delete from #temp

and do insert again

USE NORTHWIND
declare @.cmd nvarchar(4000)
declare @.max_note int
select @.max_note =max(employeeid ) from employees
SELECT IDENTITY(int, 1,1) AS IDNUM,LASTNAME INTO #TEMP FROM EMPLOYEES
--SELECT * FROM #TEMP
DELETE FROM #TEMP
INSERT #TEMP(LASTNAME)
SELECT LASTNAME FROM EMPLOYEES
SELECT * FROM #TEMP

DROP TABLE #TEMP

not the best solution but works the same

|||

Identity function doesn't support variables for the seed or increment. So you have several options since you are creating a temporary table:

1. Create the temporary table without the identity column and then do an ALTER TABLE to add the identity column using dynamic SQL like:

exec('alter table #prod_note add m_key int identity(' + @.max_note_val + ', 1)')

2. Create an empty table using CREATE TABLE and then use DBCC CHECKIDENT (this accepts variable for seed parameter) to reseed the table before doing the insert. This will be fine if you are dumping small number of rows into the temporary table & performance is not the main criteria. The main reason being that SELECT INTO is the fastest way to create a temporary table with data and doing CREATE followed by INSERT...SELECT will be slower.

|||This answer above is awesome!

pass list of id's in xml as i/p to stored proc

hii

I have a problem in when i pass xml as input to stored procedure. The problem i m facing is that :

Say i have a table which has id as primary key in it.Now in my sp i want to delete some rows from that table and in xml i am passin more than 1 id ( it can be four even).

But before deleteion i want to check that delete the with that ID iff the ID exists else raise error for the id w/c does not exist like 'This Id does not exist.'

what i want is to loop between the id's which are being passed as i/p to sp..

and delete the row if it exists elso diaplsy message this id does not exist..

somewhat like this

If Exists(Select ProfileName From Area52 Where Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) ))

Begin

Select * From Area52Docs

WHERE Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) )

DELETE FROM Area52Docs

WHERE Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) )

DELETE FROM Area52

FROM @.XMLString.nodes('Area52') AS T(Item)

where Area52id=T.Item.value( '@.Area52ID', 'uniqueidentifier')

select T.Item.value( '@.Area52ID', 'uniqueidentifier') FROM @.XMLString.nodes('Area52') AS T(Item)

Select 12

End

Else RaisError('This Record Does Not Exist',11,1)

the thing i want to do is as above..but its not workin..it works only for the first id int he list of id's

plzz do help...

Here it is,

Code Snippet

Declare @.Area52ID as XML

Set @.Area52ID = '<root>

<Area52 Area52ID="1"/>

<Area52 Area52ID="2"/>

<Area52 Area52ID="3"/>

</root>'

Declare @.Deletable Table

(

Area52ID int,

IsExists bit

);

Insert Into @.Deletable(Area52ID)

select

T.C.value('@.Area52ID','int') Area52ID

from

@.Area52ID.nodes('/root/Area52') as T(C)

Update @.Deletable

Set

IsExists = case when [Docs].Area52ID is null then 0 else 1 end

From

@.Deletable [Status]

Left Outer Join Area52Docs [Docs]

on [Status].Area52ID = [Docs].Area52ID;

Delete from Area52Docs

Where Exists (Select 1 From @.Deletable [Status]

Where [Status].Area52ID = [Area52Docs].Area52ID)

Select

Area52ID,

Case IsExists When 1 Then 'Area52ID =' + Cast(Area52ID as varchar) + ', Is Deleted Successfully'

When 0 Then 'Area52ID =' +Cast(Area52ID as varchar) + ', Is Not Exists' End

From

@.Deletable

|||

Hii

The approach to reach my problem is very correct as has been told by you.

But this is being done by first fetching the Id's in a table variable..that alternative i was also thinkin will work and its working..But what i was thinking if i could do this wtihout taking any intermediary table and direct from the xml...

Thanx

Supriya