optional table name parameters in stored procedure

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 2008 Forums

Transact-SQL (2008)

optional table name parameters in stored procedure

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2013-07-05 : 07:09:10

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

52326 Posts

Posted - 2013-07-05 : 07:21:21

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

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-07-05 : 07:54:04

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

MrBloom
Starting Member

36 Posts

Posted - 2013-07-05 : 09:41:25

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources