How to get sp_executesql result into Variable in SQL Server? You may have a piece of dynamic SQL that you need to execute, then want to store the result into a variable. You know that you are able to sp_executesql but you cannot find clear examples on how to do this. Here, let us find out that answer.
HOW TO GET SP_EXECUTESQL RESULT INTO A VARIABLE
If you have output parameters you are able to do:
DECLARE @retval int
DECLARE @sSQL nvarchar (500)
DECLARE @ParmDefinition nvarchar(500)
DECLARE @tablename nvarchar(50)
SELECT @tablename = N’products’
SELECT @SQL = N’ SELECT @retval OUT = MAX (ID) FROM + @tablename
SET @Parm.Definition = N@retvalOUT int OUTPUT
EXEC sp_executesql @SQL, @ParmDefinition, @retval OUT=@retval OUTPUT
But if you do not have output parameters and cannot modify the SP, you are able to do:
(Assuming that your SP return 1 value)
create table #temptable (ID int null)
insert into #temptable exec mysp ‘Value1’, ‘Value2’
select * from #temptable
A variable is an object which is able to hold a single data value of a special type. The variables in batches and scripts are usually used:
- As a counter either to count the number of times a loop is performed or to control how lots of times the loop is performed.
- For holding a data value to be tested by a control of flow statement.
- For saving a data value to be returned by a stored procedure return code or function return value.
- The names of several Transact-SQL system functions start with two at signs. They do not have the same behaviors as variables. They are system functions, and their syntax usage follows the rules for functions.
- You will not be able to utilize variables in a view.
- Changes to variables are not affected by the rollback of a transaction.
DECLARING A VARIABLE (TRANSACT-SQL)
Based on the research, the declare statement initializes a variable (Transact-SQL) by:
- Assigning a name
The name should have a single @ as the first character.
- Assigning a system supplied/ user defined data type.
For numeric variables, a precision and scale are assigned too. For variables of type XML, an optional schema collection can be assigned.
- Setting the value to NULL.
For instance, the following declare statement makes a local variable called @mycounter with an int data type.
DECLARE @MyCounter INT;
If you want to declare more than one local variable, you are able to use a comma after the first local variable defined. Then, you have to specify the next local variable name and data type. The scope of a variable is the range of Transact-SQL statements which is able to reference the variable.
The variable lasts from the point it is declared until the end of the batch. For instance, the script generates a syntax error as the variable is declared in one batch and referenced in another. Variables own the local scope which they are only visible within the batch. In the example, the nested scope made for execution of sp_executesql does not have access to the variable declared in the higher scope and returns an error.
SETTING A VALUE IN A VARIABLE (TRANSACT-SQL)
Once a variable is first declared, its value is set to NULL. To set a value to a variable, you have to use the SET statement. This is the preferred way of setting a value to a variable. Also, a variable is able to own a value assigned by being referenced in the select list of a SELECT statement.
To set a variable a value by using the SET statement, please include the variable name and the value to set to the variable. This is the preferred way of setting a value to a variable. The following batch, for instance, declares two variables, sets values to them, and then utilizes them in the WHERE clause of a SELECT statement.
Aside from that, the variables are also able to have a value assigned by being referenced in a select list. If a variable is referenced in a select list, it must be assigned a scalar value or the SELECT statement should only return one row.
If a SELECT statement returns more than one row and the variable references a non-scalar expression, then the variable is set to the value returned for the expression in the last row of the result set. For instance, in the batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned.
As we explained above, If you have output parameters you are able to get sp_executesql result into Variable in SQL Server. Now, you may want to know more information regarding Parameters in SQL Server. Let us continue reading the following text.
Parameters are used to exchange data between stored procedures and functions as well the application or tool which called the stored procedure or function:
- Input parameters offer the caller to pass a data value to the stored procedure or function.
- Output parameters offer the stored procedure to pass a data value or a cursor variable back to the caller. User-defined functions cannot identify output parameters.
Those stored procedures show the use of an input parameter, an output parameter, and a return code.
Once a stored procedure or function is executed, input parameters are able to their value set to a constant or utilize the value of a variable. Output parameters and return codes have to return their values into a variable. Parameters and return codes will be able to exchange data values with either Transact-SQL variables or application variables.
If a stored procedure is called from a script/batch, the parameters and return code values are able to use variables (Transact-SQL) defined in the same batch. There is the example which executes the procedure created earlier. In addition, an application is able to use parameter markers bound to program variables to exchange data between parameters, application variables, and return codes.
On my daily job, I am a software engineer, programmer & computer technician. My passion is assembling PC hardware, studying Operating System and all things related to computers technology. I also love to make short films for YouTube as a producer. More at about me…