I have a stored procedure named GetRowNumforFace constructed as follows:
USE [solidiadb]
GO
/****** Object: StoredProcedure [dbo].[GetRowNumforFace] Script Date: 10/10/2019 1:34:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetRowNumforFace] @BatchID int
ASDECLARE @faces AS varchar(50);
SET @faces = (SELECT ChildFaceMixID_new FROM BatchSheetGeneral WHERE BatchSheetID = @BatchID);DECLARE @sql AS nvarchar(MAX);
SET @sql = 'SELECT t.row_num
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY BatchSheetID DESC) - 1 AS row_num, BatchSheetID
FROM BatchSheetGeneral
WHERE UseTypeID = 2 AND activeflag = 1
) t
WHERE t.BatchSheetID IN ('+@faces+')';DECLARE @tmpTbl TABLE(row_num int);
INSERT INTO @tmpTbl EXEC sp_executesql @sql;DECLARE @result AS varchar(MAX);
SELECT @result = COALESCE(@result + ',', '') + CAST(row_num AS varchar(50)) FROM @tmpTbl;
SELECT @result AS row_num;
When I execute the query in MSSQL and Database Query Browser in Ignition I get different results. Basically, when run in Ignition says its an empty result set in a Data binding to custom property and when I run in the database query browser it is in fact empty.
MSSQL:
Ignition: