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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 optional table name parameters in stored procedure
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
36 Posts

Posted - 07/05/2013 :  07:09:10  Show Profile  Reply with Quote

I have a stored procedure using dynamic sql doing a simple inner join on 2 tables. I use dynamic variable table names as the table names will be passed from the user application and will change according to user request. The stored procedure below works if the number of table name parameters is always set to 2 but the number of joins between tables should also vary according to user request. So I would like to know if its possible to make the table name parameters in the stored procedure optional.
I know this is possible if I was using variables for the arguments in a where clause use a case statement.

here is my current stored procedure


Create procedure sp_OptionalParameters
@Table1 varchar(128),
@Table2 varchar(128)


declare @sql varchar(4000)

select @sql = ' 
	 SELECT     MySchema.[' + @Table1 + '].Column1, 
	 MySchema.[' + @Table1 + '].Column2, 
	 MySchema.[' + @Table1 + '].Column3, 
	 MySchema.[' + @Table1 + '].[Column4], 
FROM         MySchema.[' + @Table1 + ']
INNER JOIN MySchema.[' + @Table2 + '] ON MySchema.[' + @Table1 + '].Column1 = MySchema.[' + @Table2 + '].Column1 

	exec (@sql)

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 07/05/2013 :  07:21:21  Show Profile  Reply with Quote
First of all I didnt understand why you need to pass the table names as parameters. why cant they be static? Why should you give users control over what table to be used? who are these users actually? are they sql dev/dbs people or "end users"? Normally users need to only worry on data inputs that are sent as parameters and logic will be written to retrieve them data they want based on inputs. Tables will be designed beforehand with fixed structure.
Please elaborate on your exact scenario for us to understand need of passing object names as parameters

SQL Server MVP
Go to Top of Page

Constraint Violating Yak Guru

352 Posts

Posted - 07/05/2013 :  07:54:04  Show Profile  Reply with Quote
I might suggest you use alias values as in SELECT A.Column1, A.Column2 ...

What you would need for your problem is to split the SET @sql statement such that you could put in logic (if or case).

That said, visakh16 has a very good point.

Go to Top of Page

Starting Member

United Kingdom
36 Posts

Posted - 07/05/2013 :  09:41:25  Show Profile  Reply with Quote
Thanks for your replies. visakh16 has got me thinking that maybe I can normalize my database first and approach the problem in a different way.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000