ExecSQL

Enables capturing secondary SQL statement outputs, such as recordsets, print messages and more.

Stored Procedure
SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017

Syntax:

EXEC SQLHTTP.net.ExecSQL @SQL [, @TableIndex][, @ConnectionString][, @DisplayTableDefinition][, @Messages xml OUTPUT]

Arguments:

Name	Type	Description
@SQL	nvarchar(MAX)	SQL Statements to be executed.
@TableIndex	int	Optional. The ordinal position of the table to retrieve. Default: 1
@ConnectionString	nvarchar(4000)	Optional. A connection string to a SQL Server instance, if not the current SQL Server instance, or if alternate credentials are needed.
@DisplayTableDefinition	bit	Optional. A boolean indicator. If 1 (true), a table definition of the retrieved recordset will be outputted as well. Default 0 (false)
@Messages	xml	Optional. Output Parameter. An XML containing all the messages and/or errors collected during the procedure’s execution.



Remarks:

This stored procedure has many similarities to SQL Server’s EXEC statement and sp_executesql stored procedure, but also has the following enhancements:

The @SQL parameter is of type nvarchar(MAX) which allows for a larger script to be executed.
Ability to retrieve any of the recordsets generated by the SQL script, not only the first one.
A Connection String to a remote server without the use of a linked server.
Display of the table structure of the outputted recordset for easy usage in an INSERT… EXEC statement.
An XML output of all messages including error messages.



Sample Usage:


SET NOCOUNT ON

DECLARE @SQL nvarchar(MAX)
DECLARE @Messages xml

SET @SQL = '
SELECT 1 AS SingleCellTable1
SELECT 2 AS SingleCellTable2
PRINT ''This text was generated using the PRINT statement''
SELECT 1/0'

EXEC SQLHTTP.net.ExecSQL @SQL = @SQL,
                         @TableIndex = 2,
                         @DisplayTableDefinition = 1,
                         @Messages = @Messages OUTPUT

SELECT  T.C.value(N'@Order', N'nvarchar(MAX)') AS [Order]
       ,T.C.value(N'@IsError', N'nvarchar(MAX)') AS [IsError]
       ,T.C.value(N'.', N'nvarchar(MAX)') AS [Message]
FROM @Messages.nodes(N'/Messages/Message') T(C)

SET NOCOUNT ON

DECLARE @SQL nvarchar(MAX)

DECLARE @Messages xml

SET @SQL = '

SELECT 1 AS SingleCellTable1

SELECT 2 AS SingleCellTable2

PRINT ''This text was generated using the PRINT statement''

SELECT 1/0'

EXEC SQLHTTP.net.ExecSQL @SQL = @SQL,

@TableIndex = 2,

@DisplayTableDefinition = 1,

@Messages = @Messages OUTPUT

SELECT T.C.value(N'@Order', N'nvarchar(MAX)') AS [Order]

,T.C.value(N'@IsError', N'nvarchar(MAX)') AS [IsError]

,T.C.value(N'.', N'nvarchar(MAX)') AS [Message]

FROM @Messages.nodes(N'/Messages/Message') T(C)

SingleCellTable2

----------------------------

Order IsError Message

--------- ----------- -------------------------------------------------------------

1 false This text was generated using the PRINT statement

2 true Msg 8134, Level 16, State 1, Line 0

3 true Divide by zero error encountered.

DECLARE @Table TABLE(
[SingleCellTable2] int NULL)

INSERT INTO @Table([SingleCellTable2])
EXEC SQLHTTP.net.ExecSQL
@SQL = ‘
SELECT 1 AS SingleCellTable1
SELECT 2 AS SingleCellTable2
PRINT ”This text was generated using the PRINT statement”
SELECT 1/0’,
@TableIndex = 2



ExecSQL

Featured API Wrapper Code / SQL Server