How to use this procedure - 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

General SQL Server Forums

Database Design and Application Architecture

How to use this procedure

Author

Topic

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-20 : 02:58:44

Hai,

I used some calculation for all id. After this calculation all value insert into table.

But this is each day inserted once and remaining times its update.
I knw how to insert.But i dont knw how to check for insert or update.

This is my procedure

ALTER procedure [dbo].[insert_cutoff]
@memberid varchar(11),
@LeftAmt money,
@RightAmt money,
@Cutoff money,
@TDS money,
@SC money,
@Total money,
@Date varchar(20),
@camt money,
@pleg varchar(10)
as
begin
declare @datechk varchar(20)

select @datechk=Date from Cutoff where MemberId=@memberid

if @datechk!=@Date or @datechk='' or @datechk is null
--if @datechk != @Date

insert into cutoff(MemberId,LeftAmt,RightAmt,Cutoffpay,TDS,SC,Total,Date,CarryAmt,PowerLeg) values(@memberid,@LeftAmt,@RightAmt,@Cutoff,@TDS,@SC,@total,@Date,@camt,@pleg)

--else
update Cutoff set LeftAmt=@LeftAmt,RightAmt=@RightAmt,CutoffPay=@Cutoff,TDS=@TDS,SC=@SC,Total=@Total,CarryAmt=@camt,PowerLeg=@pleg where MemberId=@memberid and Date=@Date
end

....

This procedure not work correct format. This procedure executed where top up is done. Each day allowed lot of topup to id.

Kindly help me.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 04:02:22

I dont understand your requirement. Please post some sample data and explain what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-20 : 20:44:15

Thank you. I resolved this issue.

I have other issue. I want to select information from table between from 20th date to 30th date. using date only not month or year.

I used this querey

select MemberId, PlanAmt,Total,Date,ClosedDate, DAY(date) as du from Plan_Payment where du between '20' and '30'

I got error in 'du' is invalid column.

Can i use this format. or not. Kindly tel me how to reframe it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-20 : 21:03:49

you can't use the column alias in the WHERE clause

where du between '20' and '30'
where DAY(date) between 20 and 30

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

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-20 : 22:42:20

Thank you so much. It work well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 01:15:40

otherwise you need to do like this


select *
from
(
select MemberId, PlanAmt,Total,Date,ClosedDate, DAY(date) as du 
from Plan_Payment 
)t
where du between '20' and '30'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-11-21 : 05:51:08

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources