SQL SERVER Stored Procedure

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

I want to create a stored procedure in SQL Server2012

well thats good,,, i have wrote many stored procedures to work this problem out but none of them worked

the stored procedure has to do the following

Select all rows of a coloumn(date as data type) if any one of the row matches the current datetimestamp

then select id of that row whoose date is current date and then perform some other operations

I need this stored procedure to run daily

Urgent help needed

Posted 9-Apr-14 14:26pm

Srikanth545545

Updated 9-Apr-14 14:32pm

v2

Add a Solution

Comments

[no name] 9-Apr-14 20:31pm

No it's not urgent at all. And, in your haste, you completely forgot to tell us the specific problem that you have along with the code that you have tried.

Srikanth545545 9-Apr-14 20:34pm

I have tried to write procedures but i dont have a problem in execution
i have a problem in implementing this logic

[no name] 9-Apr-14 20:37pm

And? How does that help? What is the input? What is the output you expect? What does "some other operations" mean? What have you, yourself tried? Where is the code that you have tried? What is the problem that you have with your code? You need to tell us this stuff as we cannot see your code, your hard drive or read your mind.

Srikanth545545 9-Apr-14 20:44pm

Create Procedure sample
@mdt date,
@amnt numeric(18,0),
@per numeric(18,0),
@acnum numeric(18,0),
@invid numeric(18,0)
as
select @mdt=OnlineBankingServer.dbo.Fixed_Inv.Mdate from OnlineBankingServer.dbo.Fixed_Inv
while @mdt is not null
begin
if @mdt=CURRENT_TIMESTAMP
begin
update OnlineBankingServer.dbo.Cust_Details set accnt_bal=accnt_bal+((@amnt*@per)/100) where account_num=@acnum
delete from OnlineBankingServer.dbo.Fixed_Inv where Inv_ID=@invid
insert into OnlineBankingServer.dbo.Transactions values(@acnum,1234,'invd',@amnt,CURRENT_TIMESTAMP)
end
end

go

this was the code i have written

I want to select Mdate from Fix_Inv table
loop through
if any Mdate row matches today's date
then select ID of that row from Fix_Inv
then do the operations

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Answer 1 · 2014-04-09T22:30:00

Solution 1

Of course no record would match exactly the current timestamp. You have to use just the date part of the timestamp itself.

Posted 9-Apr-14 22:30pm

CPallini

Comments

ashok rathod 10-Apr-14 4:34am

Current Timestamp will not match that much records thats why you can check with date part

Andrius Leonavicius · Answer 2 · 2014-04-09T22:37:00

Hi,

If you need to loop through records, why not use a cursor? And if you want to compare with today's date (without time), you need to get rid of the time part from CURRENT_TIMESTAMP.

If you're looping through records and selecting Mdate and Inv_ID from that row, then why do you need @mdt date and @invid numeric(18,0) as input parameters?

Example (according to your given code; simplified):

SQL

CREATE PROCEDURE sample (@amnt  NUMERIC(18, 0),
                        @per   NUMERIC(18, 0),
                        @acnum NUMERIC(18, 0))
AS
  BEGIN
      DECLARE @invid NUMERIC(18, 0);
      DECLARE MyCursor CURSOR FAST_FORWARD FOR
        -- select Inv_IDs where Mdate matches today's date
        SELECT Inv_ID FROM OnlineBankingServer.dbo.Fixed_Inv WHERE CONVERT (DATE, Mdate) = CONVERT (DATE, CURRENT_TIMESTAMP)
 
      OPEN MyCursor
 
      FETCH NEXT FROM MyCursor INTO @invid
 
      WHILE @@FETCH_STATUS = 0
        BEGIN
            -- your operations
	    UPDATE OnlineBankingServer.dbo.Cust_Details SET accnt_bal = accnt_bal + ( ( @amnt * @per ) / 100 ) WHERE account_num = @acnum
	    DELETE FROM OnlineBankingServer.dbo.Fixed_Inv WHERE Inv_ID = @invid
	    INSERT INTO OnlineBankingServer.dbo.Transactions VALUES (@acnum, 1234, 'invd', @amnt, CURRENT_TIMESTAMP)
 
            FETCH NEXT FROM MyCursor INTO @invid
        END
 
      CLOSE MyCursor
 
      DEALLOCATE MyCursor
  END
 
GO