Referencing global variables from views

[CMBNH]

Hi,
I am converting my data storage from access to sql server 2000 and am having a number of difficulties.
While access' upsizing wizard converted my tables well, most of my queries were left untouched. I am trying to rewrite them manually and have hit a block. Many of my queries reference a global variable in the where statement like Where PatientID = getgblPatientID(). The "getgblPatientID" is simply a function which returns the global variable gblPatientID (assigned from forms).
How can I accomplish this in SQL server? Will this variable be stored locally (i.e not viewable/modifiable from other client applications)?
Thanks in advance,

Chris

 

[SQLSister]

Create stored procedures with input parameters that will stand for the same data items as your global variables. Then when you execute the stored procedure you pass the values of the global variables as the values for the stored procedures input parameters.

 

[cmmrfrds]

You can use a function in your sql statement just like you do in access. The function gets resolved before the sql is passed to SQL Server for execution. I have had no problem doing this, but the stored procedure is a good route to go. If you are using SQL Views (as in an access project) that is a different story since the sql already exists on the server, then you would need to use the stored procedure method.