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 psuedo 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?

2. 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.