SQL Stored Procedure Query to generate Invoice No

SP to generate InvoiceNo.

CREATE PROCEDURE [dbo].[SPGenerateInvoiceNo]
 -- Add the parameters for the stored procedure here
 @InvoiceDate datetime,
 @InvoiceNo varchar(10)='' output
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 DECLARE @IncrementNo varchar(6);
    DECLARE @FinancialYear varchar(4);
    DECLARE @LastInvoiceNo varchar(50);
    DECLARE @NextInvoiceNo varchar(50);
 DECLARE @Alphabate VARCHAR(1);
   
   SET @FinancialYear = (SELECT
   CASE WHEN MONTH(@InvoiceDate)>=4 THEN
         SUBSTRING(convert(VARCHAR,YEAR(@InvoiceDate)),3,2)+SUBSTRING(convert(VARCHAR,YEAR(@InvoiceDate)+1),3,2)
   ELSE
   SUBSTRING(convert(VARCHAR,YEAR(@InvoiceDate)-1),3,2)+SUBSTRING(convert(VARCHAR,YEAR(@InvoiceDate)),3,2)
   END);
     
    IF NOT EXISTS(SELECT * FROM [InvoiceSystem].[dbo].[Tbl_Invoice] WHERE InvoiceDate > @InvoiceDate)
  BEGIN
  IF EXISTS(select TOP(1)InvoiceNo from [InvoiceSystem].[dbo].[Tbl_Invoice] order BY InvoiceNo desc)
   BEGIN
   SET @LastInvoiceNo =(select TOP(1)InvoiceNo from [InvoiceSystem].[dbo].[Tbl_Invoice] order BY InvoiceNo desc)
   END
  ELSE
   BEGIN
   SET @LastInvoiceNo = '00000000'
   END
  SET @IncrementNo = (SELECT SUBSTRING(@LastInvoiceNo,5,4));
  SELECT @IncrementNo = RIGHT('0000'+ CONVERT(VARCHAR,convert(INT,@IncrementNo)+1), 4);
  END
    ELSE
  BEGIN
   SET @NextInvoiceNo = (SELECT TOP(1)InvoiceNo FROM [InvoiceSystem].[dbo].[Tbl_Invoice] WHERE InvoiceDate> @InvoiceDate ORDER BY InvoiceNo);
   print(@NextInvoiceNo)
   Declare @len varchar(5);
   SET @len = (select len(@NextInvoiceNo));
   IF (@len <= 8)
   BEGIN
    SET @LastInvoiceNo = (SELECT TOP(1)InvoiceNo FROM [InvoiceSystem].[dbo].[Tbl_Invoice] WHERE InvoiceDate<= @InvoiceDate ORDER BY InvoiceNo DESC);
  
    SET @len = (select len(@LastInvoiceNo));
    IF (@len > 8)
     BEGIN 
     SET @Alphabate = (SELECT char(ascii(SUBSTRING(@LastInvoiceNo,10,1)) +1)); 
     SET @IncrementNo = (SELECT SUBSTRING(@LastInvoiceNo,5,4)+'.'+@Alphabate);
     END
    ELSE
     BEGIN
     IF (@len is null)
      BEGIN
      SET @IncrementNo = '0';
      END
     ELSE
      BEGIN
      SET @IncrementNo = (SELECT SUBSTRING(@LastInvoiceNo,5,4)+'.A');
      END
   
     END
   END
   ELSE
   BEGIN
    SET @IncrementNo=0
   END
  END
 IF (@IncrementNo <> '0')
  BEGIN
   SET @InvoiceNo = (SELECT @FinancialYear + @IncrementNo);
   SELECT @InvoiceNo AS 'InvoiceNo';
  END
 ELSE
  BEGIN
   SELECT '-99' AS 'InvoiceNo';
  END
END

SQL Query for From Date and To Date in Database and Parameter

In this post I am giving example of sql query.

sometimes we need From Date and To Date filter in our application and there is already From Date and To Date fields exist in our Database so how to query it I will show in belowe stored procedure.

Here check the where condition in the code.

CREATE PROCEDURE [dbo].[SPDailyDeviceLog]

@FDate date = NULL,
 @TDate date = NULL
AS
BEGIN

 SET NOCOUNT ON;

 select * from DailyLogs as l
 join Devices as d on d.DeviceID=l.DeviceID
 join Users as u on u.UserID = l.UserID
where ((@FDate BETWEEN convert(Date,l.CheckedInTime) AND convert(Date,l.CheckedOutTime)) OR (@FDate < convert(Date,l.CheckedInTime)) OR (@FDate IS NULL) or (l.CheckedInTime is NULL))
  and ((@TDate BETWEEN convert(Date,l.CheckedInTime) AND convert(Date,l.CheckedOutTime)) OR (@TDate > convert(Date,l.CheckedOutTime)) OR (@TDate IS NULL) or (l.CheckedOutTime is NULL))
   order by l.DailyLogID desc
END

SQL Scripts

To Create Table

CREATE TABLE Users
(
 UserID int primary key identity(1,1),
 UserName varchar(100) NOT NULL,
 Password varchar(100) NOT NULL,
 CreatedDate datetime default getdate(),
 IsActive bit default 1
)

To Add a Column to a Table

ALTER TABLE Users Add Role int Default 2

To Modify a Column to a Table

ALTER TABLE Users ALTER COLUMN Role varchar(50) NULL

To Concatenate and set into variable with airthmatic operation

DECLARE @id varchar(50)
set @id = 'result'+cast(((select COUNT(id) from cash)+1) as varchar(50));
print @id;

Output
result34

Syntex of Temp Table and Cursor in Stored Procedure.

CREATE PROCEDURE SPGetApprovedJobs
 @Customer varchar(100)
AS
BEGIN
 DECLARE @UserName varchar(100)
 CREATE TABLE #TempTable (Job varchar(100))
 DECLARE @MyCursor CURSOR
 SET @MyCursor = CURSOR FAST_FORWARD
 FOR
 select cm.Username from Contact_management as cm join Contact as c on  c.Contact_id = cm.Contact_id
 join Customer_management as cust on cust.Customer_id = c.Customer_id
 where cust.Description = @Customer
 OPEN @MyCursor
 FETCH NEXT FROM @MyCursor
 INTO @UserName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 INSERT INTO #TempTable select Job_sub as Job from Project_management
 where Project_id like '%'+@UserName+'/%'
 FETCH NEXT FROM @MyCursor
 INTO @UserName
 END
 CLOSE @MyCursor
 DEALLOCATE @MyCursor
 select * from #TempTable
 DROP TABLE #TempTable
END

Stored Procedure for the parameter of coma seprated values

-- EXEC SPUploadMultipleJobs 'clco1/2,clco1/6','test.pdf'
-- =============================================
CREATE PROCEDURE [dbo].[SPUploadMultipleJobs]
 @JobIds varchar(max) = '',
 @Attachment varchar(500) = ''
AS
BEGIN
 if(@JobIds <> '' AND @Attachment <> '')
 BEGIN
  DECLARE
   @ProjectId varchar(500),
   @position INT,
   @RevisionCount varchar(500);
  SET @position=0;
  if (right(@JobIds,1)<>',') SET @JobIds=@JobIds+',';
  WHILE charindex(',',@JobIds)>0
  BEGIN
      SET @ProjectId = cast(substring(@JobIds,0, charindex(',',@JobIds)) as varchar(500))       
      --print @ProjectId;
      set @RevisionCount = 'Revision/'+cast(((select COUNT(Revision_num) from Revision_table where Project_id = @ProjectId)+1) as       varchar(500));
      --print 'JobId : '+@JobIds+' Attachment : '+@Attachment+' ProjectId : '+@ProjectId +' RevisionCount : '+ @RevisionCount;
      update Project_management set Status = 'For Approval' where Project_id = @ProjectId;
      insert into Revision_table (Project_id, Attachments, Revision_num) Values (@ProjectId,@Attachment,@RevisionCount);
      SET @JobIds = substring(@JobIds, charindex(',',@JobIds)+1, LEN(@JobIds) - @position);
  END
 END
END

Common Table Expression in Stored Procedure

CREATE PROCEDURE SPUpdateVoucherStatus
 @VoucherId nvarchar(50) = ''
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @TotalAmount int,@PaidAmount int;
 WITH Account(Total,Paid) as
 (
    select vd.Amount as TotalAmount,SUM(CONVERT(int,pd.TotalAmount)) as AmountPaid
 from Voucher_details as vd
 left join Payment_details as pd on pd.VoucherId = vd.Voucher_id
 group by pd.VoucherId,vd.Vendor_id,vd.Customer_id,vd.Amount,vd.Date,vd.Status
 having vd.Status = 'Unbilled' and pd.VoucherId=@VoucherId
 )
 select @PaidAmount = Paid,@TotalAmount = Total from Account;
 if(@PaidAmount >= @TotalAmount)
 begin
  update Voucher_details set Status = 'Billed' where Voucher_id = @VoucherId
 end
END

Example of Transactions in SQL in Stored Procedure

CREATE PROCEDURE SPPartPaymentEntry
 @VoucherId nvarchar(50) = '',
 @PaymentDate nvarchar(50) = '',
 @TotalAmount nvarchar(50) = '',
 @PaymentMode nvarchar(50) = '',
 @CashAmount nvarchar(50) = '',
 @ChequeAmount nvarchar(50) = '',
 @ChequeNo nvarchar(50) = '',
 @BankName nvarchar(50) = '',
 @ChequeDate nvarchar(50) = '',
 @BankBranch nvarchar(50) = ''
AS
BEGIN
 SET NOCOUNT ON;
 BEGIN TRANSACTION PaymentEntry

 insert into Payment_details(VoucherId,Payment_date,TotalAmount,Payment_mode,Amount_cash,Amount_cheque,Cheque_num,Bank_name,Cheque_date,Bank_branch)
 values(@VoucherId,@PaymentDate,@TotalAmount,@PaymentMode,@CashAmount,@ChequeAmount,@ChequeNo,@BankName,@ChequeDate,@BankBranch)

 declare @Peti_id nvarchar(50);
 set @Peti_id = 'pid'+cast(((select COUNT(Peti_id) from Peti_cash_id)+1) as nvarchar(50));

 insert into Peti_cash_deposit (Peti_id, Description, Date, Deposit, Withdraw)
 Values (@Peti_id, 'Paid for Voucher No. ' + @VoucherId, @PaymentDate, '0', @CashAmount);
 update Peti_cash_balance Set Balance = (CAST(((select Balance from Peti_cash_balance)- CAST(@CashAmount as int)) as nvarchar(50)));

 insert into Peti_cash_id (Peti_id) Values (@Peti_id)

 IF @@ERROR = 0
  COMMIT TRANSACTION PaymentEntry
 ELSE
  ROLLBACK TRANSACTION PaymentEntry
END

Select First and Last Date of Month using Date

DECLARE @mydate DATETIME
SELECT @mydate = '04/10/2013'
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value /*First Date*/
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,'04/10/2013'))), DATEADD(mm,1,'04/10/2013')),101) /*Last Date*/

Select First and Last Date of Month using Month and Year

DECLARE @Month int;
DECLARE @Year int;
set @Month = 05
set @Year = 2013
select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First Date*/
select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last Date*/

Insert into Table from another Table

insert into Leave_detail(Company,Emp_name,Emp_id,AL,LeaveYear)
select e.Company_id,em.F_name,em.Employee_id,'11','2013' from Employee_management as em
join Employee as e on em.Employee_id=e.Employee_id

Update Table values from another Table

update Salary set Salary.Company_id = e.Company_id from Employee e where e.Employee_id = Salary.Employee_id

Get the Last inserted id in table

SqlCommand cmd = new SqlCommand("insert into Role (Role) OUTPUT INSERTED.RoleID Values ('Admin')",Conn);
conn.Open();
Int32 RoleID = (Int32)cmd.ExecuteScalar();
conn.Close();