Rachit Gupta

How can we find the stored procedure in the database if we have just some idea of the procedure name?

Store procedure can be searched via 2 ways: Via GUI: 1. Connect to server 2. Connect to the database 3. Click on the database and expand--> Click on Programmability --> Right Click on stored Procedure --> Click on Filter--> Click on Add--> A new dialog box will get open--> write a procedure name for which you have an…

SQL Server

Answered Jan 27, 2020 · 1.6K Views Read answer →

How to modify/alter and delete/drop a stored procedure?

Via GUI 1. Connect to server 2. Connect to the database For Alter/ Modify Click on the database and expand--> Click on Programmability --> Click on stored Procedure --> Right click on Procedure Nameà Script store procedureà Alter Toà New Query Editor Window For Delete/ drop Click on the database and expand--> Click on…

SQL Server

Answered Jan 26, 2020 · 1.8K Views Read answer →

How will the transaction work with nested procedure?

In nested stored procedure: you call a procedure inside a procedure. This transaction needs to be handled in a different way. If in case a transaction is written in both the procedure and if the child procedure fails then the output of the procedure will not be handed by the parent procedure so it is recommended to…

SQL Server

Answered Jan 25, 2020 · 2.0K Views Read answer →

What are the different types of procedures?

Procedures are segregated into 3 parts: 1. User defined Stored procedure These are procedures which are created via user on the user databases, users create these procedures to get accomplish specific tasks. It can be created via Database administrator or database developer. 2. System Stored procedure: it is the…

SQL Server

Answered Jan 25, 2020 · 2.2K Views Read answer →

What are the stored procedures?

Stored procedure is a collection of SQL statements that are stored on a SQL server. Its performance is better than the queries executed separately as the query plan is stored in the server which helps in executing the statements early. Variables can be passed into the procedure and we get back the out variable/…

SQL Server

Answered Jan 20, 2020 · 1.5K Views Read answer →

How many ways can procedures be executed?

Procedures can be executed via 2 ways: Static way: Via passing the parameters directly to the procedure and execute it, in multiple ways: -- Create Stored Procedure CREATE PROCEDURE usp_TestParameter @p_First VARCHAR(50), @p_Second VARCHAR(50) AS

SQL Server

Answered Jan 19, 2020 · 1.5K Views Read answer →

How can data insert/update/delete in a table using a stored procedure?

We can insert/update/delete data in the table by passing the values in parameters and inserting /updating/ deleting the data into the table. After operations the best thing is to get the output so that it is confirmed that the requirement is fulfilled or not. In the below procedure insert is tested, however…

SQL Server

Answered Jan 18, 2020 · 2.1K Views Read answer →

Is it allowed to perform DDL/DML operations on tables in the procedure?

Yes DML operations (update/insert/ delete) are allowed in the procedure also (DDL: such as create/ alter/drop/ truncate) is also allowed in the procedures. In case if the procedure will get executed in parallel then DDL on permanent table is not recommended as there could be chances that one procedure has already…

SQL Server

Answered Jan 17, 2020 · 1.7K Views Read answer →

How to debug a stored procedure using print statements and time analyses for each statement?

Print statements can be used to set the print in between the statements where we need to see the time taken by the query. Always use a print statement before and one after that so that we can track the time taken in between. Note: these statements are used only for analyses never run the print statement for the…

SQL Server

Answered Jan 16, 2020 · 8.8K Views Read answer →

How can you send mail on an exception raised in procedure?

Mail can be sent via SQL server only if SMTP is set on the server. Post that you can send the mails as the normal mail, below is the example by which you can send a mail when exception is raised: Below is the sample procedure for sending a mail : alter proc usp_exception_handle as begin try select 2 RAISERROR('User…

SQL Server

Answered Jan 16, 2020 · 2.3K Views Read answer →

What is deferred name resolution in SQLs server?

While creating the procedure SQL server only checks the syntax of the statements not the existence of the object. I.e. a procedure can be created using a table name in between which doesn't exist. The object existence is checked while executing the procedure, in case if the object is not present then it will give an…

SQL Server

Answered Jan 14, 2020 · 1.7K Views Read answer →

How can a table be passed as parameter to another procedure?

To pass a table from one procedure to another first we need to create a user-defined table type. In which we will define the table. CREATE type [dbo].[EmployType] as table ( [EmployID] [int] NULL, [EmployName] [varchar](30) NULL, [EmploySalary] [int] NULL

SQL Server

Answered Jan 14, 2020 · 1.6K Views Read answer →