Help needed in build dynamic SQL of unknown table.

Hi Experts,

I have the following code in a module and would like to apply that to another case, however in that other case I don't know beforehand the name of the table I am filtering for (like Employeestbl in the example below), how can this be accomplished?

        s = s & " AND Exists (SELECT 1 FROM Employeestbl WHERE " & sTbl & "EmployeeID = Employeestbl.ID and " & Mid(sEmp, 5) & ")"

Open in new window


PS. I know this can be done using
 where  EmployeeID in (Select ID from Employeestbl)

Open in new window

but that would not be as efficient.
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I'm not sure why you are using a subquery but regardless, you would have to build the entire query string using VBA if you want to specify a table name at run time.  No querydef can have variable structural elements.  All you can do is pass in a data value at runtime.  The query engine creates an execution plan when Access saves the querydef so if you think about it, the plan would have to completely change if you substitute one table for another.
bfuchsAuthor Commented:
Hi Pat,

This is used in vba as a parameter for the following store procedure

USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[procStatistics]    Script Date: 05/12/2015 18:02:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procStatistics]
(
	@intCategory	AS int,
	@strFilter		AS nvarchar(4000)
)
AS
BEGIN
	--DECLARE @strSQL AS nvarchar(MAX) = '';
	DECLARE @strSQL AS nvarchar(MAX);
	
if @strFilter = '' set @strfilter = 'EmployeeID > 0';
	
 IF @intCategory = 1	--State
		SET @strSQL = ' SELECT [State] AS Category, COUNT(*) AS [Total #]
						FROM dbo.View_EmpStatisticsEmployeestbl
						WHERE ' + @strFilter +
					  ' GROUP BY [State]';
ELSE IF @intCategory = 8	--Source
		SET @strSQL = ' SELECT [Source] AS Category, COUNT(*) AS [Total #]
						FROM dbo.View_EmpStatisticsSource
						WHERE ' + @strFilter +
					  ' GROUP BY [Source]';	
	EXECUTE sp_executesql @strSQL;				
END

Open in new window


I have there two  options of tables (View_EmpStatisticsEmployeestbl & View_EmpStatisticsSource)

Now if I use EmployeeID in (Select ID from Employeestbl), that will work as both tables/views have EmployeeID, however if I want to replace it with the faster method Exists, I'm wondering how can it be done?
Nick67Commented:
As @PatHartman said, it is just string work.
The question is about "dynamic SQL' so is it safe to assume you are doing this in VBA?
Its just string work
Dim s as string

s= "Select SomeField "
s = s & "From SomeTable Join AnotherTable "
s = s & "Where SomeCondition = SomeValue "
s = s & " And Exists (Select who knows what)"

EmployeeID in (Select ID from Employeestbl)
EXISTS is more efficient that IN if NULL is in play.
But both are markedly poorer than WHERE, and they all get their butts kicked by JOIN if you can manage that.
And generally -- though it can be a brain-bender if you don't breathe set logic -- An IN or EXISTS clause can be rewritten as a JOIN.

But it's all just concatenating strings.

*** posted before your replay to @PatHartman***
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PatHartmanCommented:
If this is part of a stored procedure, you will need to replace the sp with updated SQL.  Stored procedures are like querydefs.  When they are saved, the query engine builds an execution plan.  If you change something structural such as replacing one table with another, you must rebuild the sp.

I don't know enough about stored procedures to tell you if you can actually use dynamic SQL.

When I was working in COBOL, I could.  embedded SQL was compiled at compile time and an execution plan was created and saved.  dynamic SQL was compiled at run time and a plan was built each time the query ran.
Nick67Commented:
Looking at your sproc, why don't you add a third parameter of  @EmployeeID to it?
You could then use VBA to pass in the appropriate value -- 0 for none, and a real value if there is one, and have the logic in the sproc branch based on what comes in?

No IN or EXISTS required anymore, as you'll do the subquery in the VBA
Nick67Commented:
@PatHartman
Look at the logic.
It's an IF THEN based on what value of @intCategory he passes in.
The SELECT tables comes from the branches of the IF THEN
Scott PletcherSenior DBACommented:
You'll be just fine using:

WHERE  EmployeeID IN (SELECT ID FROM EmployeesTbl)

as SQL Server will convert that to the equivalent of an EXISTS anyway.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
@Scott
How would you rewrite the two branches using a JOIN?
I don't do the SQL Server end that often but isn't
WHERE  EmployeeID IN (SELECT ID FROM EmployeesTbl)

the functional same as

FROM dbo.View_EmpStatisticsEmployeestbl JOIN EmployeesTbl on EmployeesTbl.ID = dbo.View_EmpStatisticsEmployeestbl  

And will it perform better/same/worse?
bfuchsAuthor Commented:
Thanks a lot!

Actually I was thinking similar to Nick's suggestion, to determine the table according to category and use a replace function to a given string like replace( "MyTable", @TableName).
But since its not a big difference, why bother..
Scott PletcherSenior DBACommented:
but isn't

WHERE  EmployeeID IN (SELECT ID FROM EmployeesTbl)

 the functional same as

FROM dbo.View_EmpStatisticsEmployeestbl JOIN EmployeesTbl on EmployeesTbl.ID = dbo.View_EmpStatisticsEmployeestbl  

 And will it perform better/same/worse?

Typically yes.  If you don't reference another column from the EmployeesTbl, SQL should turn the INNER JOIN into the equivalent of an EXISTS since that performs better.  SQL's optimizer is sophisticated enough to do that.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.