SQL Snippet

Select multiple columns from a subquery in Select Statement with SQL Server Or Use OF Outer Apply

1. SELECT t1.ID, t1.fck, t1.f1, t3.f2, t3.f3
2. FROM tbl1 AS t1
3. OUTER APPLY (
4. SELECT f2, f3 
5. FROM tbl2 AS t2 
6. WHERE t2.ID = t1.fck) AS t3

Decaaring table as Variable And Inserting In it:

1. declare @listOfTablesArrangementIDs table (id int);  
2. insert @listOfTablesArrangementIDs(id) values(1),(2),(3) 
3. insert @listOfTablesArrangementIDs(id) values(2027),(20258),(7)  
4. select  * from TablesArrangement where ID NOT IN (select * from @listOfTablesArrangementIDs ) AND AreaCode = 'tst1'

Create Trigger

1.  -- ============================================= 
2.  -- Author: <Muhammad Ashikuzzaman,,Name> 
3.  -- Create date: <24-10-2018,,> 
4.  -- Edit date: <14-08-2018, > 
5.  -- Description: <Initial server: 192.168.153.208 > 
6.  -- ============================================= 
7.  CREATE TRIGGER [ccl2].[TRG_TBL_PriceSetup] 
8.  ON [ccl2].[TBL_PriceSetup] 
9.  AFTER UPDATE 
10.  AS 
11.  BEGIN 
12.  -- SET NOCOUNT ON added to prevent extra result sets from 
13.  -- interfering with SELECT statements. 
14.  SET NOCOUNT ON; 
15.  --Declare @PriceID bigint 
16.  --Select @PriceID = PriceID From deleted 
17.  
18.  insert into ccl2.ARC_PriceSetup (PriceID,Price) 
19.  select PriceID,Price from deleted 
20.  END

Create A Stored Procedure

1. USE [ProjectInfoDB]
2. GO
3. /****** Object:  StoredProcedure [dbo].[SPROC_GET_tblDyesForecastingHead_LDNO]    Script Date: 11/1/2018 12:44:51 PM ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8. -- =============================================
9. -- Author: <Muhammad Ashikuzzaman,,Name>
10. -- Create date: <14-08-2018,,>
11. -- Edit date: < ,,>
12. -- Description: <Initial server: 192.168.153.208 ,,>
13. -- =============================================
14.  ALTER PROCEDURE [dbo].[SPROC_GET_tblDyesForecastingHead_LDNO] 
15.  @LDNO bigint = null, 
16.  @Result  bit =1 out ,
17.  @Message  nvarchar(200) = 'ok' out   
18.   
19.  AS
20. BEGIN
21.   
22. SET NOCOUNT ON;
23. 
    
24. --select top 50  CommonId_For_ForeCastingDetails, DyeingPart, DyesID  from dbo.tblDyesForecastingDetail
25. 
    
26.  select DISTINCT  LDNO  from dbo.tblDyesForecastingHead 
27.  where   Convert(nvarchar(50), LDNO) like '%'+ Convert(nvarchar(50), ISNULL(@LDNO, LDNO) ) +'%'
28. 
    
29.  --select top 50  CommonId_For_ForeCastingDetails, DyeingPart, DyesID  from dbo.tblDyesForecastingDetail
30. 
    
31.  If (@@ERROR<>0)
32. Begin
33. set @Result = 0
34. set @Message = 'Failed to Retrieve, Try Again'
35. End
36. Else
37. BEGIN
38.      set @Result = 1
39.      set @Message = 'Data was Retrieved successfully'
40. END
41. END

Create a Cursor and traverse selected row through this cursor

1. declare @Test table (Id int, Col1 int, Col2 int, Col3 int)
2. insert into @Test (Id , Col1 , Col2 , Col3 )
3. values  (1 , 1 , 1 , 40 ),
4.             (2 , 1 , 1 , 41 ), 
5.             (3 , 1 , 1 , 41 ),
6.             (4 , 1 , 1 , 42 ),
7.             (5 , 1 , 2 , 40 ),
8.             (6 , 1 , 3 , 41 )
9.  
10.           declare @Id bigint
11.           declare @col1 int 
12.            declare @col2 int 
13.            declare @col3 int  
14.            declare @CtnInfo cursor  
15.            set @CtnInfo = CURSOR FOR  
16.            SELECT Id, COL1, COL2, COL3 FROM @Test  
17.            declare @tblBody nvarchar(max) 
18.            set @tblBody= 'Start : '  
19.            OPEN @CtnInfo  
20.            SELECT @@FETCH_STATUS as FETCH_STATUS1 
21.            WHILE 1 = 1 
22.            BEGIN  
23.            FETCH NEXT FROM @CtnInfo INTO
24.                                                      @id, @col1, @col2, @col3 
25. 
    
26.            IF @@FETCH_STATUS = -1 
27.            Break 
28.            Else  
29.            SET @tblBody=@tblBody+ ', ' + convert(nvarchar(20), @Id) 
30.            END  
31.            CLOSE @CtnInfo 
32.            DEALLOCATE @CtnInfo  
33.            SELECT @@FETCH_STATUS as FETCH_STATUS2 
34.            select @tblBody as TotalSelecteddata

   

Understanding partition by 

1. -- My understanding partition by will count seqs increasingly until partition  by column value remain unchanged
2.     declare @Test table (id int, Col1 int, Col2 int, Col3 int)
3.     insert into @Test (id , Col1 , Col2 , Col3 )
4.     values (1 , 1 , 1 , 40 ),
5.     (1 , 1 , 1 , 41 ),
6.     (1 , 1 , 1 , 41 ),
7.     (1 , 1 , 1 , 42 ),
8.     (1 , 1 , 2 , 40 ),
9.     (1 , 1 , 3 , 41 ),
10.     (1 , 1 , 3 , 41 ),
11.     (2 , 1 , 4 , 43 ),
12.     (2 , 2 , 1 , 43 ),
13.     (2 , 2 , 1 , 44 ),
14.     (2 , 2 , 2 , 44 ),
15.     (2 , 2 , 3 , 44 ),
16.     (2 , 2 , 4 , 45 ),
17.     (3 , 3 , 1 , 46 ),
18.     (4 , 3 , 2 , 46 ),
19.     (5 , 3 , 2 , 47 )
20. -- Normal Group BY
21.     select col1, col2 , count(Col1) Col1Count, count(Col2) Col2Count
22.     from @Test
23.     group by col1, col2 order by Col1
24. 
    
25. --partition BY With Group BY
26. -- My understanding partition by will count seqs increasingly untill partition by column value remain unchanged
27.     select col1, col2, col3, count(col3)Col3Count, row_number()
28.     over (Partition BY col1 , col2
29.     order By col1 ) seqs
30.     from @Test
31.     group by col1, col2, col3
32.     order by col1, col2

Remove Time portion from a date time
select DATEADD(DD, DATEDIFF(dd, 0, '2018-12-25 12:25:00'  ), 0)-- will remove time  portion from the date

 select DATEDIFF(dd, 0, '2018-12-25 12:25:00'  )  here 2nd parameter   0 means  '1900-01-01'

Send Mail By sp_send_dbmail stored procedure

1.            declare @tblBody nvarchar(max) 
2.            set @tblBody='<style type="text/css"> 
3.    #detail_table {
4.            border: 2px solid gray; 
5.                border-collapse: collapse; 
6.                          }      
7.   #one td {          border: 1px solid #00000;       }  
8.   #detail_table td , #detail_table th {
9. text-align: center;
10. border: 1px solid gray; 
11. } </style>'
12.           set @tblBody=@tblBody+'<div align="center" style=" border:0px solid #000000; width: 98%;height: 95%;padding: 8px;padding-top:0px; margin: 5px;">  <div align="center"  style="font-weight: bold;font-size: 22px;"> HamdoonSoft Complex </div>  <div style="clear:both;padding: 7px;"></div>   <div align="center" style="font-weight: bold;font-size: 16px; width:230px; border-bottom: 1px solid #222;position:absolute; left:42%">  Hamdoon Soft Notification </div>  <div style="clear:both;"></div>  
13. <div align="left" style="font-size:12px;border:0px solid #000000; width: 99%;height: 100%;padding: 20px;padding-top:0px;">  
14.    <div align="left" style="margin-top:35px;"><b> Date: </b> '+  convert( nvarchar(30) , GETDATE() )+'</div>  
15. <div align="left" style="margin-top:8px;">To, </div>  
16. <div align="left" style="margin-top:10px; margin-left:20px;">DGM/ AGM/ Sr. Manager</div>  <div align="left" style="margin-top:5px; margin-left:20px; height: 30px; "> Department: '+ '@ResponsibleDepartment' +'</div>  <div align="left" style="margin-top:5px; margin-left:22px;">Subject: Problematic fabric information from off line Inspection  to problamatic department.</div>  <div align="left" style="margin-top:18px;">Dear Sir</div>   <div align="left" style="margin-top:15px; margin-left:20px;">This is to inform you that, we found below batch problematic during 100% inspection in off line quality zone.</div> 
17.     <div align="left" style="margin-top:8px;">Details :- </div>  
18.           <br/>       <table id="detail_table" style="margin-top:20px; margin-right:.5%; width:99.5%;  "   >     <tr id="one">  <th>C1 </th> <th>C2 </th> <th>C3 </th> <th>C4 </th> <th>C5</th>    </tr>   '
19.             
20. DECLARE @c1        NVARCHAR(40)
21. DECLARE @c2   NVARCHAR(40)
22. DECLARE @c3   NVARCHAR(40)
23. DECLARE @c4   NVARCHAR(40)
24. DECLARE @c5   NVARCHAR(40) 
25.          
26.    declare @CtnInfo cursor  
27.            set @CtnInfo = CURSOR FOR  
28.             
29.   SELECT    * from (values    (1, 2, 3, 4, 5)  , (1, 2, 3, 4, 5) ) 
30.                 t21 (c1, c2, c3, c4, c5)
31. 
    
32.            OPEN @CtnInfo   
33.            WHILE 1 = 1 
34.            BEGIN  
35.            FETCH NEXT FROM @CtnInfo INTO
36.               @c1, @c2, @c3, @c4, @c5 
37.    IF @@FETCH_STATUS = -1 
38.                Break 
39.            Else  
40.           set @tblBody=@tblBody+' <tr > 
41.            <td  align="center" > '+@c1+'  </td>   <td  align="center" > '+@c2 +'  </td>   <td align="center" > '+@c3 +'  </td>   <td align="center" > '+@c4+' </td><td align="center" > '+@c5+'</td> </tr>   '
42.            END  
43.            CLOSE @CtnInfo 
44.            DEALLOCATE @CtnInfo  
45.            SELECT @@FETCH_STATUS as FETCH_STATUS2 
46.            select @tblBody as TotalSelecteddata
47.    set @tblBody=@tblBody+' </table> <br/> <div style="position: relative!important;left: -530px!important; top: 10px!important; font-weight:bold;  font-family:Verdana, Arial, Helvetica, sans-serif;"> </div>  </div>  </div>'
48.            Set @tblBody=@tblBody+'<div style="Font-weight:bold; margin-top:30px;font-family:Verdana, Arial, Helvetica, sans-serif; "> Best Regards </div>  <div style=" Font-weight:bold; margin-top:10px;font-family:Verdana, Arial, Helvetica, sans-serif; "> MIS Department </div>'
49.  
50. declare @recipient nvarchar(max) = 'ashikcse20@gmail.com;'
51. declare @copy_recipient nvarchar(max) = ' ashikcse20@gmail.com;'
52.  
53. EXEC msdb.dbo.sp_send_dbmail 
54.  
55. @profile_name = 'MIS-MTL',  
56. @recipients =  @recipient, 
57. @copy_recipients= @copy_recipient ,  
58. @body =   @tblBody  ,
59. @subject = 'Automatic Mail Sending',
60. @body_format = 'HTML'