Joining onto a table specified as a parameter into a Stored Procedure?

Question

Joining onto a table specified as a parameter into a Stored Procedure?

themancorp

SSC Veteran

Points: 268
More actions
October 14, 2009 at 8:08 am

#227129

Does anyone know whether it’s possible to join onto a table inside a stored procedure using a parameter to specify the table to join onto?
I.e.
…
FROM DBA.CustTrans_TB
JOIN @strMyJoinView AS MJV
…
..where the @strMyJoinView value is passed in as a parameter (with a value such as “DBA.TableName_TB”).
When I try running the SP above, it complains that it cannot find the table “@strMyJoinView”!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

It's looking for a table variable in that case. That's why you're getting the specific error.

Yes, you can do this, but what you have to do is build the query as a string, then execute it as dynamic SQL. Easy enough to do, but making it secure and safe is more complex.

-- Change to Input Param in proc

declare @TableName varchar(100);

select @TableName = 'MyTable';

-- Body of proc

set nocount on;

declare @TableChecked varchar(100);

select @TableChecked = '[' + name + ']'

from sys.tables

where name = @TableName;

declare @DynamicSQL varchar(max);

select @DynamicSQL = 'select * from ' + @TableChecked;

exec(@DynamicSQL);

This takes a table name as an input parameter, checks to make sure it really is a table name, and then builds a query string with it.

By using the sys.tables query, you largely eliminate the possibility of SQL injection.

By placing brackets around it, you make it so it can deal with names with special characters in them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sergiy SSC Guru Points: 110209 More actions · Answer 2

GSquared (10/14/2009)
declare @TableChecked varchar(100);
select @TableChecked = '[' + name + ']'
from sys.tables
where name = @TableName;
declare @DynamicSQL varchar(max);
select @DynamicSQL = 'select * from ' + @TableChecked;
exec(@DynamicSQL);[/code]

I know, normal people won't do this, but anyway it's not a good way to build a potential error in a code.

GSquared, your code will fail in this case:

declare @TableName varchar(100);

select @TableName = 'My[Freaky]Table';

It's not so difficult to make it bullet-proof:

select @TableChecked = QUOTENAME(name, '[')

from sys.tables

where name = @TableName

_____________
Code for TallyGenerator

themancorp SSC Veteran Points: 268 More actions · Answer 3

Thanks guys for the replies. Much appreciated! 🙂