How to Assign the Result of exec() to a Python Variable?

💬 Question : Say you have an expression you want to execute using the exec() function. How to store the result of the expression in a Python variable my_result ?

Before I show you the solution, let’s quickly recap the exec() function:

Recap Python exec()

Python’s exec() function executes the Python code you pass as a string or executable object argument.

This is called dynamic execution because, in contrast to normal static Python code, you can generate code and execute it at runtime.

This way, you can run programmatically-created Python code.

Python exec() — A Hacker’s Guide to A Dangerous Function

Related Tutorial : Python’s exec() built-in function

Without further ado, let’s learn how you can store the result of the exec() function in a Python variable:

Method 1: Define Variable in String Representation of Code

You can define a variable inside the exec() function that will then be added to the global namespace. Thus, you can access the defined variable in your code after the termination of the exec() expression!

Here’s how that works in a minimal example:

Variable my_result is only defined in the string expression passed into exec() . You can use it in the code like it was part of the original source code.

Method 2: How to Replace Value of a Variable in exec()

To replace a variable defined outside the exec() function call, you can simply do so using an f-string and pass the new value as the right-hand side of the assignment operator within the string representation of the code to be executed.

Here’s a minimal example where we overwrite the old_variable with the new string 'hello finxter' by using the expression:

exec(f'old_variable = "{new_string}"')

Method 3: How to Replace Global Variable in exec()?

If you use the previous approach to replace a global variable defined outside the exec() expression that is called in a local context, the assignment operator will work on a local alias variable .

The global variable to be replaced will remain the same!

This can be seen in the following code example, where the global old_variable is not replaced with the new string 'hello finxter' because of the local scope of the function f that creates its own namespace with its own old_variable that overshadows the global old_variable .

To overwrite a global variable within the exec() function, use the global keyword, followed by an empty space, the name of the global variable, a semicolon ; to close the statement, and the expression to overwrite the global variable like so: exec(f'global old_variable; old_variable = "{new_string}"')

Here’s the full example:

Related tutorial:

  • How to Use Global Variables In a Python Function?

How to Use Global Variables Inside a Python Function?

Method 4: Redirect Standard Output

Okay, let’s do something crazy, shall we? 🤯

Let’s redirect the standard output and print directly to the variable!

This method assumes you have a print() statement within the expression passed into the exec() function such as shown in the following three examples:

  • exec('print(2+2)')
  • exec('print([1, 2, 3, 4] + [5, 6])')
  • exec('print(2+2*0)')

To get the output and store it in a variable my_result , you need to temporarily redirect the standard output to the variable.

The following code shows you how to accomplish exactly this:

If you need some assistance understanding this whole idea of redirecting the standard output, have a look at our in-depth guide on the Finxter blog.

👀 Related Article: 7 Easy Steps to Redirect Your Standard Output to a Variable (Python)

Note that this approach even works if you don’t have a print() statement in the original exec() expression because you can always artificially add the print() statement around the original expression like so:

  • exec('2+2') becomes exec('print(2+2)')
  • exec('2+2*0') becomes exec('print(2+2*0)')
  • exec('[1, 2, 3] + [4, 5]') becomes exec('print([1, 2, 3] + [4, 5])')

Even if it’s a bit clunky, after applying this short trick, you can redirect the standard output and store the result of any exec() expression in a variable.

👀 Related Article: How to Assign the Result of eval() to a Python Variable?

Where to Go From Here?

Enough theory. Let’s get some practice!

Coders get paid six figures and more because they can solve problems more effectively using machine intelligence and automation.

To become more successful in coding, solve more real problems for real people. That’s how you polish the skills you really need in practice. After all, what’s the use of learning theory that nobody ever needs?

You build high-value coding skills by working on practical coding projects!

Do you want to stop learning with toy projects and focus on practical code projects that earn you money and solve real problems for people?

🚀 If your answer is YES! , consider becoming a Python freelance developer ! It’s the best way of approaching the task of improving your Python skills—even if you are a complete beginner.

If you just want to learn about the freelancing opportunity, feel free to watch my free webinar “How to Build Your High-Income Skill Python” and learn how I grew my coding business online and how you can, too—from the comfort of your own home.

Join the free webinar now!

While working as a researcher in distributed systems, Dr. Christian Mayer found his love for teaching computer science students.

To help students reach higher levels of Python success, he founded the programming education website Finxter.com that has taught exponential skills to millions of coders worldwide. He’s the author of the best-selling programming books Python One-Liners (NoStarch 2020), The Art of Clean Code (NoStarch 2022), and The Book of Dash (NoStarch 2022). Chris also coauthored the Coffee Break Python series of self-published books. He’s a computer science enthusiast, freelancer , and owner of one of the top 10 largest Python blogs worldwide.

His passions are writing, reading, and coding. But his greatest passion is to serve aspiring coders through Finxter and help them to boost their skills. You can join his free email academy here.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Return data from a stored procedure

  • 15 contributors

There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes. This article provides information on the three approaches.

Return data using result sets

If you include a SELECT statement in the body of a stored procedure (but not a SELECT ... INTO or INSERT ... SELECT), the rows specified by the SELECT statement will be sent directly to the client. For large result sets, the stored procedure execution won't continue to the next statement until the result set has been completely sent to the client. For small result sets, the results are spooled for return to the client and execution will continue. If multiple such SELECT statements are run during the execution of the stored procedure, multiple result sets will be sent to the client. This behavior also applies to nested Transact-SQL batches, nested stored procedures, and top-level Transact-SQL batches.

Examples of returning data using a result set

The following examples use the AdventureWorks2022 sample database . This example shows a stored procedure that returns the LastName and SalesYTD values for all SalesPerson rows that also appear in the vEmployee view.

Return data using an output parameter

If you specify the output keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the output keyword when executing the procedure. For more information about what data types can be used as output parameters, see CREATE PROCEDURE (Transact-SQL) .

Examples of output parameters

The following example shows a procedure with an input and an output parameter. The @SalesPerson parameter would receive an input value specified by the calling program. The SELECT statement uses the value passed into the input parameter to obtain the correct SalesYTD value. The SELECT statement also assigns the value to the @SalesYTD output parameter, which returns the value to the calling program when the procedure exits.

The following example calls the procedure created in the first example and saves the output parameter @SalesYTD returned from the called procedure into the @SalesYTDBySalesPerson variable.

The example:

  • Declares the variable @SalesYTDBySalesPerson to receive the output value of the procedure.
  • Executes the Sales.uspGetEmployeeSalesYTD procedure specifying a last name for the input parameter. Save the output value in the variable @SalesYTDBySalesPerson .
  • Calls PRINT to display the value saved to @SalesYTDBySalesPerson .

Input values can also be specified for output parameters when the procedure is executed. This allows the procedure to receive a value from the calling program, change or perform operations with the value, and then return the new value to the calling program. In the previous example, the @SalesYTDBySalesPerson variable can be assigned a value before the program calls the Sales.uspGetEmployeeSalesYTD procedure. The execute statement would pass the @SalesYTDBySalesPerson variable value into the @SalesYTD output parameter. Then in the procedure body, the value could be used for calculations that generate a new value. The new value would be passed back out of the procedure through the output parameter, updating the value in the @SalesYTDBySalesPerson variable when the procedure exits. This is often referred to as "pass-by-reference capability."

If you specify output for a parameter when you call a procedure and that parameter isn't defined by using output in the procedure definition, you get an error message. However, you can execute a procedure with output parameters and not specify output when executing the procedure. No error is returned, but you can't use the output value in the calling program.

Use the cursor data type in output parameters

Transact-SQL procedures can use the cursor data type only for output parameters. If the cursor data type is specified for a parameter, both the varying and output keywords must be specified for that parameter in the procedure definition. A parameter can be specified as only output, but if the varying keyword is specified in the parameter declaration, the data type must be cursor and the output keyword must also be specified.

The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because output parameters must be bound before an application can execute a procedure, procedures with cursor output parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, procedures, or triggers only when the cursor output variable is assigned to a Transact-SQL local cursor variable.

Rules for cursor output parameters

The following rules pertain to cursor output parameters when the procedure is executed:

For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the procedure execution, for example:

A non-scrollable cursor is opened in a procedure on a result set named RS of 100 rows.

The procedure fetches the first five rows of result set RS .

The procedure returns to its caller.

The result set RS returned to the caller consists of rows from 6 through 100 of RS , and the cursor in the caller is positioned before the first row of RS .

For a forward-only cursor, if the cursor is positioned before the first row when the procedure exits, the entire result set is returned to the calling batch, procedure, or trigger. When returned, the cursor position is set before the first row.

For a forward-only cursor, if the cursor is positioned beyond the end of the last row when the procedure exits, an empty result set is returned to the calling batch, procedure, or trigger.

An empty result set is not the same as a null value.

For a scrollable cursor, all the rows in the result set are returned to the calling batch, procedure, or trigger when the procedure exits. When returned, the cursor position is left at the position of the last fetch executed in the procedure.

For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, procedure, or trigger. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

The closed state matters only at return time. For example, it is valid to close a cursor part of the way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, procedure, or trigger.

Examples of cursor output parameters

In the following example, a procedure is created that specified an output parameter, @CurrencyCursor using the cursor data type. The procedure is then called in a batch.

First, create the procedure that declares and then opens a cursor on the Currency table.

Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

Return data using a return code

A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement . As with output parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc , such as:

Return codes are commonly used in control-of-flow blocks within procedures to set the return code value for each possible error situation. You can use the @@ERROR function after a Transact-SQL statement to detect whether an error occurred during the execution of the statement. Before the introduction of TRY / CATCH / THROW error handling in Transact-SQL return codes were sometimes required to determine the success or failure of stored procedures. Stored procedures should always indicate failure with an error (generated with THROW / RAISERROR if necessary), and not rely on a return code to indicate the failure. Also you should avoid using the return code to return application data.

Examples of return codes

The following example shows the usp_GetSalesYTD procedure with error handling that sets special return code values for various errors. The following table shows the integer value that is assigned by the procedure to each possible error, and the corresponding meaning for each value.

The example creates a procedure named Sales.usp_GetSalesYTD , which:

  • Declares the @SalesPerson parameter and sets its default value to NULL . This parameter is intended to take the last name of a sales person.
  • If @SalesPerson is NULL, the procedure prints a message and returns the return code 1 .
  • Otherwise, if the @SalesPerson parameter isn't NULL, the procedure checks the count of rows in the HumanResources.vEmployee table with a last name equal to the value of @SalesPerson . If the count is zero, the procedure returns the return code 2 .
  • Queries the year-to-date sales for the sales person with the specified last name and assigns it to the @SalesYTD output parameter.
  • If @@ERROR isn't equal to zero, the procedure returns the return code 3 .
  • If @@ERROR was equal to zero, the procedure checks to see if the @SalesYTD parameter value is NULL. If no year to date sales were found, the procedure returns the return code 4 .
  • If neither of the preceding conditions are true, the procedure returns the return code 0 .
  • If reached, the final statement in the stored procedure invokes the stored procedure recursively without specifying an input value.

At the end of the example, code is provided to execute the Sales.usp_GetSalesYTD procedure while specifying a last name for the input parameter and saving the output value in the variable @SalesYTD .

The following example creates a program to handle the return codes that are returned from the usp_GetSalesYTD procedure.

  • Declares variables @SalesYTDForSalesPerson and @ret_code to receive the output value and return code of the procedure.
  • Executes the Sales.usp_GetSalesYTD procedure with an input value specified for @SalesPerson and saves the output value and return code in variables.
  • Checks the return code in @ret_code and calls PRINT (Transact-SQL) to display an appropriate message.

Related content

For more information about stored procedures and related concepts, see the following articles:

  • CREATE PROCEDURE (Transact-SQL)
  • PRINT (Transact-SQL)
  • SET @local_variable (Transact-SQL)
  • RETURN (Transact-SQL)
  • @@ERROR (Transact-SQL)

Submit and view feedback for

Additional resources

assigning exec query result to variable

danielsisk619

SSC Veteran

Points: 282

More actions

May 8, 2008 at 3:27 pm

I'm trying to get a count of rows for each column that has info in it. I have

DECLARE @cmd varchar(100)

DECLARE @fldname varchar(50)

declare @number int

-- the name of a each column is put into @fldname

SELECT @cmd = 'select count(*) from CONTACT2 where ' + @fldname + ' is not null '

-- now what I want it to do is to assign the results of exec(@cmd) into @number.

is this possible and if so how do i do it?

Thanks for the help,

SSCommitted

Points: 1724

May 8, 2008 at 4:04 pm

You may do this way,

[font="Courier New"]DECLARE @cmd varchar(100)

DECLARE @number int

SET @fldname = 'ContactName'

CREATE TABLE #number (RecordCount INT)

SELECT @cmd = 'INSERT INTO #number SELECT COUNT(*) FROM CONTACT2 WHERE ' + @fldname + ' IS NOT NULL '

EXECUTE (@cmd)

SELECT @number = RecordCount FROM #number

SELECT @number

DROP TABLE #number[/font]

May 8, 2008 at 5:18 pm

thanks it worked great

May 10, 2008 at 8:55 am

Execute dynamic SQL statement using sp_executesql system stored procedure, if you want a output to a variable. It allows input parameters as well as output parameters.

DECLARE @cmd NVARCHAR(100)

DECLARE @parm NVARCHAR(100)

DECLARE @fldname VARCHAR(50)

DECLARE @number INT

DECLARE @number_out INT

SET @fldname = 'Region'

SELECT @cmd = N'SELECT @number_out = COUNT(*) FROM Customers'

SELECT @cmd = @cmd + N' WHERE ' + @fldname + ' IS NOT NULL'

SELECT @parm = N'@number_out INT OUTPUT'

EXECUTE sp_executesql @cmd,@parm,@number_out = @number OUTPUT

Points: 110206

May 10, 2008 at 2:36 pm

I support sp_executesql approach.

But the query IMHO is built not in the best way.

I must be like this:

'select count(' + QUOTENAME(@fldname) + ' ) from CONTACT2 '

_____________ Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Learn Python practically and Get Certified .

Popular Tutorials

Popular examples, reference materials, learn python interactively, built-in functions.

  • Python abs()
  • Python any()
  • Python all()
  • Python ascii()
  • Python bin()
  • Python bool()
  • Python bytearray()
  • Python callable()
  • Python bytes()
  • Python chr()

Python compile()

  • Python classmethod()
  • Python complex()
  • Python delattr()
  • Python dict()
  • Python dir()
  • Python divmod()
  • Python enumerate()
  • Python staticmethod()
  • Python filter()
  • Python eval()
  • Python float()
  • Python format()
  • Python frozenset()
  • Python getattr()

Python globals()

Python exec()

  • Python hasattr()
  • Python help()
  • Python hex()
  • Python hash()
  • Python input()
  • Python id()
  • Python isinstance()
  • Python int()
  • Python issubclass()
  • Python iter()
  • Python list() Function

Python locals()

  • Python len()
  • Python max()
  • Python min()
  • Python map()
  • Python next()
  • Python memoryview()
  • Python object()
  • Python oct()
  • Python ord()
  • Python open()
  • Python pow()

Python print()

  • Python property()
  • Python range()
  • Python repr()
  • Python reversed()
  • Python round()
  • Python set()
  • Python setattr()
  • Python slice()
  • Python sorted()
  • Python str()
  • Python sum()
  • Python tuple() Function
  • Python type()
  • Python vars()
  • Python zip()
  • Python __import__()
  • Python super()

Python Tutorials

  • Python Basic Input and Output

The exec() method executes a dynamically created program, which is either a string or a code object.

exec() Syntax

The syntax of exec() is:

exec() Parameters

The exec() method takes three parameters:

  • object - Either a string or a code object
  • globals (optional) - a dictionary
  • locals (optional) - a mapping object (commonly dictionary)

exec() Return Value

The exec() method doesn't return any value.

Example 1: Python exec()

In the above example, we have passed the string object program to the exec() method.

The method executes the python code inside the object method and produces the output Sum = 15 .

Example 2: exec() With a Single Line Program Input

In the above example, we have provided the code;

as an input value.

Here, we have used the exec() method to execute the program object which has the user input code.

Note : When you use the exec() method with the OS module, you need to be careful. This is because you can accidentally change and delete files when you use the os.system('rm -rf *') code in the input.

Example 3: exec() with a Multi-line Input Program

We can pass a multi-line input program to the exec() method with the help of \n . But we need to use the compile() method to compile the program first.

In the above example, we have passed a multi-line program as an argument to the exec() method.

But before that, we need to compile this program using the compile() method.

Example 4 : Checking Usable code with exec()

It's a good idea to check the methods and variables you can use with the exec() method. You can do this with the help of the dir() method.

Example 5 : Blocking unnecessary methods and variable in exec()

Most of the time, we don't need all the methods and variables with exec() .

We can block these unnecessary methods and variables by passing the optional globals and locals parameter to the exec() method.

For more information, check locals() and globals() .

In the example above, we have restricted the use of the cbrt() method in exec() although we have imported the whole math library.

That's why we get the output NameError: name 'cbrt' is not defined .

Example 6: Using necessary methods and variables in exec()

We can also make the necessary methods and variables available to use with the exec() method.

To do this, we need to pass the locals dictionary to exec() .

In the above example, we have blocked all the global builtin methods with the code:

But, we have allowed two methods - print() and dir() to be executable with the code:

Finally, we have passed the dir() method inside print() method and then passed it to the exec() method to print a list of usable methods.

The globalsParameter and localsParameter here are optional parameters that we have used with the exec() method to print only the methods that we want to access.

Recommended Readings

Sorry about that.

Python References

Python Library

  • Create Account

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,202 software developers and data experts.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use .

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

Save SQL Server Stored Procedure Results to Table

By: Rick Dobson   |   Comments   |   Related: More > Stored Procedures

I am encountering requirements where I need to process a results set from a SQL Server stored procedure in another application or I am asked to pass the results set generated by a stored procedure to another team for data mining.  What are some ways of making a results set from a stored procedure available for data mining or data science projects?  Please also demonstrate how to store for subsequent use more than one results set from a single stored procedure.

When you run a stored procedure with one or more select statements, SQL Server displays a results set for each SELECT statement .  While it is sometimes enough to just view the results set from a stored procedure, it can easily happen that you or a data mining colleague need the results set for subsequent processing.  This tip equips you to persist one or more results sets from a stored procedure.  If you need a refresher on creating, altering, dropping, and executing stored procedures, you can review a prior tip, Create, Alter, Drop and Execute SQL Server Stored Procedures , in this series on stored procedures and two other kinds of code modules (user-defined functions and views).

The general solution for persisting a results set from a stored procedure is to store the results set(s) in one or more tables.  You can use any of several different types of tables, including regular tables , local temp tables and global temp tables .  This tip includes three examples that reveal how to persist a results set from a stored procedure with each of these options.  This tip also demonstrates how to persist more than one results set from a single stored procedure.

Copying a SQL Server Stored Procedure’s Results Set to a Local Temp Table

You can copy the results set from a stored procedure to a local temp table in a three-step process. 

  • In the first step, create a fresh copy of the stored procedure with a select statement that generates a results set whose output you want to persist.
  • With a local temp table, the data in the table exists for the duration of the session creating the local temp table and goes out of scope automatically when the session creating the local temp table closes.
  • In contrast, the data in a regular table can exist until the table is dropped. 
  • In the third step, run the stored procedure so that its results set becomes the source for populating the table.

Here’s an example of a simple script for the first step.

  • The use statement sets the database context.  This is the database where the stored procedure object will be saved.  The data source for the select statement inside a stored procedure can be from a different database.
  • The next code block in the script drops a stored procedure named uspMySecondStoredProcedure in the dbo schema if it exists already; the dbo schema will be used in all samples throughout this tip.  It is necessary to include a go statement after the conditional drop proc formulation so that create proc in the next statement is at the top of its batch.
  • First, it adds a new stored procedure to the database in which it runs.
  • Second, it contains a select statement that displays a results set.

After you create the stored procedure, you need to run it and store the generated results set in a table.  The next script demonstrates one approach to accomplishing these goals.

  • The target table for storing the results set is a local temp table named #soh.  If you want a refresher on temp tables, see this prior tip . 
  • After creating the temp table, the script runs uspMySecondStoredProcedure as the source for an insert into statement for the #soh table.
  • The identity_insert property is turned on just before invoking the insert into statement.
  • The identity_insert property is restored to its default value of off right after the exec statement for the stored procedure.
  • The final select statement in the script displays the stored procedure’s results set from the local temp table.  You can use table values in any ways that your needs dictate, such as to compute sales taxes for orders.

Saving the Results Set from a SELECT Statement within a SQL Server Stored Procedure to a Regular Table

By saving a select statement’s results set to a regular table from inside a stored procedure, the process for persisting a results set can be even simpler than with the preceding example.  There are two main reasons that the process is simpler.

  • Regular tables, in contrast to local temp tables, can have the same scope whether they are populated from inside or outside of a stored procedure.  Their scope extends beyond the stored procedure.  The scope of a local temp table created and populated from within a stored procedure is limited to the stored procedure.
  • You can transfer the results set from a select statement to a regular table from an into clause within a select statement.  Insert the into clause after the select list and before the from clause.  Name the regular table in the into clause.  The table must reside in the current database context.  When the limitations of an into clause are acceptable, then the into clause offers a very attractive solution for persisting data from a stored procedure.

The following script excerpt shows an approach to populating a regular table from within a stored procedure.

  • The database context in the example has the name CodeModuleTypes.
  • The conditional drop formulations remove prior versions of the stored procedure that creates a results set and the table persisting the results set.
  • The go keyword allows the next create proc statement to appear at the top of its batch.
  • The into clause within the select statement in uspMySecondStoredProcedure copies the select statement’s results set to a regular table named soh_col_subset in the dbo schema of the CodeModuleTypes; one limitation of the into keyword is that it can only transfer data to objects within the current database context.
  • The code to create the stored proc only needs to run once. The go keyword after the create proc statement completes the creation of the stored procedure before an attempt is made to execute the stored procedure within the same code block.
  • The line of dashes after the go keyword at the end of the create proc statement separates the create proc statement from a trailing exec statement.
  • The code after the line of dashes includes an exec statement for the stored procedure followed by a select statement to display the results set from the soh_col_subset regular table.

The following screen shot shows from Object Explorer the soh_col_subset table design created by the into clause in the preceding script.  Notice the into clause does not include the identity property from the SalesOrderID column in the SalesOrderHeader table from the AdventureWorks2014 database.  The insert into and select into statements handle source data with an identity column somewhat differently.  Recall it was necessary to manipulate the identity_insert property when copying with an insert into statement to a local temp.

table columns in ssms

The empty results set from the following script confirms that all the row values for the selected columns from the SalesOrderHeader table match the row values within the soh_col_subset table.  The except operator returns all rows from the results set before it that are missing from the results set after it.  If your requirements are for the identity values and not the identity property, you can simplify your code by using a select into statement versus an insert into statement.

Storing Results Sets from Two SELECT Statements for Use Outside a SQL Server Stored Procedure

Up until this point, this tip focused on extracting just one results set to a table from one select statement inside a stored procedure.  However, one special capability of a stored procedure is that it can return results sets from multiple select statements.  This section shows you how to transfer multiple results sets from a stored procedure for use outside of a stored procedure.

You could simply specify an into clause for each select statement to make the results sets for each select statement available in a regular table after the stored procedure runs.  However, this approach requires you to manage the lifecycle of the regular tables.  Another approach is to transfer from within the stored procedure the results set for each select statement to a global temp table.  Because the scope of a global temp table extends to the scope of the session in which it was created, then the transferred results set remains available for as long as you keep the session running.  When you close the session from which you invoked the stored procedure, the global temp tables created within the session automatically ceases to exist as soon as no users have connections to a global temp table.  This is a great feature so long as you only need a data source for the duration of a session.

Here’s a script to demonstrate an approach for transferring multiple results sets from a stored procedure via global temp tables.

  • First, any prior version that exists is dropped.
  • Next, a create proc statement names the stored procedure and is followed by the as keyword with the defining T-SQL code for the stored procedure.
  • A trailing go keyword follows the code to perform the operations encapsulated in the stored proc.
  • First, it creates fresh versions of ##soh and ##customer global temp tables.
  • Next, it uses insert into statements to populate both global temp tables from select statements from regular tables in the AdvertureWorks2014 database.  These insert into statements are embedded inside code that turns the identity_insert property on and off for the population of each global temp table.  Recall that this kind of manipulation preserves the identity property in a table holding values from a select statement.

After creating a fresh version of uspMySecondStoredProcedure, you can run an exec statement in another script that runs the stored proc and then use the global temp tables in any way your requirements dictate.  The following example merely lists the contents of both tables.

  • You can try out the code examples for this tip on a SQL Server with the AdventureWorks2014 database.  This prior MSSQLTips.com tip describes how to download a copy of the AdventureWorks2014 database.
  • Next, copy the script(s) that you want to test and modify.  Confirm your copied code generates valid results for the AdventureWorks2014 database.
  • Finally, modify the script(s) to reference another source database with selected columns from tables that follow from your requirements.

sql server categories

About the author

MSSQLTips author Rick Dobson

Comments For This Article

Related articles.

  • SQL Server training
  • Write for us!

Hadi Fadlallah

Execute SQL Task in SSIS: Output Parameters vs Result Sets

In the previous article, I gave an overview of Execute SQL Task in SSIS and we illustrated some of the differences between writing an expression to evaluate SqlStatementSource property and writing this expression within a variable and change the Execute SQL Task Source Type to variable.

In this article, I will describe the difference between using output parameters and result sets within Execute SQL Task in SSIS.

Note that this article is the fifth article in the SSIS feature face to face series , which aims to remove confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.

Execute SQL Task in SSIS allows user to execute parameterized SQL statement and create mapping between these parameters and the SSIS variables. To add a parameter into a SQL statement you must use a parameter marker which differs based on the connection type.

There are three types of parameters that can be used within an Execute SQL Task in SSIS:

  • Input parameters: used to pass a value as a parameter within a SQL command or stored procedure
  • Output parameters: used to store a value generated from an SQL command or stored procedure
  • Return Value: used to store a value returned by an SQL command or stored procedure

When using Execute SQL Task in SSIS, to map a parameter to a variable, you must go to the Parameter Mapping tab, and define the variable mapping.

As example, in the Execute SQL Task, we defined the following SQL command:

Now, if we click on the Parameter Mapping tab, we should see the following form:

This image shoes the Parameter Mapping Tab within Execute SQL Task in SSIS

Figure 1 – Parameter Mapping Tab

To add a parameter mapping, we must click on the Add button, since we have on specified one parameter in the SQL command then we should add only one mapping. When we click on the Add button, one line is added to the grid as shown in the image below:

This image shows how Parameter Mapping is added in Execute SQL Task in SSIS

Figure 2 – Adding Parameter Mapping

Within Execute SQL Task in SSIS, you have to configure the following properties of each parameter:

  • Variable Name: Select the variable name that you want to map to a parameter
  • Direction: Specify if the type of the parameter (input, output, return value)
  • Data Type: Specify the data type of the parameter (It must be compatible with the data type of the variable)

Parameter Name: The name of the parameter, the naming convention depends on the connection type:

  • Parameter Size: Specify the length of the parameter when using string data types otherwise it must be -1 (default value)

Output parameters

When it comes to output parameters, they are used to store values that can be set at any part of the SQL command, they have the same concept of the stored procedure output parameters. As example, if we can use a similar SQL command to store the Maximum value of BusinessEntityID column:

Then we have to configure the parameter mapping as shown in the image below:

This image shows how an output parameter is configured in Execute SQL Task in SSIS

Figure 3 – Output Parameter example

If we add a breakpoint on the PostExecute event of the Execute SQL Task, and we check the variable value, it shows the Maximum BusinessEntityID value as shown below:

This image shows the value of the output parameter configure in Execute SQL Task

Figure 4 – Output Parameter Value

In addition, the output parameter can be defined in a stored procedure as the following:

You can refer to the Microsoft official documentation to learn more about Execute SQL Task in SSIS parameters and return values.

Result Sets

When using an Execute SQL Task in SSIS, result sets can be generated from an SQL command mostly a SELECT query. There are many types of result sets:

  • None: No result set is generated
  • Single Row: When the result set is a single row, such as SELECT TOP 1 or a SELECT MAX() commands
  • Full Result set: When the SQL statement generates multiple rows such as a SELECT * command
  • XML: This option is used to store the result within an XML value

You can select the result set type from the Execute SQL Task editor (General Tab):

This image shows where to change the Result Set type in Execute SQL Task in SSIS

Figure 5 – Result Set type selection

To store the result set into a variable, we must configure the variable mapping within the Result Set tab.

This image shows the Result Set Tab in Execute SQL Task in SSIS

Figure 6 – Result Set Tab

In the Result Set tab, you must specify the result name and the variable that you want to map to. If the result set type is Single row , the result name must be the column name or the column position in the column list. If the result set type is Full result set or XML, you must use 0 as the result set name.

When it comes to variable mapping each type of result set must be mapped to specific variable data types:

  • Single Row: The variable data type depends on the returned column data type
  • Full Result Set: The variable must be a System.Object variable and the Object type is an ADO Recordset mostly or a System.Data.Dataset in case of ADO.NET connection
  • XML: The variable must be a System.String variable where the result will be stored as an XML string, or a System.Object variable where the result set is stored as MSXML6.IXMLDOMDocument mostly or System.Xml.XmlDocument when using an ADO.NET connection

When using Single Row result set or XML string, values are stored within variable and can be consumed directly within Expressions, Tasks, Scripts or Transformations. But, when the result set is stored within a variable of type System.Object , the variable can be consumed using:

  • ADO enumerator within a Foreach Loop container : This option is used to loop over the rows of the result set and consume them row by row by mapping each row columns with SSIS variables

Using a .Net Script (Task / Component): The code differs based on the Object type:

System.Data.DataSet:

ADO RecordSet:

System.Xml.XmlDocument:

Note that, an ADO RecordSet variable can be consumed only one time.

For additional information you can refer to the official documentations:

  • Result Sets in the Execute SQL Task
  • Execute SQL Task Editor (Result Set Page)
  • Execute SQL Task in SSIS

Output Parameters Vs Result Sets

Many times, I was asked on the differences between using output parameters and Result Set and which is more recommended when using Execute SQL Task in SSIS. In general, each one of these options has its own use cases even if there are some similarities. In this section, I will try to illustrate the differences and similarities between these two options.

  • When the result consists of multiple rows, the output parameter cannot, since they don’t allow to store a table valued results

If we need to store values from different SQL command we cannot use Result Sets, while output parameters can do the trick:

In case that we need to retrieve a value from a query located in the middle of the whole SQL statement and reuse this value within the same SQL statement, we need output parameters:

  • Result Set cannot store a Return Value

Result Set:

Parameters:

Both options can store XML results (To use parameters you must use FOR XML clause in the SQL Ssatement)

There are many cases where output parameters and result sets are used in the same Execute SQL Task in SSIS:

  • From a performance perspective, there is a difference between using parameters and Result Set, since in general returning scalar values is more efficient then using Result Set since the second carries a lot of helper methods

External Links and References

  • Map Query Parameters to Variables in an Execute SQL Task
  • Execute SQL Task output parameter vs ResultSet

Table of contents

  • Recent Posts

Hadi Fadlallah

  • An overview of SQL Server monitoring tools - December 12, 2023
  • Different methods for monitoring MongoDB databases - June 14, 2023
  • Learn SQL: Insert multiple rows commands - March 6, 2023

Related posts:

  • SSIS XML Source vs. XML task
  • An Overview of the XML Task in SSIS Packages
  • SSIS Expression Tasks vs Evaluating variables as expressions
  • Overview of SSIS Package Logging
  • SSIS Foreach Loop vs For Loop Container
  • Skip to main content
  • Skip to search
  • Skip to select language
  • Sign up for free
  • English (US)

RegExp.prototype.exec()

The exec() method of RegExp instances executes a search with this regular expression for a match in a specified string and returns a result array, or null .

The string against which to match the regular expression. All values are coerced to strings , so omitting it or passing undefined causes exec() to search for the string "undefined" , which is rarely what you want.

Return value

If the match fails, the exec() method returns null , and sets the regex's lastIndex to 0 .

If the match succeeds, the exec() method returns an array and updates the lastIndex property of the regular expression object. The returned array has the matched text as the first item, and then one item for each capturing group of the matched text. The array also has the following additional properties:

The 0-based index of the match in the string.

The original string that was matched against.

A null -prototype object of named capturing groups, whose keys are the names, and values are the capturing groups, or undefined if no named capturing groups were defined. See capturing groups for more information.

This property is only present when the d flag is set. It is an array where each entry represents the bounds of a substring match. The index of each element in this array corresponds to the index of the respective substring match in the array returned by exec() . In other words, the first indices entry represents the entire match, the second indices entry represents the first capturing group, etc. Each entry itself is a two-element array, where the first number represents the match's start index, and the second number, its end index.

The indices array additionally has a groups property, which holds a null -prototype object of all named capturing groups. The keys are the names of the capturing groups, and each value is a two-element array, with the first number being the start index, and the second number being the end index of the capturing group. If the regular expression doesn't contain any named capturing groups, groups is undefined .

Description

JavaScript RegExp objects are stateful when they have the global or sticky flags set (e.g. /foo/g or /foo/y ). They store a lastIndex from the previous match. Using this internally, exec() can be used to iterate over multiple matches in a string of text (with capture groups), as opposed to getting just the matching strings with String.prototype.match() .

When using exec() , the global flag has no effect when the sticky flag is set — the match is always sticky.

exec() is the primitive method of regexps. Many other regexp methods call exec() internally — including those called by string methods, like @@replace . While exec() itself is powerful (and is the most efficient), it often does not convey the intent most clearly.

  • If you only care whether the regex matches a string, but not what is actually being matched, use RegExp.prototype.test() instead.
  • If you are finding all occurrences of a global regex and you don't care about information like capturing groups, use String.prototype.match() instead. In addition, String.prototype.matchAll() helps to simplify matching multiple parts of a string (with capture groups) by allowing you to iterate over the matches.
  • If you are executing a match to find its index position in the string, use the String.prototype.search() method instead.

Using exec()

Consider the following example:

The following table shows the state of result after running this script:

In addition, re.lastIndex will be set to 25 , due to this regex being global.

Finding successive matches

If your regular expression uses the g flag, you can use the exec() method multiple times to find successive matches in the same string. When you do so, the search starts at the substring of str specified by the regular expression's lastIndex property ( test() will also advance the lastIndex property). Note that the lastIndex property will not be reset when searching a different string, it will start its search at its existing lastIndex .

For example, assume you have this script:

This script displays the following text:

Warning: There are many pitfalls that can lead to this becoming an infinite loop!

  • Do not place the regular expression literal (or RegExp constructor) within the while condition — it will recreate the regex for every iteration and reset lastIndex .
  • Be sure that the global ( g ) flag is set, or lastIndex will never be advanced.
  • If the regex may match zero-length characters (e.g. /^/gm ), increase its lastIndex manually each time to avoid being stuck in the same place.

You can usually replace this kind of code with String.prototype.matchAll() to make it less error-prone.

Using exec() with RegExp literals

You can also use exec() without creating a RegExp object explicitly:

This will log a message containing 'hello world!' .

Specifications

Browser compatibility.

BCD tables only load in the browser with JavaScript enabled. Enable JavaScript to view data.

  • Regular expressions guide

Assigning an Expression Result

Simple enough. But REXX expressions can have very complex forms as well, and they can work with all kinds of information. They are our topic for the next chapter.

Reading 1 continues in Expressions .

IMAGES

  1. EXEC SQL overview and examples

    assign exec result

  2. EXEC SQL overview and examples

    assign exec result

  3. EXEC SQL overview and examples

    assign exec result

  4. Assign an Excel function result to a variable within a cell

    assign exec result

  5. How to Assign the Result of exec() to a Python Variable?

    assign exec result

  6. Add Checkbox to select all tests to assign in exec

    assign exec result

VIDEO

  1. കണക്ക് തീർത്ത് ബ്ലാസ്റ്റേഴ്സ് #keralablasters #shorts #youtubeshorts

  2. Zaba E Zaiba E Zabaiba Ta Zaibal Zaiba E Sing By Jameel Jani Vol 02

  3. MS Excel Part 03 IT result Sheet

  4. Excel Mcq 39

  5. Full Set With Advance Excel

  6. EXCEL/SPREADSHEET || 2023 में पूछे जाने वाले सवाल || PAPER 1 || TARGET NET JRF

COMMENTS

  1. How to assign an exec result to a sql variable?

    How to assign an exec result to a sql variable? Ask Question Asked 14 years ago Modified 5 months ago Viewed 407k times 139 How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay, which returns a single date. Can you do something like this:

  2. How to Assign the Result of exec() to a Python Variable?

    Method 1: Define Variable in String Representation of Code You can define a variable inside the exec () function that will then be added to the global namespace. Thus, you can access the defined variable in your code after the termination of the exec () expression! Here's how that works in a minimal example: exec('my_result = 40 + 2')

  3. How to save result from a Stored Procedure in a Linked Server to a

    1 I need to read the results of a stored procedure in a linked server, and store them in a variable, or a temporary table if not possible. I can do this, and I get the correct result: EXEC ('EXEC DBName.dbo.procedure_name') AT [xxx.xx.xxx.xx] However, when I try to do something like this:

  4. Python's exec(): Execute Dynamically Generated Code

    Python's built-in exec () function allows you to execute arbitrary Python code from a string or compiled code input. The exec () function can be handy when you need to run dynamically generated Python code, but it can be pretty dangerous if you use it carelessly.

  5. Return data from a stored procedure

    As with output parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program. For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc, such as: SQL. DECLARE @result int; EXECUTE @result = my ...

  6. Working with parameters in the sp_executesql stored procedure

    EXEC sp_executesql @SQL, @params, @FirstName = 'Jossef', @Lastname = 'Goldberg'; In the first two lines, we defined the variables used to build the dynamic SQL statement as explained previously. In the third line, we created a variable that stores the definition of the parameters. When executing the stored procedure, we pass the variable that ...

  7. assigning exec query result to variable

    assigning exec query result to variable danielsisk619 SSC Veteran Points: 282 More actions May 8, 2008 at 3:27 pm #75210 I'm trying to get a count of rows for each column that has info in it. I...

  8. EXEC SQL overview and examples

    1. EXEC ('use testdb; EXEC TestProcedure') at [TEST01V] Following is the example of executing a stored procedure on the linked server using four-part notation. Here " TEST01V" is the server name, " test " is the database name, and " dbo " is the schema name. 1. EXEC [TEST01V].test.dbo.testProc.

  9. How to store results from EXEC into an @variable [duplicate]

    How to assign an exec result to a sql variable? (7 answers) Closed 9 years ago. I had the same issue located here Table name as variable My question is, how can I store the results returned from the EXEC statement into a @variable ? Example: EXEC ('SELECT count (*) FROM ' + @tablename) Thanks sql sql-server Share Improve this question Follow

  10. Python exec() (With Examples)

    Example 3: exec () with a Multi-line Input Program. We can pass a multi-line input program to the exec () method with the help of \n. But we need to use the compile () method to compile the program first. # compile the program in execution mode b = compile (program, 'something', 'exec')

  11. How to put result from EXEC into a variable

    If you want to execute a script and use the result value, probably the solution presented by Guy is the only way to do it. Insert the result into a temporary table and read it into a variable. But if you use a stored procedure instead of the scrip, you can use EXEC to transfer result directly into the variable. EXEC @Result = YourSP(Parameters ...

  12. Assign EXEC output to Variable

    Assign EXEC output to Variable. Archived Forums 421-440 > Transact-SQL. Question; 2. Sign in to vote. Hi, I have variable as DECLARE @Count int; SET SET @Count = 0; I have some count statement command as follows - ...

  13. SQL SERVER

    [HumanResources]. [Department] WHERE DepartmentID = @ID' EXEC sp_executesql @sqlCommand, N'@ID INT', @ID = @ID Now the question is how to get the value of the column [Name] into a variable. Here is the very easy trick for the same. Here is the script which declares additional parameter which is Name and returns value into it.

  14. Save SQL Server Stored Procedure Results to Table

    In the first step, create a fresh copy of the stored procedure with a select statement that generates a results set whose output you want to persist. In the second step, create a local temp table outside of the stored procedure. With a local temp table, the data in the table exists for the duration of the session creating the local temp table ...

  15. sql

    Insert result of EXEC into table parameter Ask Question Asked 4 years, 11 months ago Modified 4 years, 11 months ago Viewed 5k times 0 In a stored procedure, I have an EXEC statement; I want to store its results into a table. First I create table parameter as: DECLARE @MyTable AS TABLE ( [Item1] INT, [Item2] DECIMAL )

  16. Execute SQL Task in SSIS: Output Parameters vs Result Sets

    To add a parameter into a SQL statement you must use a parameter marker which differs based on the connection type. There are three types of parameters that can be used within an Execute SQL Task in SSIS: Input parameters: used to pass a value as a parameter within a SQL command or stored procedure

  17. Assiging stored procedure results to SSIS variable

    The same holds true when you're using the Execute SQL Task within SSIS. You must specify that the parameter is on OUTPUT and also specify it in the Parameter mappings tab. Also specify the variable you want to map and use the OUTPUT direction there. Here I've mapped the result into an SSIS Variable of type Int32 called orderCount. Single Result Set

  18. Assign result of dynamic sql to variable

    5 Answers Sorted by: 106 You can use sp_executesql with output parameter. declare @S nvarchar (max) = 'select @x = 1' declare @xx int set @xx = 0 exec sp_executesql @S, N'@x int out', @xx out select @xx Result: (No column name) 1 Edit In my sample @S is instead of your @template.

  19. RegExp.prototype.exec()

    Return value. If the match fails, the exec () method returns null, and sets the regex's lastIndex to 0. If the match succeeds, the exec () method returns an array and updates the lastIndex property of the regular expression object. The returned array has the matched text as the first item, and then one item for each capturing group of the ...

  20. How to get sp_executesql result into a variable?

    DECLARE @retval int DECLARE @sSQL nvarchar (500); DECLARE @ParmDefinition nvarchar (500); DECLARE @tablename nvarchar (50) SELECT @tablename = N'products' SELECT @sSQL = N'SELECT @retvalOUT = MAX (ID) FROM ' + @tablename; SET @ParmDefinition = N'@retvalOUT int OUTPUT'; EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT; SELE...

  21. Assigning an Expression Result

    Take another look at the program ASSIGN EXEC in Figure 1.The instruction amount = amount + 25 demonstrates how variables can represent another kind of unknown information: data that must be calculated or otherwise manipulated. You can simply assign to a variable the result of a calculation or expression.Here is another example: Figure 1. AREAS EXEC