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
 General SQL Server Forums
 New to SQL Server Programming
 how to select different column!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 12/31/2012 :  13:34:41  Show Profile  Reply with Quote
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
2869 Posts

Posted - 12/31/2012 :  14:16:05  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 12/31/2012 :  14:17:51  Show Profile  Reply with Quote
That's not going to work because I have about 200 Clients!


--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/31/2012 :  14:28:54  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 12/31/2012 :  14:56:12  Show Profile  Reply with Quote
yes! that's a possible way of doing this. it sounds like the right solutions

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/31/2012 :  15:19:08  Show Profile  Reply with Quote
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
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 01/01/2013 :  15:14:14  Show Profile  Reply with Quote
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.
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.12 seconds. Powered By: Snitz Forums 2000