Dynamically select @SelectCol - SQL Server Forums

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

Transact-SQL (2000)

Dynamically select @SelectCol

Author

Topic

swanagetown
Starting Member

19 Posts

Posted - 2005-04-01 : 12:41:30

Hi,

I need to select a one column (from 25 columns) at runtime and was wondering what the best way of doing this. I'm currently doing something like this:

CREATE PROCEDURE dbo.SelectCol
(@SortOrder VarChar(20), @SelectCol VarChar(255))
AS

SELECT mrates.Scheme, mrates.Code, (CASE WHEN @SelectCol = 'Col1' THEN valuation.[col1] WHEN @SelectCol = 'Col2' THEN valuation.[col2] WHEN @SelectCol = 'Col3' THEN valuation.[col3] WHEN @SelectCol = 'Col4' THEN valuation.[col4]etc... upto col25 END) AS Expr1
FROM mrates INNER JOIN
valuation ON mrates.ProID = valuation.ProdID
WHERE Blablabla... ORDER BY (Case When @SortOrder = 'Bins' Then mrates.Bins etc... End
GO

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-01 : 12:45:34

Can you tell us why?

2 things....

1st you'll have to worry about datatype conversion issues...

2nd, not that I'm reccommending it, but this is a job for dynamic sql...

But...you gotta let us know the business reason behind this...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-01 : 12:46:19

why not return all the columns and have the presenation layer pick the ones it needs? What the examples of the columns that you are picking from? Is your database properly normalized?

- Jeff

swanagetown
Starting Member

19 Posts

Posted - 2005-04-02 : 07:43:55

OK Thanks I'll explain further (sorry a bit long)

It's for a mortgage comparison site. There are 2 sql tables.

TABLE 1:

SchemeID (int 4 autoincrement): 1, 2, 3 etc...
ProductCode (varchar 8): i.e asdf543f etc...
Lender (varchar 25): i.e Chase, xyz Bank etc...
Initial Interest Rate(IR) (Decimal 5, 3, 2): i.e 2.50, 3.25 etc...
(IR)Type (Varchar 20): f or d or c or t
(IR) DurationMonths (tinyInt 1): 24, 36 etc...
(IR) DurationYears (smalldatetime 4): 31/10/2008, 01/12/2010 etc...
Rest (varchar 1): a or m
Standard Interest Rate (Decimal 5, 3, 2): i.e 2.50, 3.25 etc...
APR (Decimal 5, 3, 2): i.e 2.50, 3.25 etc...
Arrangement Fee (numeric 5): i.e 200, 395 etc...
Booking Fee (numeric 5): i.e 200, 395 etc...
Free Valuation (varchar 1): i.e y or n or r
Free Legals (varchar 1): i.e y or n
Redemption Fee (varchar 50): i.e typed text
Final Fee (numeric 5): i.e 200, 395 etc...
Max Loan to Property Value (decimal 5, 3, 2): i.e 0.75, 0.80 etc...
Minumum Loan (numeric 5): i.e 25000, 50000 etc...
Maximum Loan (numeric 5): i.e 200000, 400000 etc...

My stored procedure receives 2 variables: @LoanAmount and @PropertyValue.
I then calculate the loan to value (@LoanAmount / @PropertyValue = i.e 0.67 @LTV) These allow me to to use: SELECT * From TABLE1 WHERE (LTV >= @LTV) AND (Minumum Loan <= @LoanAmount) AND (Maximum Loan >= @LoanAmount)

The column, Free Legals (varchar 1): i.e y or n, in Table1 mean yes or no. Thus I have get the Valuation cost for those schemes where the valuation isn't free. For this I have Table2 which looks like this:

TABLE 2

Lender (varchar 25): i.e Chase, xyz Bank etc...
upto 25000 (smallint): i.e 150, 300 etc...
upto 50000 (smallint)
upto 75000 (smallint)
etc... (there are 36 valuation column bandings)

I read that DynamicSQL is a bad idea so was thinking along these lines:
SELECT TABLE1.*, (CASE WHEN @PropertyValue < 25000 THEN TABLE2.[upto25000] WHEN @PropertyValue < 50000 THEN TABLE2.[upto50000] etc.... END) AS Valuation
FROM TABLE1 INNER JOIN
TABLE2 ON TABLE1.Lender = TABLE2.Lender
WHERE (LTV >= @LTV) AND (Minumum Loan <= @LoanAmount) AND (Maximum Loan >= @LoanAmount)
Go

Thanks for your help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-02 : 07:50:58

quote:
there are 36 valuation column bandings

This sounds like the crux of the problem, easily solved by normalizing the table so that these valuations are stored as separate rows instead of separate columns. Doing so eliminates the need for a CASE expression to choose the correct column, and will optimize better.

swanagetown
Starting Member

19 Posts

Posted - 2005-04-02 : 14:48:31

Thanks robvolk, however if I did that how would I do TABLE1.Lender = TABLE2.Lender? It's the Lender that's linking the 2 tables.

Lender - to25000 - to50000 - to75000 etc...(36 cols in total)
Bank a - 130 - 140 - 150
Bank b - 90 - 110 - 135
Bank c - 115 - 135 - 145
Bank d - 145 - 160 - 190
Bank e - 130 - 160 - 180
etc...
(about 40 banks)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-02 : 17:23:22

CREATE TABLE LenderValuations(Lender varchar(25) NOT NULL, Band int NOT NULL, Valuation smallint NOT NULL)

INSERT LenderValuations SELECT 'Chase', 25000, 130
INSERT LenderValuations SELECT 'Chase', 50000, 140
INSERT LenderValuations SELECT 'Chase', 75000, 150
...etc.

SELECT A.*, B.Valuation
FROM Table1 A
INNER JOIN LenderValuations B ON A.Lender=B.Lender
WHERE B.Valuation=(SELECT Min(Band) FROM LenderValuations WHERE Lender=B.Lender AND Band>=@PropertyValue)

The last query may not be all that efficient, but it should get the job done. The nice thing about this structure is that you can have bands of any increment, not fixed like the multiple-column structure requires.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources