Setting Connection Parameters via ADO for SQL Server

Question

Is it possible to set a connection parameter on a connection to SQL Server and have that variable persist throughout the life of the connection? The parameter must be usable by subsequent queries.

We have some old Access reports that use a handful of VBScript functions in the SQL queries (let's call them GetStartDate and GetEndDate) that return global variables. Our application would set these before invoking the query and then the queries can return information between date ranges specified in our application.

We are looking at changing to a ReportViewer control running in local mode, but I don't see any convenient way to use these custom functions in straight T-SQL.

I have two concept solutions (not tested yet), but I would like to know if there is a better way. Below is some pseudo code.

1. Set all variables before running Recordset.OpenForward

   Connection->Execute("SET @GetStartDate = ...");  
   Connection->Execute("SET @GetEndDate = ...");
   // Repeat for all parameters  
   

Will these variables persist to later calls of Recordset->OpenForward? Can anything reset the variables aside from another SET/SELECT @variable statement?

1. Create an ADOCommand "factory" that automatically adds parameters to each ADOCommand object I will use to execute SQL

   // Command has been previously been created
   ADOParameter *Parameter1 = Command->CreateParameter("GetStartDate");
   ADOParameter *Parameter2 = Command->CreateParameter("GetEndDate");
   // Set values and attach etc...
   

What I would like to know if there is something like:

Connection->SetParameter("GetStartDate", "20090101");
Connection->SetParameter("GetEndDate", 20100101");

And these will persist for the lifetime of the connection, and the SQL can do something like @GetStartDate to access them. This may be exactly solution #1, if the variables persist throughout the lifetime of the connection.

Answer 1

Since no one has ventured an answer I'm guessing there isn't an elegant solution, that said:

Global cursors persist for the duration of the connection and can be accessed from any SQL or stored proc so you could execute this once on the connection:

DECLARE KludgeKursor CURSOR GLOBAL STATIC FOR
SELECT StartDate = '2010-01-01', EndDate = '2010-04-30'
OPEN KludgeKursor

and in your stored procedures:

--get the values
DECLARE @StartDate datetime, @EndDate datetime
FETCH FIRST FROM GLOBAL KludgeKursor
INTO @StartDate, @EndDate

--go crazy
SELECT @StartDate, @EndDate

Each connection would only see their own values, so the same stored procs can be used for different connection/values. The global cursor is automatically deallocated when the connection ends