user-defined select criteria

[jadn]

Hi,
This seems like such a simple/common need that I'm reluctant to ask, but I just don't see a good generic solution for constructing an SQL query-string.

The user will supply "QQuery" fields which will select records from "Tests". I don't know which fields the user will supply. If they supply none, then "QResults" should contain all records from "Tests". That query might look (loosely/remotely) like:

SELECT Tests.TestID, PartTypes.PartNumber, DUTs.SerialNumber, Operators.Operator, Tests.ResultCode
FROM Operators INNER JOIN ((Stations INNER JOIN (DUTs INNER JOIN (PartTypes INNER JOIN (Tests INNER JOIN QueryTests ON Tests.TestID = QueryTests.TestID) ON PartTypes.PartNumber = QueryTests.PartNumber) ON (DUTs.DUTNdx = Tests.DUTNdx) AND (PartTypes.PartTypeNdx = DUTs.PartTypeNdx)) ON Stations.StationNdx = Tests.StationNdx)

If they supply SerialNumber and ResultCode, then only records matching the specific SerialNumber and ResultCode should be returned.

I wonder if all that's required is a correctly constructed "WHERE" clause(?) - need to try that. (still trying to get my mind around "INNER JOINS")

Sorry to bother!
Jadn

------------------------------
"QQuery", "QResults" (QQuery fields are inputs)
(
TestID int,
PartNumber char(),
SerialNumber char(),
Operator char(),
ResultCode int
)

"Tests" table has the following columns:
(
TestID int Primary Key,
DUTNdx int Foreign Key,
OperatorNdx int Foreign Key,
ResultCode int
)

"PartTypes"
(
PartTypeNdx int Primary Key,
PartNumber char()
)

"DUTs"
(
DUTNdx int Primary Key,
PartTypeNdx int Foreign Key,
SerialNumber char()
)

"Operators"
(
OperatorNdx int Primary Key,
Operator char()
)

 

[bluecjh]

don't think your query makes any sense (no offense intended)

the tables are not joined and any virtual tables
if thats what they are have no preceding SELECT.
if there are supposed to be nested queries
you will still need select other wise should be along the lines
of

SELECT
Tests.TestID,
PartTypes.PartNumber,
DUTs.SerialNumber,
Operators.Operator,
Tests.ResultCode
FROM
Operators o
INNER JOIN DUTs d ON o.? = d.?
INNER JOIN ... ETC...


BlueCJH

 

[bboffin]

This is a common problem and SQL Server provides a fairly simple approach best doen within a stored procedure.

As you suggest you need to build up a query string and then add the appropriate WHERE clauses and finally execute the string.

Here's a very simple example of how to get started.

.
.
.
DECLARE @SQL nvarchar(1000)
SET @SQL = 'SELECT .....'  -- this is the whole of your SELECT statement up to but excluding the WHERE clause.

-- now build up the WHERE clause in another variable
DECLARE @WHERE nvarchar(1000)
SET @WHERE = ''
-- @Param1 is a number
IF @Param1 is not null 
    SET @WHERE = @WHERE + 'Column1 = ' + @Param1 + ' AND '
-- @Param2 is a string so need to enclose it in apostrophes
IF Param2 is not null 
    SET @WHERE = @WHERE + 'Column2 = ''' + @Param2 + ''' AND '
-- etc
IF LEN(@WHERE) > 0
BEGIN
    -- remove last ' AND '
    SET @WHERE = SUBSTRING(@WHERE, 1, LEN(@WHERE) - 5)
    -- now comes the clever bit
    -- this will execute the query string plus the where clause that you have constructed
    -- note that the parentheses are required!
    EXEC(@SQL + ' WHERE ' + @WHERE)
END
ELSE
BEGIN
    -- don't need the where clause so it's simpler
    EXEC(@SQL)
END

This technique is known as Dynamic SQL and has some disadvantages in that it executes slightly slower (only in terms of how long it takes to start the query not how long it taked to retrieve that data) and that the user can maliciously insert code to be executed (via the parameters) that can damage the database unless the code in the stored procedure takes steps to detect and avoid such attacks. This is known as SQL Code Insertion if you want to read up on it.

Hope that helps a bit


Bob Boffin

 

[SQLDenis]

For dynamic SQL a must read is:The Curse and Blessings of Dynamic SQL (

http://www.sommarskog.se/dynamic_sql.html)

by SQL Server MVP Erland Sommarskog,

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jadn]

Hi folks,
Thanks for all the replys. It's nice to know I was on the right track, and the WHERE-clause code will jump-start the stored-procedure work (TY Bob!). Thanks, too, for the security-heads-up related to SQL embedded in parameters (I like to think I write sturdy code, but that bullet would'a blind-sidded me!)

Cheers!