how to select different column!!

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

General SQL Server Forums

New to SQL Server Programming

how to select different column!!

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 Address
T2 --> FirstName, LastName and addr
T3 --> 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 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

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?

Jim

Everyday 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 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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources