Parameters? Please help.

[Arch]

I am trying to convert this Access query into T-SQL in SQL
Server. I could not figure out the correct syntax for
parameters in SQL Server. Do I need to declare it as a
veriable?
The following is SQL from Access 2000. Please help me
convert this to SQL Server so the parameters would work.
DistManager is a table and ProjectsAwarded is a query
which is already in SQL Server.

SELECT -------
FROM -------
WHERE ((tblDistManager.DistManagerID)=[Please Enter The
District #]) AND (([qryProjectsAwarded].ProjectID) Is
Null) AND (([qryProjectsAwarded].AwardDate) Is Null) AND
(([qryProjectsAwarded].AwardApprvl) Is Null)

Thank You very much in advance.
Arch.

 

[MGFoster]

I am trying to convert this Access query into T-SQL in SQL
Server. I could not figure out the correct syntax for
parameters in SQL Server. Do I need to declare it as a
veriable?
The following is SQL from Access 2000. Please help me
convert this to SQL Server so the parameters would work.
DistManager is a table and ProjectsAwarded is a query
which is already in SQL Server.

SELECT -------
FROM -------
WHERE ((tblDistManager.DistManagerID)=[Please Enter The
District #]) AND (([qryProjectsAwarded].ProjectID) Is
Null) AND (([qryProjectsAwarded].AwardDate) Is Null) AND
(([qryProjectsAwarded].AwardApprvl) Is Null)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to turn it into a stored procedure (SP) in order to pass
parameters.

If you're joining to an Access query (qryProjectsAwarded) you'll either
have to incorporate that query as a tabular subquery in the SP, or, turn
it into a View & refer to the view in the SP.

Here is an example of a SP definition using a view in place of the
qryProjectsAwarded:

CREATE PROCEDURE usp_Example
@DistrictID INTEGER
AS
SELECT ...
FROM tblDistManager AS DM LEFT JOIN vw_ProjectsAwarded As PA
ON DM.DistManagerID = PA.DistrictID
WHERE DM.DistManagerID = @DistrictID
AND PA.ProjectID IS NULL
AND PA.AwardDate IS NULL
AND PA.AwardApprvl IS NULL

If you can't turn the qryProjectsAwarded query into a View (i.e., it
requires parameters) you could try a table-value function (if you're
using SQL'r 2000) and pass the needed parameters thru the SP. See the
SQL'r Books on Line (BOL) CREATE FUNCTION article for more info. You
could also just incorporate that query as a tabular subquery in the SP.
See the BOL article "Subqueries Used in Place of an Expression."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHwf34echKqOuFEgEQLfygCfdHbVYNw7orVcdfsYcWBz9gaLPy0AoOPK
9CccHKW8ww3SPX0X48FpD5uv
=AZ3m
-----END PGP SIGNATURE-----