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();