Input Data - SQL Server Stored Procedure With Dynamic Queries - No Columns Returned

nmotikane

Hi All,

We have a very complicated Stored Procedure that has quite a few Dynamic Queries. We are having a tough time trying to successfully bring data into Alteryx calling this stored procedure.

If someone can help solve this issue, with below Stored Procedure as an example, it will be greatly appreciated.

CREATE PROCEDURE ProcedureWithDynamicQuery
AS
BEGIN
DECLARE @SelectStatement NVARCHAR(2000)

SET @SelectStatement = 'SELECT GETDATE() '

EXECUTE (@SelectStatement)
END

Thanks

ScottG

I think you want to set NOCOUNT on and then, most importantly, use the WITH RESULTS SETS clause to specify the metadata of the resultset.  Alteryx should then have all it needs.

CREATE PROCEDURE ProcedureWithDynamicQuery

AS
BEGIN
SET NOCOUNT ON;
DECLARE @SelectStatement NVARCHAR(2000)
SET @SelectStatement = 'SELECT GETDATE() AS DT'
EXECUTE (@SelectStatement) WITH RESULT SETS ((DT DATE))
END

GO