| Author |
 Topic  |
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
 Posted - 12/31/2012 : 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 Address
T2 --> FirstName, LastName and addr
T3 --> FN, LN and location
--------------------------
Joins are what RDBMS's do for a living |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/31/2012 : 14:16:05
|
The best way to do this is with three different selct statements
IF @tableName = 'T1'
SELECT FName, LName , Address
FROM t1
IF @tableName = 'T2
SELECT FirstName, LastName ,addr
FROM T2
etc.
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
Posted - 12/31/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/31/2012 : 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?
Jim
Everyday I learn something that somebody else already knew |
|
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
Posted - 12/31/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/31/2012 : 15:19:08
|
I don't like this one bit, and I'm sure the SQL pros here will flambaste it. but here ya go
DECLARE @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)
Jim
Everyday I learn something that somebody else already knew |
|
|
|
erikhaselhofer
Starting Member
14 Posts |
Posted - 01/01/2013 : 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)
AS
BEGIN
CREATE TABLE #Temp
(
fname nvarchar(50),
lname nvarchar(50),
address nvarchar(50)
);
exec('INSERT INTO #Temp
SELECT *
FROM ' + @tablename);
SELECT
fname, lname, address
FROM #Temp
END;
go
exec junk '<your table name>'
It not elegant but it's simple and lets you work with consistent column names. |
|
|
| |
Topic |
|
how to select different column!!
