Thursday, October 13, 2011

Sending table data to a stored procedure

For context, please refer my earlier post at http://wilth.blogspot.com/2011/10/cant-open-symmetric-keys-in-udfs.html

So, since we couldnt use our UDF and had to use a stored proc instead, the trouble with using a stored proc is that it cant directly go into the VALUES clause of an INSERT INTO---SELECT statement, like a UDF could. This means we would need to execute multiple (about a million, seriously) INSERT statements by looping on a temp table.

An alternative would be to have the remote stored proc instead do all the work of opening the key, executing the INSERT INTO---SELECT statement and then closing the key. This has the added benefit of not exposing the TABLE to the remote user. How do we do it? Using TABLE-type parameters. They just came up in SQL Server 2008.
http://msdn.microsoft.com/en-us/library/bb510489.aspx
explained more in
http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

And so, we made this stored proc. But sadly, the DB server that makes the call is still on SQL Server 2005, and so could not invoke this stored procedure. The alternative, it seems, is to send an XML parameter.
http://www.sqlservercentral.com/articles/News/3182/

No comments:

Post a Comment