SQL Basic Query

1. Using sp_recompile system stored procedure: Below script can be used to recompile a stored procedure .
   
   Example:
   
   1. --Recompile Stored Procedure
   2. EXEC sp_recompile'Searching_Result_AllKeyword';
   3. 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:
   
   1. GO
   2. ALTER proc [dbo].[Retrive_Service]
   3. as begin
   4. select*fromTblMaster_Service_Type_InfoOPTION (RECOMPILE)
   5. 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:
   
   1. CREATE proc [dbo].[Retrive_Service]
   2. WITH RECOMPILE
   3. as begin
   4. select*from TblMaster_Service_Type_Info
   5. end

Recompile All Stored Procedure of a Table:

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

Output:



Execute Same Query Multiple Times

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

Example:
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:
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
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.