SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

MrBloom
Starting Member

United Kingdom
34 Posts

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


Hi.
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

thanks




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

as


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)
GO


visakh16
Very Important crosS Applying yaK Herder

India
52325 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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 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.

djj
Go to Top of Page

MrBloom
Starting Member

United Kingdom
34 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  
 New 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