March 21, 2005 at 3:03 pm
Is there any way to conditionally encrypt a stored procedure when creating it? For example, running the sp script against the development server would not encrypt the sp, but running the same sp script against the production sever WOULD encrypt the sp.
Is this possible?
Thanks.
March 21, 2005 at 3:06 pm
Yes. When creating in production add the WITH ENCRYPTION line. Probably the easiest thing to do is to write a script that handles adding that line before deployment to the production server.
K. Brian Kelley
@kbriankelley
March 21, 2005 at 3:14 pm
Brian,
Does your solution involve two different SP scripts (one with 'WITH ENCRYPTION', and one without)? Or does your solution use only one SP script? Because, what I am looking for is the ability to run only one SP script against both the development and have the SP encrypted when I run the script against the production server, but not have it encrypted when I run the exact same script against the development server. I do not want to maintain two scripts, or have to modify the script when I run it against the other server.
March 21, 2005 at 3:19 pm
Yes, it would involve two scripts, because effectively that's what you're doing. The only other thing to do is key off of the servername or some other flag and then have a conditional execution of code. But then you're talking about wrapping your stored procedure into a larger bit of code.
K. Brian Kelley
@kbriankelley
March 21, 2005 at 3:31 pm
Brian: "The only other thing to do is key off of the servername or some other flag and then have a conditional execution of code."
That's exactly what I am talking about. I wanted to wrap the 'With Encryption' (or the 'Create Procedure' and 'With Encryption') in some kind of an If statement keyed off of @@ServerName. I was hoping SQL Server had some kind of pre-processor if statement (like C does) or something similar that I could use.
March 21, 2005 at 3:41 pm
No. Not in T-SQL. However, as long as you don't go beyond 4,000 characters total (including WITH ENCRYPTION), you could build the CREATE PROCEDURE statement dynamically and then issue an EXEC() or sp_executesql statement to make it so without a lot of trouble.
K. Brian Kelley
@kbriankelley
March 22, 2005 at 6:53 pm
"However, as long as you don't go beyond 4,000 characters total (including WITH ENCRYPTION),"
You can go to 8000 characters if you use varchar instead of nvarchar. If you concatenate system names (like @@ServerName) you'll need to cast them as varchar before concatenating.
If you have to go past this you'll need to use the "text" datatype, which is more difficult to work with.
cl
Signature is NULL
March 22, 2005 at 7:07 pm
True. The "for optimum performance, do not use more than 4,000 characters" for EXEC doesn't apply in this case because you're only building the stored procedure once. Good point.
K. Brian Kelley
@kbriankelley
March 22, 2005 at 9:47 pm
Using a combination of VARCHAR(8000) as previously mentioned and multiple variables, you can build stored procedures to the max of 250 MB (for "Bytes in source text of a stored procedure" according to BOL) using concatenization of multiple VARCHAR(8000) variables like this....
EXEC (@SQL1+@SQL2+@SQL3....@SQLn). Be sure to include the parenthesis or it won't work. Several of the undocumented stored procedures included in SQL Server use this technique. Check out the dbo.sp_execresultset sproc in the Master database... it uses 250 such concatenizations.
According to most sources, the real key "for optimum performance" is to not use dynamic SQL... didn't seem to bother the kids in Redmond a bit when they wrote dbo.sp_execresultset, eh?
--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