OK, given that you are doing everything in dev, you're cool.

Question: In your "Destructive" method you list the step:

"Using same prod db, Select into temp, drop existing table,...

Well, FWIW, I don't like your "Destructive Method" because you are creating your test tables in the Production database(s).

The "Pull Method" is better, but you still are using the...

Actually, along with Merge Replication (which is more compliated because it requires conflict resolution rules to be established), there are special options called "Immediate Updating" and "Queued Updating" which allow data...

http://www.databasejournal.com/features/mssql/article.php/10894_3427581_2

(Try running the samples again--I edited my last post)

Not real sure what you want to do, but check out these commands (from BOL). Perhaps they'll give you some of what you're looking for!

SELECT SYSTEM_USER

GO

SELECT HOST_NAME()

GO

SELECT USER_NAME()

GO

Personally, I think it's a great idea to include the block around the stored procedure. 

Why?  Well, as future versions of SQL come out and the lines between Transact-SQL and pure CLR code begin...

Well,

You've got to expect some arithmetic roundoff to occur.  Numbers you're getting don't seem that unreasonable.  That's the way the numerical cookie crumbles.

One suggestion, though.  To reduce the effects of ...

Well, I know very little about Reporting Server, but could your query be written with the "CHAR()" operator? For example:

Instead of:

SELECT 'This is a string with a # symbol'

Replace with something...

Another approach which will ONLY be practical if the maximum value of [X] is small and your query is relatively short: 

Use a case statement with MAX(X) or MAX(X + 1)...

(Update--continued from last post) Actually, I vaguely recall having "gotten burned" by some weird NULL-handling behavior in a query like this.  Things didn't always perform as expected. 

Instead of testing against...

This SEEMS to work, but no guarantees!

- john

USE Northwind

GO

-- Demonstrate A Query Using An Optional Parameter

CREATE PROCEDURE spGetOrders

   @OrderID INT = NULL

AS

BEGIN

   SELECT OrderID,...

Well,

I don't have an answer(!)  But you've got several variables here, and you might investigate this phenomenon further by exploring individual scenarios one-at-a-time (at least that's how I'd try to attack the problem).  For example, investigate:

(1) The number of...

Well, sounds like there's going to be a lot of administration work in any scenario... 

Have you considered multiple environments?

In our env. we have a "development" instance where developers can...

>>this won't let them modify the SPs though - only execute

Hey yeah, you're right -- sorry I didn't see in the original message that they wanted to be able...