Can we use return in stored procedure?

Can we use return in stored procedure?

You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set prior to the RETURN statement being executed.

How do I return a stored procedure?

To see this yourself, execute any stored procedure from the object explorer, in SQL server management studio.

1. Right Click and select Execute Stored Procedure.
2. If the procedure, expects parameters, provide the values and click OK.
3. Along with the result that you expect, the stored procedure also returns a Return Value = 0.

What is Procid in SQL?

The @@procid is the identifier associated with the current executing stored procedure, trigger, or user defined function. It is an internal number sql server uses to keep track of these items in the system. So with this id you can look up what object is associated with it. SET @ProcName = OBJECT_NAME(@@PROCID);

What is @@ in SQL Server?

In SQL Server, symbol @@ is prefixed to global variables. The server maintains all the global variables. Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them.

Where is parent stored procedure in SQL Server?

Using SQL Server Management Studio Expand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure and then click View Dependencies.

Can Stored Procedure return multiple values?

Multiple values will be returned from Stored Procedure by returning comma separated (delimited) values using Output Parameter. Output Parameter is supported in Stored Procedures of all SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012 and 2014.

When to use the @ PROCID function in SQL Server?

SQL Server, from version 2000 on, has included the @@PROCID function that returns the object id of the currently running procedure. The word “procedure” used here includes stored procedures, user-defined functions and triggers.

Can you use @ PROCID to get object name?

There are places on the web that will tell you that you can use @@PROCID in a trigger to get the object id of the stored procedure that did the DML that initiated the trigger and from @@PROCID you get the object name. These sites are wrong, at least since SQL Server 2008 R2, and I think before.

What does @ @ PROCID return in DML?

@@PROCID returns the object_id of the trigger that is running, not the procedure that executed the DML. This script creates an example table and trigger to illustrate the problematic suggestion:

How to get the name of a procedure?

The word “procedure” used here includes stored procedures, user-defined functions and triggers. To get the procedure name I always include the line: DECLARE @this_proc_name sysname = QUOTENAME (OBJECT_SCHEMA_NAME (@@PROCID)) +’.’+QUOTENAME (OBJECT_NAME (@@PROCID))