Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dynamically build a UNION ALL

Posted on 2016-07-29
2
21 Views
Last Modified: 2016-07-29
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.

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

Open in new window

What would be the right way to accomplish this?
Any different approach is perfectly fine for me too.
0
Comment
Question by:Delphiwizard
2 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41734286
Add an appropriate predicate to each where clause e.g. for table1

 ... And @allowtabe1 = true

Not sure about bit variables I think you can use true.
0
 

Author Closing Comment

by:Delphiwizard
ID: 41734525
Perfect and so simple. Thanks!

For certain you can use (1=True, 0=False):
 ... And @allowtabe1 = 1
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question