SQL Server - Insert one record once a month using a sp

Asked By Daniel on 19-Jun-12 02:54 PM
Earn up to 10 extra points for answering this tough question.
Hi! I have 2 tables 

CREATE TABLE Fund (
    [FundID] INT IDENTITY(1,1) PRIMARY KEY
    , [Amount] INT
)

CREATE TABLE Transaction (
    [TransactionID] INT IDENTITY(1,1) PRIMARY KEY
    , [TransactionType] NVARCHAR(255)
   ,[Date] DATETIME
   , [FundID] INT
    )


As you can see this tables one is the parents and another is the child.
What I need is just to insert using a stored procedure many records but the stored procedure must have a control that shoul not accept duplicate [FundID] to make a transaction in same month.It can be inserted many but once per each month.Means when I am inserting a in a single month records  in the transaction Table the stored procedure should first verify if the fundID is not already been inserted.
Any Idea please I dont know where to start from.
Thanks!
S K replied to Daniel on 19-Jun-12 11:59 PM
Best way is you can use Window scheduler or Sql Job to run the stored proc once in a month and in that sp you can insert one record which you want. So in that case the sp inserted only one record in a month.
Daniel replied to S K on 20-Jun-12 12:07 AM
Sorry may be I am explain it in wrong way...I wanted to say that the stored procedure can insert many row but should control the foreign key  if it is not inserted twice in a single month.
TSN ... replied to Daniel on 20-Jun-12 01:13 AM

Here is the stored procedure that doesn’t allow you multiple fund id in the Same month.

Try this

Create procedure MyProcedure1

(

@ myDate Datetime,

@FundID int,

@TransactionType nvarchar(50)

)

As Begin

If Exist(select * from Transaction where FundID=@FundId and (YEAR(Date) = YEAR(myDate) AND MONTH(Date) = MONTH(@myDate)) )

Begin

Print Record for the Fund ID Exists in the Current Month

End

Else

begin

Insert into Transaction(TransactionType,Date,FundId) values(@TransactionType,@myDate,@FundId)

End

Try this and let me know any issues

Chintan Vaghela replied to Daniel on 29-Jun-12 01:17 AM

Hi Frndz,

 

Functionality:  Check Month Exist

 

 

To achieve this task,

 

Make one function that  check month and fundid record exist or not.

 

Check VerifyExistData function

 

After then make one SP and pass paramerter Fundid, TransactionType

 

Now check Exist month for particular ID or not

 

SET @Flag = dbo.VerifyExistData(@FundID)

 

Check below function and SP

 

 

 

Full Logic     :

 

Function for check Month Record Exist or not

 

CREATE FUNCTION [dbo].[VerifyExistData]

(

  @FundID INT

)

RETURNS BIT

AS

    BEGIN

        DECLARE @rtnValue as BIT = 1

        IF EXISTS

        (

          Select

              [Date]

          from

              [Transaction]

          where [FundID] = @FundID

              AND DatePart(month, [Date]) = DATEPART(month,getdate())

              AND DatePart(year, [Date]) = DATEPART(YEAR,getdate())

             

        )

        BEGIN

          SET @rtnValue = 0;

        END

        RETURN  @rtnValue ;

    END

 

 

Stored Procedure for Insert Record into Transaction Table

 

CREATE PROCEDURE [dbo].[InsertTransaction]

    @FundID AS INT,

    @TransactionType AS Varchar(255)

AS

 

Declare @Flag  AS BIT

 

SET @Flag = dbo.VerifyExistData(@FundID)

 

IF (@Flag = 1)

BEGIN

    INSERT INTO [Transaction](FundID,TransactionType,[Date]) values (@FundID,@TransactionType,GETDATE())

END

 

 

Hope this helpful!

Thanks