Dynamically build a UNION ALL

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

Featured Post

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…