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.
Author |
Topic |
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-12-31 : 13:34:41
|
Hi all! I have 3 tables for 3 different clients; T1, T2 and T3.they all have FName, LName and Address1 columns. However, the column names is different from one table to the other.ONE thing i'm sure of is the Order is always the same.What I need: is to create a stored proc that knows how to select by just passing the TableName as Parameter!!is this possible?Thanks!T1 --> FName, LName and AddressT2 --> FirstName, LastName and addrT3 --> FN, LN and location--------------------------Joins are what RDBMS's do for a living |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-31 : 14:16:05
|
The best way to do this is with three different selct statementsIF @tableName = 'T1'SELECT FName, LName , AddressFROM t1IF @tableName = 'T2SELECT FirstName, LastName ,addrFROM T2etc.JimEveryday I learn something that somebody else already knew |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-12-31 : 14:17:51
|
That's not going to work because I have about 200 Clients!--------------------------Joins are what RDBMS's do for a living |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-31 : 14:28:54
|
And my guess is that you want SQL to dynamically create the correct SELECT statement based on the table name, where you don't have to manually create each one?JimEveryday I learn something that somebody else already knew |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-12-31 : 14:56:12
|
yes! that's a possible way of doing this. it sounds like the right solutions--------------------------Joins are what RDBMS's do for a living |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-31 : 15:19:08
|
I don't like this one bit, and I'm sure the SQL pros here will flambaste it. but here ya goDECLARE @tableName varchar(50) = 'tblACSSRegion' DECLARE @Select varchar(100)SET @Select = 'Select ' + (SELECT STUFF( ( SELECT ',' + Column_Name FROM Information_Schema.Columns WHERE ordinal_Position < 4 -- this just assumes that the desired columns are the first 3 AND TABLE_NAME = @TableName for xml path('') ),1,1,''))+ ' FROM ' + @tableName EXEC (@SQL)JimEveryday I learn something that somebody else already knew |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-01-01 : 15:14:14
|
Here's another approach that might work for you. It's dependent on the underlying tables not changing and you should use it at your own peril. I'd also consider revisiting the entirety of your design but sometimes you get what you get. So, here goes.I'm assuming your tables have only 3 columns for this example. If they have more, and surely they do, just adjust the table design to so that the needed columns fit within your design.CREATE PROCEDURE junk @tablename nvarchar(50)ASBEGIN CREATE TABLE #Temp ( fname nvarchar(50), lname nvarchar(50), address nvarchar(50) ); exec('INSERT INTO #Temp SELECT * FROM ' + @tablename); SELECT fname, lname, address FROM #TempEND;goexec junk '<your table name>'It not elegant but it's simple and lets you work with consistent column names. |
|
|
|
|
|
|
|