SQL Query Returns Different Results MSSQL vs Database Query Browser

#1

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
AS

DECLARE @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:
StoredProcedure_ignition

0 Likes

#2

In your stored procedure you need to add the line “SET NOCOUNT ON;” to the immediate beginning.

ALTER PROCEDURE [dbo].[recordSiloLevel]
	-- Add the parameters for the stored procedure here
	@inputXML as NVARCHAR(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Your Procedure Here...
END
0 Likes

#3

Thanks Tim! That did the trick!

0 Likes