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 |
Newby
Starting Member
3 Posts |
Posted - 2014-04-23 : 12:19:04
|
I have a stored procedure that populates a table. I want to query the table using column names as variables, however the data is in decimal format so I am getting errors converting varchar to numeric. My table consists of columns labeled D1, D2, etc. for every possible day of any month, DOW1, DOW2, etc. for every day of the week. I also have values labeled midDpct and midDOWpct. My query is as follows:select *from Tablewhere D10 > midDaypct * 2 and DOW6 > 0union select *from Tablewhere DOW6 >midDOWpct * 2 and D10 > 0We are targeting a specific day of the month and a specific day of week, day 10 and day of week 6 in this example. I want to make these variables so we can easily change out the target day and dow. Any suggestions are appreciated. |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-23 : 12:49:14
|
1. define your stored procedure with the parameters: @midDaypct numeric(n, p), -- where n is number of digits and p is number of decimal places @midDOWpct numeric(n, P) -- optionally, use a type of float (This forces a numeric type so that the expressions in the where clause work OK)2. your select could look like:select * from tablewhere (D10 > @midDaypct*2 and DOW6 > 0) or (DOW6 > @midDOWpct * 2 and D10 > 0) (Only one pass through the table instead of two as in your original query) |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-04-23 : 12:50:14
|
My suggestion is to redesign the table structure so that fewer columns and more rows. Perhaps a single [date] instead of 31 columns for days and 7 columns for DOW. From the date column you can quickly and easily derive day of month and day of week using built in datetime functions. Then your queries become much easier.Be One with the OptimizerTG |
|
|
Newby
Starting Member
3 Posts |
Posted - 2014-04-23 : 13:07:54
|
Thank you for your response. I have re-written the query as suggested. I may not have posed my question correctly. Using the suggested select statement, I want to now be able to set the column names in the where clause as variables. So, without changing the query, I want to be able to change the D10 to D30 or DOW6 to DOW1. Is this possible? |
|
|
Newby
Starting Member
3 Posts |
Posted - 2014-04-23 : 15:39:26
|
Not sure if this is the most efficient, but I did get this to work.declare @day int, @dow int, @sql varchar (1000)set @day = 10set @dow = 6set @sql = 'select *from Tablewhere ' + 'days >= 7 and ((D' + cast(@day as varchar) + ' > midDaypct * 2 and DOW' + cast(@dow as varchar) + ' > 0) or (DOW' + cast(@dow as varchar) + ' > midDOWpct * 2 and D' + cast(@day as varchar) + ' > 0))'exec (@sql) |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-24 : 08:22:55
|
TG's suggestion about redoing your table is the way to go. No Dynamic SQL needed in that case! |
|
|
|
|
|
|
|