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
 Dynamic Statement Needed???

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 Table
where D10 > midDaypct * 2 and DOW6 > 0
union
select *
from Table
where DOW6 >midDOWpct * 2 and D10 > 0

We 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 table
where (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)
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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?
Go to Top of Page

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 = 10
set @dow = 6

set @sql =
'select *
from Table
where ' + '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)
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -