Create dynamic stored procedure

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

Create dynamic stored procedure

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-10-16 : 03:29:05

hi prof,

Let me explain what i want to do 1st.

i have 2 table.

table1 i allow user to upload their forecast data start from the following month. My fiscal year is start from month 4,5,6....11,12,1,2,3.

For example now is 2009, Oct. So user only allow to do forecast starting from 2009 Nov.

Example user upload data as like table1.
After get approval, then i need to update table2 with only update starting from 2009 Nov - 2010 March and insert for those not in table2, because i need to keep the previous record which user upload before 2009 Oct as like table1(after update table1)

i have 2 table as below :

table1
batchid cust_id year 4 5 6 7 8 9 10 11 12 1 2 3 (month)
-----------------------------------------------
id1 custid1 2009 0 0 0 0 0 0 0 11 11 11 0 0
id1 custid2 2009 0 0 0 0 0 0 0 12 12 12 0 0
id1 custid3 2009 0 0 0 0 0 0 0 14 14 14 0 9
id1 custid4 2009 0 0 0 0 0 0 0 17 17 17 8 9

table2
cust_id year 4 5 6 7 8 9 10 11 12 1 2 3 (month)
-------------------------------------------------------
custid1 2009 0 0 3 3 3 3 0 0 0 0 0 0
custid2 2009 0 0 4 4 4 4 0 0 0 0 0 0

After run the store procedure, table2 data should as below.

table2 (after updated)
cust_id year 4 5 6 7 8 9 10 11 12 1 2 3 (month)
-------------------------------------------------------
custid1 2009 0 0 3 3 3 3 0 11 11 11 0 0
custid2 2009 0 0 4 4 4 4 0 12 12 12 0 0
custid3 2009 0 0 0 0 0 0 0 14 14 14 0 9
custid4 2009 0 0 0 0 0 0 0 17 17 17 8 9

My planning is define an array of fiscal year which starting April
1.declare arraymonth {'4','',''........''12','1','2',3',}
2.Check the month and the year now from sql server.
3.loop from the array and look for existing cust_ID which i pass parameter from my coding,
4. If exist, then update the following month until March of 2009.
4 else Insert as a new row data.

Can anyone told me how to write such store procedure??

Thanks........ very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-16 : 06:01:54

[code]
UPDATE t2
SET [4] = CASE WHEN t1.[4] <> 0 THEN t1.[4] ELSE t2.[4] END,
[5] = CASE WHEN t1.[5] <> 0 THEN t1.[5] ELSE t2.[5] END,
. . .
FROM table2 t2 INNER JOIN table1 t1
ON t2.custid = t1.cust_id
AND t2.year = t1.year
WHERE t2.[4] <> CASE WHEN t1.[4] <> 0 THEN t1.[4] ELSE t2.[4] END
OR t2.[5] <> CASE WHEN t1.[5] <> 0 THEN t1.[5] ELSE t2.[5] END
OR . . .

INSERT INTO table2 (custid, year, [4], [5], . . . )
SELECT custid, year, [4], [5], . . .
FROM table1 t1
WHERE NOT EXISTS
(
SELECT *
FROM table2 t2
WHERE t2.custid = t1.custid
AND t2.year = t1.year
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-10-20 : 03:19:04

Hi Khtan,

Thank you very very much.
And this is what want.

May i know that can i put both sql statement in one stored procedured?? It look like not working when i put both statement.

Please advice.

quote:

Originally posted by khtan


UPDATE t2
SET    [4] = CASE WHEN t1.[4] <> 0 THEN t1.[4] ELSE t2.[4] END,
       [5] = CASE WHEN t1.[5] <> 0 THEN t1.[5] ELSE t2.[5] END,
       . . . 
FROM   table2 t2 INNER JOIN table1 t1
       ON  t2.custid = t1.cust_id
       AND t2.year   = t1.year
WHERE  t2.[4] <> CASE WHEN t1.[4] <> 0 THEN t1.[4] ELSE t2.[4] END
OR     t2.[5] <> CASE WHEN t1.[5] <> 0 THEN t1.[5] ELSE t2.[5] END
OR     . . . 

INSERT INTO table2 (custid, year, [4], [5], . . . )
SELECT custid, year, [4], [5], . . . 
FROM   table1 t1
WHERE  NOT EXISTS
       (
            SELECT *
            FROM   table2 t2
            WHERE  t2.custid = t1.custid
            AND    t2.year   = t1.year
       )

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-20 : 04:00:18

quote:
May i know that can i put both sql statement in one stored procedured??

Yes you can.

quote:
It look like not working when i put both statement.

How is it not working ? You encounter error ? It won't execute ? result is wrong ?

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-10-20 : 04:27:23

Sorry, KhTan,

Is my parameter length not long enough.
It solve.

Thank you very much

quote:
Originally posted by khtan

quote:
May i know that can i put both sql statement in one stored procedured??

Yes you can.
quote:
It look like not working when i put both statement.

How is it not working ? You encounter error ? It won't execute ? result is wrong ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-20 : 04:31:29

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-10-21 : 06:03:11

Sorry, Khtan

can we only update those after this month data only?
I mean example now alredy Oct, can we only update those data after this month like only Nov, Dec, Jan, Feb and March 2009?
But is the data not exist then we enter the whole slot?

quote:
Originally posted by khtan

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 06:55:57

add a where clause


where year >= datepart(year, getdate())

and for the updating of column, add another condition to check for the month


SET  [4] = CASE WHEN t1.[4] <> 0 AND datepart(month, getdate()) > 4
                THEN t1.[4] 
                ELSE t2.[4] 
                END

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-10-21 : 21:23:16

Hi khtan,

Thanks, i try 1st, then let u know again.
Thanks your immediate respond.

Sorry khtan,
may be i provide wrong infor at my last posting.

Correct my requirement here,

My fiscal year is from April to next year March.
Mean from April 2009 to March 2010 counted as yearly data.

Example now already Oct, can i only update [11],[12] for 2009 until
[1],[2] and [3] 2010 only?

quote:
Originally posted by khtan

add a where clause

where year >= datepart(year, getdate())

and for the updating of column, add another condition to check for the month

SET [4] = CASE WHEN t1.[4] <> 0 AND datepart(month, getdate()) > 4
THEN t1.[4]
ELSE t2.[4]
END

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-22 : 01:34:40

[code]
DECLARE @fy_st datetime,
@fy_en datetime,
@today datetime

SELECT @fy_st = '2009-04-01',
@fy_en = '2009-03-31',
@today = GETDATE()

UPDATE t2
SET [4] = CASE WHEN t1.[4] <> 0
AND (
( t2.YEAR = DATEPART(YEAR, @today) AND DATEPART(MONTH, @today) <= 4 )
OR ( t2.YEAR = DATEPART(YEAR, @fy_en) AND DATEPART(MONTH, @fy_en) >= 4 )
)
THEN t1.[4]
ELSE t2.[4] END,
[5] = CASE WHEN t1.[5] <> 0
AND (
( t2.YEAR = DATEPART(YEAR, @today) AND DATEPART(MONTH, @today) <= 5 )
OR ( t2.YEAR = DATEPART(YEAR, @fy_en) AND DATEPART(MONTH, @fy_en) >= 5 )
)
THEN t1.[5]
ELSE t2.[5]
END,
. . .
FROM table2 t2 INNER JOIN table1 t1
ON t2.custid = t1.cust_id
AND t2.YEAR = t1.YEAR
WHERE t2.YEAR >= DATEPART(YEAR, @today)
AND t2.YEAR <= DATEPART(YEAR, @fy_en)
(
t2.[4] <> CASE WHEN t1.[4] <> 0 THEN t1.[4] ELSE t2.[4] END
OR t2.[5] <> CASE WHEN t1.[5] <> 0 THEN t1.[5] ELSE t2.[5] END
OR . . .
)

INSERT INTO table2 (custid, YEAR, [4], [5], . . . )
SELECT custid, YEAR, [4], [5], . . .
FROM table1 t1
WHERE NOT EXISTS
(
SELECT *
FROM table2 t2
WHERE t2.custid = t1.custid
AND t2.YEAR = t1.YEAR
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-10-23 : 02:29:21

Hi Khtan,

I solve it based on your last posting,
Thanks you khtan.
I actually allow user to update as long as the month is after this month until March 2009, even zero value.
The data after march 2009 is belong to April 2010.
i will check on the fiscal year that user uploaded.

Finally i create the stored procedured as below.

---------------------------------------------------------------

CREATE PROCEDURE [dbo].[st_PROCEDURE]

@RET_VAL INTEGER OUTPUT,
@CMD VARCHAR(1),
@BATCHID varchar(20)

AS

Declare @ERR_NO INT
Set @ERR_NO] = 0
Declare @month INT
if datepart(month, getdate()) = 1 or datepart(month, getdate()) = 2 or datepart(month, getdate()) = 3
Set @month = datepart(month, getdate()) + 12
else
Set @month = datepart(month, getdate())

Begin

UPDATE t2
SET
/*[4] = CASE WHEN t1.[4] <> 0 or @month < 4 THEN t1.[4] ELSE t2.[4] END,*/
[4] = CASE WHEN @month < 4 THEN t1.[4] ELSE t2.[4] END,
[5] = CASE WHEN @month < 5 THEN t1.[5] ELSE t2.[5] END,
[6] = CASE WHEN @month < 6 THEN t1.[6] ELSE t2.[6] END,
[7] = CASE WHEN @month < 7 THEN t1.[7] ELSE t2.[7] END,

= CASE WHEN @month < 8 THEN t1.

ELSE t2.

END,
[9] = CASE WHEN @month < 9 THEN t1.[9] ELSE t2.[9] END,
[10] = CASE WHEN @month < 10 THEN t1.[10] ELSE t2.[10] END,
[11] = CASE WHEN @month < 11 THEN t1.[11] ELSE t2.[11] END,
[12] = CASE WHEN @month < 12 THEN t1.[12] ELSE t2.[12] END,
[1] = CASE WHEN @month < 13 THEN t1.[1] ELSE t2.[1] END,
[2] = CASE WHEN @month < 14 THEN t1.[2] ELSE t2.[2] END,
[3] = CASE WHEN @month < 15 THEN t1.[3] ELSE t2.[3] END

FROM Table2 t2 INNER JOIN Table1 t1
ON t2.CUSTCODE] = t1.CUSTCODE
AND t2.FISCALYR = t1.FISCALYR
AND t2.COMMODITYCODE = t1.COMMODITYCODE

WHERE

t1.Batchid = @BATCHID

INSERT INTO Table2 (TRANSDATE,CUSTCODE,SPCODE,COMMODITYCODE,COMMODITYNAME,FISCALYR],[4],[5],[6],[7],

,[9],[10],[11],[12],[1],[2],[3]
)
SELECT TRANSDATE,CUSTCODE,SPCODE,COMMODITYCODE,COMMODITYNAME,FISCALYR],[4],[5],[6],[7],

,[9],[10],[11],[12],[1],[2],[3]
FROM Table1 t1
WHERE t1.Batchid] = @BATCHID AND NOT EXISTS
(
SELECT *
FROM Table2 t2
WHERE t2.CUSTCODE = t1.CUSTCODE
AND t2.FISCALYR = t1.FISCALYR
AND t2.COMMODITYCODE = t1.COMMODITYCODE
)

Set @RET_VAL = @ERR_NO

End

---------------------------------------------------------------

quote:

Originally posted by khtan


DECLARE @fy_st	datetime,
	@fy_en	datetime,
	@today	datetime
	
SELECT	@fy_st 	= '2009-04-01',
	@fy_en	= '2009-03-31',
	@today	= GETDATE()

UPDATE 	t2
SET    	[4] = CASE WHEN t1.[4] <> 0 
		   AND  (
		   		(	t2.YEAR = DATEPART(YEAR, @today) AND	DATEPART(MONTH, @today) <= 4	)
		   	OR	(	t2.YEAR = DATEPART(YEAR, @fy_en) AND 	DATEPART(MONTH, @fy_en) >= 4	)
		   	)
		   THEN t1.[4] 
		   ELSE t2.[4] END,
       	[5] = CASE WHEN t1.[5] <> 0 
		   AND  (
		   		(	t2.YEAR = DATEPART(YEAR, @today) AND	DATEPART(MONTH, @today) <= 5	)
		   	OR	(	t2.YEAR = DATEPART(YEAR, @fy_en) AND 	DATEPART(MONTH, @fy_en) >= 5	)
		   	)
       		   THEN t1.[5] 
       		   ELSE t2.[5] 
       		   END,
       	. . . 
FROM   	table2 t2 INNER JOIN table1 t1
       	ON  t2.custid = t1.cust_id
       	AND t2.YEAR   = t1.YEAR
WHERE  	t2.YEAR	>= DATEPART(YEAR, @today)
AND	t2.YEAR	<= DATEPART(YEAR, @fy_en)
	(
	t2.[4] <> CASE WHEN t1.[4] <> 0 THEN t1.[4] ELSE t2.[4] END
OR     	t2.[5] <> CASE WHEN t1.[5] <> 0 THEN t1.[5] ELSE t2.[5] END
OR     	. . . 
	)

INSERT INTO table2 (custid, YEAR, [4], [5], . . . )
SELECT custid, YEAR, [4], [5], . . . 
FROM   table1 t1
WHERE  NOT EXISTS
       (
            SELECT *
            FROM   table2 t2
            WHERE  t2.custid = t1.custid
            AND    t2.YEAR   = t1.YEAR
       )

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-23 : 02:41:53

Good. Any reason your table is not normalized ?

You can make things much easier if the table is normalized.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources