SQL Snippet - Eg Net Solution

Here in this sites web and software developer can get some essential information.

MY Favorite .Net Question For Interview

This are not tidy. Just for rough. In Sha Allah will make it tiddy soon. 1.  DateTime2 Date range 0001-01-01 through 9999-12-31  vs Date...

Users Countries


Tuesday, October 9, 2018

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. --select top 50  CommonId_For_ForeCastingDetails, DyeingPart, DyesID  from dbo.tblDyesForecastingDetail

  24.  select DISTINCT  LDNO  from dbo.tblDyesForecastingHead 
  25.  where   Convert(nvarchar(50), LDNO) like '%'+ Convert(nvarchar(50), ISNULL(@LDNO, LDNO) ) +'%'

  26.  --select top 50  CommonId_For_ForeCastingDetails, DyeingPart, DyesID  from dbo.tblDyesForecastingDetail

  27.  If (@@ERROR<>0)
  28. Begin
  29. set @Result = 0
  30. set @Message = 'Failed to Retrieve, Try Again'
  31. End
  32. Else
  33. BEGIN
  34.      set @Result = 1
  35.      set @Message = 'Data was Retrieved successfully'
  36. END
  37. 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.            IF @@FETCH_STATUS = -1 
  26.            Break 
  27.            Else  
  28.            SET @tblBody=@tblBody+ ', ' + convert(nvarchar(20), @Id) 
  29.            END  
  30.            CLOSE @CtnInfo 
  31.            DEALLOCATE @CtnInfo  
  32.            SELECT @@FETCH_STATUS as FETCH_STATUS2 
  33.            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. --partition BY With Group BY
  25. -- My understanding partition by will count seqs increasingly untill partition by column value remain unchanged
  26.     select col1, col2, col3, count(col3)Col3Count, row_number()
  27.     over (Partition BY col1 , col2
  28.     order By col1 ) seqs
  29.     from @Test
  30.     group by col1, col2, col3
  31.     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.            OPEN @CtnInfo   
  32.            WHILE 1 = 1 
  33.            BEGIN  
  34.            FETCH NEXT FROM @CtnInfo INTO
  35.               @c1, @c2, @c3, @c4, @c5 
  36.    IF @@FETCH_STATUS = -1 
  37.                Break 
  38.            Else  
  39.           set @tblBody=@tblBody+' <tr > 
  40.            <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>   '
  41.            END  
  42.            CLOSE @CtnInfo 
  43.            DEALLOCATE @CtnInfo  
  44.            SELECT @@FETCH_STATUS as FETCH_STATUS2 
  45.            select @tblBody as TotalSelecteddata
  46.    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>'
  47.            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>'
  48.  
  49. declare @recipient nvarchar(max) = 'ashikcse20@gmail.com;'
  50. declare @copy_recipient nvarchar(max) = ' ashikcse20@gmail.com;'
  51.  
  52. EXEC msdb.dbo.sp_send_dbmail 
  53.  
  54. @profile_name = 'MIS-MTL',  
  55. @recipients =  @recipient, 
  56. @copy_recipients= @copy_recipient ,  
  57. @body =   @tblBody  ,
  58. @subject = 'Automatic Mail Sending',
  59. @body_format = 'HTML' 


No comments:

Add Choice