Skip to content

ErcanOPAK.com

  • ASP.Net WebForms
  • ASP.Net MVC
  • C#
  • SQL
  • MySQL
  • PHP
  • Devexpress
  • Reportviewer
  • About

Tag: query

SQL

How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()

- 20.07.22 | 21.07.22 - ErcanOPAK comment on How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()

In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well. SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS — Skip this number of rows FETCH NEXT 1 ROWS ONLY; — Return this […]

Continue Reading ..
SQL

How to check if recursive triggers are enabled in SQL

- 25.12.21 - ErcanOPAK comment on How to check if recursive triggers are enabled in SQL

Recursive triggers are set at the database level. It’s part of the database metadata information and is available through “sys.databases” view. You can use the below query to check whether the recursive triggers are enabled or not on your database: SELECT name AS ‘Database’, is_recursive_triggers_on AS ‘Recursive Trigger Enabled’ FROM sys.databases Here is the output:

Continue Reading ..
SQL

How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

- 11.04.20 - ErcanOPAK comment on How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO

Continue Reading ..
SQL

Create, Alter, Drop and Execute SQL Server Stored Procedures

- 23.09.19 | 22.11.19 - ErcanOPAK comment on Create, Alter, Drop and Execute SQL Server Stored Procedures

A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table.  A block of T-SQL code can be saved in a T-SQL script file.  You can also store the code from a script file in a stored procedure. There are several benefits that result from […]

Continue Reading ..
SQL

SQL Query to Find and Replace text in a stored procedures

- 20.09.19 | 22.11.19 - ErcanOPAK comment on SQL Query to Find and Replace text in a stored procedures

Declare @spnames CURSOR Declare @spname nvarchar(max) Declare @moddef nvarchar(max) Set @spnames = CURSOR FOR select distinct object_name(c.id) from syscomments c, sysobjects o where c.text like ‘%findtext%’ and c.id = o.id and o.type = ‘P’ OPEN @spnames FETCH NEXT FROM @spnames into @spname WHILE @@FETCH_STATUS = 0 BEGIN Set @moddef = (SELECT Replace ((REPLACE(definition,’findtext’,’replacetext’)),’ALTER’,’create’) FROM sys.sql_modules […]

Continue Reading ..
SQL

“ORDER BY” with CASE and UNION in SQL Server

- 01.09.19 | 22.11.19 - ErcanOPAK comment on “ORDER BY” with CASE and UNION in SQL Server

If you want to use the combination of ORDER BY, CASE and UNION then you should use inner query for a solution without any headache: SELECT * FROM ( SELECT Col_a, Col_b, 0 AS Col_c FROM table1 WHERE conditions UNION SELECT Col_a, NULL AS Col_b, Col_c FROM table2 WHERE conditions ) x ORDER BY CASE […]

Continue Reading ..
SQL

Average of all values in a column that are not zero in SQL

- 11.06.19 | 22.11.19 - ErcanOPAK comment on Average of all values in a column that are not zero in SQL

SELECT AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END) …. AVG function will not take into account NULL values. So you can also use this AVG (NULLIF(Value, 0))

Continue Reading ..
SQL

Date and Time Conversions in SQL Server

- 26.05.19 | 22.11.19 - ErcanOPAK comment on Date and Time Conversions in SQL Server

SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, […]

Continue Reading ..
SQL

How to find records based on CAPITAL and small letters in SQL Query?

- 01.06.18 | 30.08.20 - ErcanOPAK comment on How to find records based on CAPITAL and small letters in SQL Query?

SELECT * FROM SQLTABLE WHERE SUBSTRING(TableColumn, 1, 1) = ‘x’ COLLATE SQL_Latin1_General_CP1_CS_AS

Continue Reading ..
SQL

Get a Tree view with SQL Query

- 27.05.18 | 07.06.22 - ErcanOPAK comment on Get a Tree view with SQL Query

First of all, I must thank to Maulik Dhorajia for his great post. That post saved me a lot. That’s the @Company table we will use: And here is the SQL Query we need to use: — Working Example ;WITH CTECompany AS ( SELECT EmpID, ParentID, PersonName , 0 AS HLevel, CAST(RIGHT(REPLICATE(‘_’,5) + CONVERT(VARCHAR(20),EmpID),20) AS […]

Continue Reading ..
August 2022
M T W T F S S
1234567
891011121314
15161718192021
22232425262728
293031  
« Jul    

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (362)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (234)
  • Find numbers with more than two decimal places in SQL (204)
  • Confirm before process with ASPxButton in Devexpress (200)
  • Add Constraint to SQL Table to ensure email contains @ (200)
  • ASPxGridView – Disable CheckBox based on condition in GridViewCommandColumn (199)
  • How to solve “Response.Redirect cannot be called in a Page callback” for DevExpress Components (197)
  • How to make some specific word(s) Bold or Underline in ReportViewer (191)
  • Devexpress ASPxGridview Column Grouping in Code (184)
  • Tense Changes When Using Reported Speech (171)

Recent Posts

  • How to create the ShowBlanksValue and ShowNonBlanksValue items in Devex Grid
  • How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()
  • How to change the style of scrollbar with CSS
  • How to add scroll bar to div in Asp.Net
  • How to solve “Fatal: Not possible to fast-forward, aborting” problem
  • How to add Output Parameter to SqlDataSource
  • How to get stored procedure parameters details in SQL
  • How to add default value for Entity Framework migrations for DateTime and Bool
  • String Interpolation, String Format, String Concat and String Builder in C#
  • How to get triggers create and update date in SQL

Recent Posts

  • How to create the ShowBlanksValue and ShowNonBlanksValue items in Devex Grid
  • How to get ‘n’th row in Sql Query with OFFSET FETCH NEXT and ROW_NUMBER()
  • How to change the style of scrollbar with CSS
  • How to add scroll bar to div in Asp.Net
  • How to solve “Fatal: Not possible to fast-forward, aborting” problem

Most Viewed Posts

  • Get the First and Last Word from a String or Sentence in SQL (362)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (234)
  • Find numbers with more than two decimal places in SQL (204)
  • Confirm before process with ASPxButton in Devexpress (200)
  • Add Constraint to SQL Table to ensure email contains @ (200)

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter

© 2022 ErcanOPAK.com

Proudly powered by WordPress | Theme: Xblog Plus by wpthemespace.com