Function sequence error with SQL Stored Procedure

[saqib]

I have the following Stored Procedure in SQL Server. Whenever I try to call it using the Input tool, I get the following error msg:

Error: Dynamic Input (56): Error SQLExtendedFetch: [Microsoft][ODBC Driver 13 for SQL Server]Function sequence error

USE [DevSaqib]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspTryCatch]    
   
       AS         
   BEGIN  

     begin try
         PRINT 'test'
         SELECT 'Return Value'=0

     end try

     begin catch
         PRINT 'test failed'
         SELECT 'Return Value'=-1

     end catch    
  END -- proc end

Please advise.

[JohnPo]

Hi @saqib,

What driver are you using for the SQL Server? Also, can you please share the workflow through private message?

[karthikeyangaan]

Just to confirm. Was this resolved? I have same issue.

[StephanieVHeerden]

Good Morning

I had the same error and want to post the solution for those who are struggling to find the solution.

It is not an ALTERYX or SQL error but an ODBC limitation. Cannot have more than one select or declare/set variables in the stor_proc.

https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-queries/constructing-sq...

The SQL Server Native Client ODBC driver uses server cursors to implement the cursor functionality defined in the ODBC specification.

Server cursors do not support all Transact-SQL statements. Server cursors do not support any SQL statement that generates multiple result sets.

The following types of statements are not supported by server cursors:

• Batches

  SQL statements built from two or more individual SQL SELECT statements, for example:

  SELECT * FROM Authors; SELECT * FROM Titles  

• Stored procedures with multiple SELECT statements

  SQL statements that execute a stored procedure containing more than one SELECT statement. This includes SELECT statements that fill parameters or variables.

• Keywords

  SQL statements containing the keywords FOR BROWSE, or INTO.

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlextendedfetch-function

Function sequence error

(DM) An asynchronously executing function was called for the connection handle that is associated with the StatementHandle. This asynchronous function was still executing when the SQLExtendedFetchfunction was called. (DM) SQLExecute, SQLExecDirect, or SQLMoreResults was called for the StatementHandle and returned SQL_PARAM_DATA_AVAILABLE. This function was called before data was retrieved for all streamed parameters. (DM) The specified StatementHandle was not in an executed state. The function was called without first calling SQLExecDirect, SQLExecute, or a catalog function. (DM) An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called. (DM) SQLExecute, SQLExecDirect, SQLBulkOperations, or SQLSetPoswas called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. (DM) SQLExtendedFetch was called for the StatementHandle after SQLFetch or SQLFetchScroll was called and before SQLFreeStmt was called with the SQL_CLOSE option. (DM) SQLBulkOperations was called for a statement before SQLFetch, SQLFetchScroll, or SQLExtendedFetch was called, and then SQLExtendedFetch was called before SQLFreeStmt was called with the SQL_CLOSE option.

My example:

I declared and set a variable inside my SP which caused the error.

(Not posting the whole SP)

ALTER PROCEDURE [dbo].[spTemplate] (@TemplateYear as Int = NULL)
AS
BEGIN
declare @Year as Int;
if @TemplateYear is NULL
set @Year = cast(FORMAT(GETDATE(),'yyyy') as int);
else set @Year = @TemplateYear;


with Categories as
(
select distinct Chain, Vendor, Category
,count(Vendor) over(partition by Chain,Vendor) as CategoryCount
from [dbo].[TableName] as a
where [Year] >= (select max([Year])-1 from [dbo].[TableName])
group by Chain, Vendor,Category
)
--insert into @TemplateTable
SELECT
Chain, Vendor, Category,
v.[VarDescription] as Variable,
case when @TemplateYear = null then ....

I removed the variable and handled the parameter inside a case statement in the select:

ALTER PROCEDURE [dbo].[spTemplate] (@TemplateYear as Int = NULL)
AS
BEGIN

with Categories as
(
select distinct Chain, Vendor, Category
,count(Vendor) over(partition by Chain,Vendor) as CategoryCount
from [dbo].[TableName] as a
where [Year] >= (select max([Year])-1 from [dbo].[TableName])
group by Chain, Vendor,Category
)
--insert into @TemplateTable
SELECT
Chain, Vendor, Category,
v.[VarDescription] as Variable,
case when @TemplateYear = null then
cast(FORMAT(GETDATE(),'yyyy') as int)
else
@TemplateYear
end as [Year],...

[bagarem]

Hi,

This is a similar issue that Tableau or other ETLs processes have.

Include to your SP at the beginning the setting: "SET NOCOUNT ON"

It will stop the message that shows the count of the number of rows affected by a Transact-SQL statement

CREATE PROC <your SP>
AS

       SET NOCOUNT ON

       <SQL sentences, Vars, etc.>

      SELECT * FROM <myTable

GO

[_fegodoy]

Thanks @bagarem, it worked pretty well!

[IraWatt]

Nice fix @bagarem 😄