Variable for FROM clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Variable for FROM clause

Author

Topic

MrSmallTime
Starting Member

32 Posts

Posted - 2014-07-10 : 06:03:43

I have 2 question that I’d really appreciate some help with in relation to the SP below.

1 - Is this structure OK or can it be improved in terms or performance or elegance

2 - I’d like to use 2 variables to enable the selection of
different tables in the From clause eg FROM @Tablevariable and also
different Fields in the Where clause eg @Fieldvariable >= @StartDate
Is this possible?

Many thanks in advance


CREATE Proc [dbo].[spUnionBikes]
	(
	@StartDate AS DATE = '2000-01-01'
	,@EndDate AS DATE 
	)

AS
BEGIN
	SELECT 
		ReferralID
		,Bike1 AS 'BIKES'
		,DateReceived
		,DateClosed
		,Rider 
	FROM
		TblReferral
	WHERE
		Bike1 IS NOT NULL
		AND DateReceived >= @StartDate
		AND DateReceived <= @EndDate  
	UNION ALL
	SELECT 
		ReferralID
		,Bike2
		,DateReceived 
		,DateClosed
		,Rider 		 
	FROM
		TblReferral
	WHERE
		Bike2 IS NOT NULL
		AND DateReceived >= @StartDate
		AND DateReceived <= @EndDate 
	UNION ALL
	SELECT 
		ReferralID
		,Bike3
		,DateReceived 
		,DateClosed
		,Rider  
	FROM
		TblReferral
	WHERE
		Bike3 IS NOT NULL
		AND DateReceived >= @StartDate
		AND DateReceived <= @EndDate 
	UNION ALL
	SELECT 
		ReferralID
		,Bike4
		,DateReceived 
		,DateClosed
		,Rider 
	FROM
		TblReferral
	WHERE
		Bike4 IS NOT NULL 
		AND	DateReceived >= @StartDate 
		AND DateReceived <= @EndDate 
	ORDER BY BIKES ASC
END


GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-10 : 07:43:55

The only way you can have a variable table name is to use dynamic SQL. Read up on it and look at some examples. You should be able to figure it out.

MrSmallTime
Starting Member

32 Posts

Posted - 2014-07-10 : 07:46:56

Many thanks

MrSmallTime
Starting Member

32 Posts

Posted - 2014-07-10 : 13:09:56

Thanks for the pointer towards dynamic SQL, worked a treat

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-10 : 13:24:17

While dynamic SQL may have solved your issue, it is not the right solution. If you need the FROM to be dynamic, then you've got either a code issue, a database design issue or both. Please read this in its entirety to understand the pros and cons of dynamic SQL. The cons are very important for your question.

http://www.sommarskog.se/dynamic_sql.html

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-11 : 08:59:43

Well said Tara! I should add that an alternative approach is to use synonyms. Write the query to read from a synonym, then change the synonym to point to the table you want. It can make for simple code but the drawback is that you may have to single-thread your queries. Imagine that you:

1. change the synonym
2. run the query

but after 1. someone else started to do the same thing and change the synonym again before you got to step 2. You might have to wrap the whole thing in a transaction with an appropriate isolation level to prevent that which could single-thread your query.

MrSmallTime
Starting Member

32 Posts

Posted - 2014-07-12 : 08:10:58

Thanks for all the heads-up's it's very much appreciated. The reason I'm quite taken with gbritton's suggestion to my question is that the code is re-usable. It saves having to write multiple SP's with only minor differences.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-13 : 18:58:04

True, but the negatives far outweigh that. You've got to think about scalability, performance and security.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-14 : 07:45:22

Here's yet another way: Set up your proc to take a table name, but don't use dynamic SQL. Instead, use a CTE construction like this:


with source as (
    select * from table1 where @tablename = 'table1'
    union all
    select * from table2 where @tablename = 'table2' 
    union all
    select * from table3 where @tablename = 'table3'
    ... etc ...
    )

select ...
from source

This avoids dynamic sql with its issues but requires you to know all the table names in advance. It might be a good trade off for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources