Solved

Dynamically build a UNION ALL

Posted on 2016-07-29
2
25 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
[X]
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
2 Comments
 
LVL 49

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

724 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