Query Manager Syntax

Below is a Query Manager query that I wrote with additional comments. I’m going to be making “use” of this query often as a template and example of how to quickly and easily setup future Query Manager queries. I know some people like to make things more complex and sort of “merge” the declare statements with the SET statements… but I just find that outright confusing. This works for me, and makes sense to me logically. I can understand what this is doing just by looking at it, so this is what I’m going to be doing going forward.

–You can add comments with standard SQL comment markers, though since QueryManager uses the multi-line comment marker in its code,

–It is not recommended that you use that, for the sake of clarity in code. Also, it’s WAY easier to edit these in an external editor,

–rather than within the Query Manager editor itself, since the window is so small.

–Declare your input variables to be used in your main query

DECLARE @Type NVARCHAR(30);

DECLARE @Make NVARCHAR(30);

DECLARE @Model1 NVARCHAR(50);

DECLARE @Model2 NVARCHAR(50);

DECLARE @Model3 NVARCHAR(50);

DECLARE @Model4 NVARCHAR(50);

DECLARE @Model5 NVARCHAR(50);

–Set the variables to the user input. Note that the query within the SQL comment markers are a UI element that allows users to pick from a list.

–ALL input variables MUST be “attached” to a database table populated with valid data of the correct type for the Query Manager to work properly.

SET @Type = /* SELECT eq.U_Type FROM dbo.[@EQUIPMENTLOCATION] eq WHERE eq.U_Type = */ ‘[%0]’

SET @Make = /* SELECT eq.U_Make FROM [dbo].[@EQUIPMENTLOCATION] eq WHERE eq.U_Make = */ ‘[%1]’

–Query Manager fills in the “assistance bubble” with ALL values of the field, no matter what.

–See SCN page http://scn.sap.com/thread/3276362 for more info

SET @Model1 = /* SELECT eq.U_Model FROM [dbo].[@EQUIPMENTLOCATION] eq WHERE eq.U_Model = */ ‘[%2]’

SET @Model2 = /* SELECT eq.U_Model FROM [dbo].[@EQUIPMENTLOCATION] eq WHERE eq.U_Model = */ ‘[%3]’

SET @Model3 = /* SELECT eq.U_Model FROM [dbo].[@EQUIPMENTLOCATION] eq WHERE eq.U_Model = */ ‘[%4]’

SET @Model4 = /* SELECT eq.U_Model FROM [dbo].[@EQUIPMENTLOCATION] eq WHERE eq.U_Model = */ ‘[%5]’

SET @Model5 = /* SELECT eq.U_Model FROM [dbo].[@EQUIPMENTLOCATION] eq WHERE eq.U_Model = */ ‘[%6]’

–Now, setup your main query

SELECT *

FROM [dbo].[@EQUIPMENTLOCATION] eq

WHERE eq.U_Type = @Type

    AND eq.U_Make = @Make

    AND (

        eq.U_Model = @Model1

        OR eq.U_Model = @Model2

        OR eq.U_Model = @Model3

        OR eq.U_Model = @Model4

        OR eq.U_Model = @Model5

        )

Hopefully this helps some other newbie along the way someday as well.