Stef Merlijn
asked on
Dynamically build a UNION ALL
Hi,
In a SQL Server database there are multiple tables. I want to search for a certain string-value accross these tables using a Stored Procedure. The result-set from each table will be exactly the same and is currently combined using "UNION ALL".
Depending on the authorization of a user (in my Delphi app) I only want to select records from certain tables. So I was thinking about using a few parameters to pass that information.
If a user is not authorized for any of the tables the resultset will be empty.
Any different approach is perfectly fine for me too.
In a SQL Server database there are multiple tables. I want to search for a certain string-value accross these tables using a Stored Procedure. The result-set from each table will be exactly the same and is currently combined using "UNION ALL".
Depending on the authorization of a user (in my Delphi app) I only want to select records from certain tables. So I was thinking about using a few parameters to pass that information.
If a user is not authorized for any of the tables the resultset will be empty.
CREATE PROCEDURE MyProcedure (
@SearchString nvarchar(255),
@AllowTable1 bit,
@AllowTable2 bit,
@AllowTable3 bit
)
AS
BEGIN
-- Based on parameter @AllowTable1 I want to add values from Table1 to the selection (or not).
SELECT MyID1 AS ID, MyNvarcharField1 AS FoundString, 'Table1' AS Table
FROM Table1
WHERE MyNvarcharField1 LIKE '%' + @SearchString + '%'
UNION ALL
-- Based on parameter @AllowTable2 I want to add values from Table2 to the selection (or not).
SELECT MyID2 AS ID, MyNvarcharField2 AS FoundString, 'Table2' AS Table
FROM Table2
WHERE MyNvarcharField2 LIKE '%' + @SearchString + '%'
UNION ALL
-- Based on parameter @AllowTable3 I want to add values from Table3 to the selection (or not).
SELECT MyID3 AS ID, MyNvarcharField3 AS FoundString, 'Table3' AS Table
FROM Table3
WHERE MyNvarcharField3 LIKE '%' + @SearchString + '%'
Ect.
END
What would be the right way to accomplish this?Any different approach is perfectly fine for me too.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For certain you can use (1=True, 0=False):
... And @allowtabe1 = 1