Rachit Gupta

How can you perform multiple different operations using a procedure without impacting any other object?

For an instance you need to insert/update/ delete form a table using single procedure we can do this by, passing a parameter for the operation and perform the required task create PROCEDURE usp_Test_Multiple_task @p_First VARCHAR(50), @p_Second VARCHAR(50), @P_Operation VARCHAR(50), @p_Result VARCHAR(50) out AS begin

SQL Server

Answered Jan 13, 2020 · 1.4K Views Read answer →

How can you pass parameter and get the data back from the procedure?

There are options to pass parameters and get back values from the procedure: IN: This is the Default Parameter which is passed to procedure. It always receives the values from the calling program. In the procedure we can set a default value for this parameter as well, in case it will be passed as null as it will take…

SQL Server

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

What are functions in SQL server?

It is a database object which can accept input parameters and process the statements and provide the result (single value/data set) function will always return a value. Function is found in Sys.objects with type=’FN’ or it can be extracted from database objects. The function name can’t exceed 128 characters.Functions…

SQL Server

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

How to create a function/ view/ trigger/ procedure inside a procedure?

Creating/updating tables is allowed in the procedure directly, however creating a view/ trigger/ procedure is not allowed directly. So we can write dynamic queries to perform such tasks, in this we make a sql command (procedure/view/function/trigger details) in the variable and then execute it. create proc…

SQL Server

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

How to create scalar function?

Scalar functions are functions which return only a single value of any SQL type. Function can accept up to 1024 input parameters. Value can only be returned by the keyword return. Sample for the function: Syntax: Create function functionname( @input_paramters datatype) Return data type As Begin Return output_value end

SQL Server

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

How to set up an audit for the tables using procedures?

As a lot of operations and tasks are performed in a single procedure, it is recommended that the to keep the logs in the table to check in case of any issue or analysis in case if it fails on production execution or for any audit purposes. It is always recommended to keep track of the parameters which were sent to…

SQL Server

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

How are temporary tables used in stored procedure?

There are 2 types of temporary tables: local temporary table, global temporary table. Local temporary table: This table can be used by only the session which has created the table. And it drops when the session is closed. create Procedure usp_test_localTempTable as Begin Create Table #Employ(EMP_Id int, EMP_Name…

SQL Server

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

System functions are segregated into how many types will be examples of each type.

System functions are segregated into 13 parts mainly as mentioned below: 1. Aggregate function: functions which are used for any aggregates work. eg: min, max, sum, avg, count etc. 2. Configuration function: Functions relating to server related options such as : @@Language, @@Langid, @@Servername ,@@Servicename 3.…

SQL Server

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

How are table variables used in stored procedure?

Table variables declared in the same way a variable declared. i.e. declare @EMP table (EMP_ID INT, EMP_NAME nvarchar (50)) The scope of table variable ends after the end of execution of batch/ procedure. Select/ insert/update/ delete can be performed as done on a normal table. But table variables can not be…

SQL Server

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

How can you send mail from a procedure with an attachment?

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 also send the data from SQL server as and attached file via mail create proc usp_send_user_mail_with_attachment as Begin DECLARE @V_MESSAGE NVARCHAR(MAX), @V_subject NVARCHAR(MAX) ,@V_query…

SQL Server

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

How to create a stored procedure?

Store procedure can be created by mainly 2 ways: Using SQL commands: 1. Connect to server 2. Connect to the database

SQL Server

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

What are the different procedure attributes in SQL server?

There are 2 type of attributes which are frequency used: With Encryption With recompile In Encryption : After using this there is no chance of viewing the script of procedure as it is encrypted . and the script will not be visible in syscomments . This is used while installing the application on the client server, as…

SQL Server

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