August 10, 2011 at 12:19 pm
I have 3 input parameters and I want to run different peice of code for all 3 different parameters...
For example I want to group by values according to datepart(mm,parameter1) for parameter 1 and datepart(dd,parameter2) and so on...
How can I write this code. I tried using CASE statement but having errors
CREATE PROCEDURE [dbo].[myproc]
(
@value_in_tens int,
@valuein_hundred int,
@value_in_thousand int
)
AS
BEGIN
SET NOCOUNT ON;
insert into dbo.table1_data
(
id,
value,
date,
period
)
SELECT
id,
avg(value),
@timestamp,
3 cases: WHEN @value_in_tens THEN @value_in_tens
WHEN @value_in_hundred THEN @value_in_hundred
WHEN @value_in_thousand THEN @value_in_thousand
FROM dbo.table1_data
group by prf_cnt_counter_id, 3 different cases: WHEN @value_in_tens THEN datepart(mm, @value_in_tens)
WHEN @value_in_hundred THEN datepart(dd,@value_in_hundred)
WHEN @value_in_thousand THEN datepart(yy,@value_in_thousand)
August 10, 2011 at 12:36 pm
I'm not sure if I understand the concept. So let me ask: why do you have three parameter instead of just one?
Based on the description it should be easy to figure that a value of 20 would be @value_in_tens and 200 would match @valuein_hundred.
The second question would be: what is the general purpose of this code? It seems like it's supposed to perform some date calculation. Would you mind explainig what you're intended to do? There might be easier ways than the one described...
Also, within a GROUP BY each expression must include a column. In the given scenario it would be to group by a variable only. That doesn't make sense.
August 10, 2011 at 12:46 pm
Let us say when I exec stored proc
EXEC myproc (32,442,5000)
I want the code to insert into table 3 times one having period value 32.
2nd insert having period value 442 and 3rd insert having period value 5000
Same time I want to groupby in 1st case 32---> groupby datepart(mm,mydate)
2nd case 442 ---> groupby datepart(hh,mydate)
3rd case 5000 ----> groupby datepart(dd,mydate)
Sorry for the incorrect groupby clause earlier
August 10, 2011 at 1:06 pm
if you want to insert three "groups" of data you should use three insert statements, each one with its own group by clause.
There might be a way to build one insert statement but I would need some sample data as well as expected results in a ready to use format as described in the first link in my signature.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply