SQL Basic Query

I decided to write an article on these queries, because I think that such type of query we required many times in daily programming work and basic knowledge of these query can save our time to retrieve the specific information about the database, table or any other object.

Let’s start today's session:

List of Stored procedure modified in last N days:

This script can be used to retrieve the name and time of modification about stored procedure that are modified within a specific time period. The following script provide the name and time of stored procedure that are modified within last 10 days.

SELECT name,modify_date
FROM sys.objects
WHERE type='P'
ANDDATEDIFF(D,modify_date,GETDATE())< 10

Output:



You can also retrieve information about any other objects of database by defining there types. I mean if you want to retrieve the name of all Triggers that are modified with in a specific time period then use (Type=”TR”) instead of(Type=”P”). The following table provide information about the types of objects.

 

Object Name	Object Symbol
Internal Table	IT
Stored Procedure	P
System Base Table	S
Trigger	TR
Table Types	TT
View	V

List of Stored procedure created in last N days:

The following script can be used to provide the name and time of all stored procedure that are created in last N days.

SELECT name,sys.objects.create_date
FROM sys.objects
WHERE type='P'
ANDDATEDIFF(D,sys.objects.create_date,GETDATE())< 40

Output:



Retrieve Names of Stored Procedure Related to Specific Table:

The following script can be used to retrieve the name of stored procedure that are related to a specific table.

SELECT DISTINCT SYS2.Name
FROM sys.syscommentsASSYS
INNERJOIN sys.sysobjectsASSYS2 ONSYS.id=SYS2.id
WHERE SYS.TEXTLIKE'%Basic_Detail%'
--Here BAsic_Detail is a Table Name

Output:



Recompile A Specific Stored Procedure

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. We should prefer to recompile all the stored procedure on the table, which has faced mass insert or update. When a procedure is compiled for the first time or recompiled, then the procedure’s query plan is optimized for the current state of the database and table. If a database or table’s data or structure are changed recompiling a procedure updates and optimizes the procedure’s query plan for those changes. This can improve the procedure’s processing performance.

There are three ways to recompile the stored procedure.

 

1. Using sp_recompile system stored procedure: Below script can be used to recompile a stored procedure .
   
   Example:
   

   --Recompile Stored Procedure
   EXEC sp_recompile'Searching_Result_AllKeyword';
   GO

   Output:
   
   
   
   
2. Recompile Query Hint:
   
   This method is used when the procedure is created and the hint is included in Transact-SQL statements in the procedure. You must apply only to statements in the stored procedure where you’d like to implement the recompile
   
   Example:
   

   GO
   ALTER proc [dbo].[Retrive_Service]
   as begin
   select*fromTblMaster_Service_Type_InfoOPTION (RECOMPILE)
   end

3. With Recompile Option:
   
   This option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. The option most folks discover first is to use ‘WITH RECOMPILE’ in the header of the stored procedure.
   
   Example:
   

   CREATE proc [dbo].[Retrive_Service]
   WITH RECOMPILE
   as begin
   select*from TblMaster_Service_Type_Info
   end

Recompile All Stored Procedure of a Table:

Below script can be used to recompile all stored procedure of a specific table.

--Recompile All Stored Procedure of specifc table
EXEC sp_recompileTblCompany_Basic_Detail;
GO

Output:



Execute Same Query Multiple Times

Using GO command we can execute same query or statement multiple times.

Example:

SELECTGETDATE()ASDateTime
GO 7

Output:



Select First Row from Top and Bottom of Table:

Below query explain that how to retrieve first from the top and bottom of table.

We will execute the query for below table.



Query:

SELECT * FROM dbo.EmployeeAS EMP
WHERE EMP.Emp_IdIN(SELECTTOP 1 EMP2.Emp_Id FROMdbo.EmployeeAS EMP2 ORDERBY EMP2.Emp_Id)
UNIONALL
SELECT*FROMdbo.EmployeeAS EMP
WHERE EMP.Emp_IdIN(SELECTTOP 1 EMP3.Emp_Id FROMdbo.EmployeeAS EMP3 ORDERBY EMP3.Emp_Id DESC)

Output:



Single Stored Procedure For DML Queries

We generally use the stored procedure for retrieve, insert, and delete data from table. We create separate stored procedure for Insert, Delete, and Update operation. But suppose we have 1000 of tables, if we create separate stored procedure for each DML query (Insert, Update, Delete), then it will take a large space in Context menu and it will be very difficult to search any specific stored procedure.

To solve this problem we can create a common stored procedure for DML query that contain one extra parameter for recognize the type of query. This approach will save our space and time.

Now we will take an example how can we achieve this approach.

I have a table Employee. Now I will create a stored procedure for this table that contains all queries of DML.

Stored Procedure

CREATE PROC USP_DML_EMPLOYEE(
@EMP_ID INT,
@EMP_NAME NVARCHAR(MAX),
@EMP_SALARY INT,
@EMP_CITY NVARCHAR(MAX),
@QUERY_TYPE INT
)AS BEGIN IF @QUERY_TYPE = 1 BEGININSERTINTO Employee(
Emp_ID,Emp_Name,Emp_Salary,Emp_City
)
VALUES
(
@EMP_ID, @EMP_NAME, @EMP_SALARY, @EMP_CITY
)END ELSEIF @QUERY_TYPE = 2 BEGIN
UPDATE
Employee
SET
Emp_Name= @EMP_NAME,
Emp_Salary= @EMP_SALARY,
@EMP_CITY = @EMP_CITY
WHERE
Emp_ID= @EMP_ID ENDELSEIF @QUERY_TYPE = 3
DELETE FROM
Employee
WHERE
Emp_ID= @EMP_ID END

In this procedure we pass one extra parameter (i.e. @QUERY_TYPE.) This parameter is used for recognizing the type of DML query. If we want to perform insert operation then we pass 1 for Insert , 2 for Update and 3 for Delete.

Today we read some basic queries, I hope you enjoyed today’s session. I request that if you have a better solution for any of the above query then please share that, it will be very helpful.