For the sake of future reference, I want to document a struggle that I encountered today with a Stored Procedure.

For the sake of brevity I have only included the "problem" part.

So I have/had a stored procedure that included this Select statement

Code:
SELECT LEFT([TEXT],6) as Code, COUNT(LEFT([TEXT],6)) as counted
--INTO #progstats
FROM FA_Lns 
    WHERE [TYPE] = 'M'
    AND ORGUUID = @orguuid
	AND
        1 = 
        CASE WHEN @field = '*all' THEN 1
        ELSE
           CASE WHEN @field = LEFT([TEXT],6) THEN 1
	         ELSE 0
           END
        END


	AND NUMBER IN 
	    (SELECT [NUMBER] FROM FA_Hdr 
		    WHERE [DATE] BETWEEN @periodbegin AND @periodend
			AND ORGUUID = @orguuid
			AND 
               1 = 
               CASE WHEN @source BETWEEN 1 AND 5 THEN
                  CASE WHEN SOURCE =@source THEN 1
             	 ELSE 0
             	 END
               ELSE
               1
               END


			AND TYPE ='C' )  --'E' FOR CHILDREN
GROUP BY LEFT([TEXT],6)
It works fine in SSMS and also if I stop my DF program and copy the EXEC statement from the program to the SSMS, it also runs fine.

Code:
-- copied from dataflex
EXEC [dbo].[PR_TotalQtyCount]
 @yrbegin = '2021-04-01',
 @yrend = '2022-03-31',
 @periodbegin = '2021-04-01',
 @periodend = '2022-03-31',
 @source = 1,
 @orguuid = '0021f322-7396-4926-8453-105730c067b5',
 @counter = 'Total',
 @type = 'QtyCount',
 @field = '*all'
I have close to 250 stored procedures, but this one had me stumped. Why would it work well through SSMS but not when being called from my program
BTW SET NOCOUNT ON was set in the procedure.

Anyway after many hours of tinkering I found that if I avoided the SELECT LEFT([TEXT],6) as Code, COUNT(LEFT([TEXT],6)) as counted, then I could get the query to work (albeit not returning the meaningful results that I was wanting.)

I had to take the long way around and modify the procedure so that I avoided the highlighted text above, and ended up with this:

[CODE]
SELECT A.[CODE] as Code,
(SELECT COUNT([TYPE]) FROM FA_Lns
WHERE [TYPE] = 'M'
--AND [TEXT] LIKE CONCAT(A.[CODE],' - %')
AND LEFT([TEXT],6) = A.[CODE]
AND ORGUUID = @orguuid
AND NUMBER IN
(SELECT [NUMBER] FROM FA_Hdr
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
AND TYPE ='C') --'E' FOR CHILDREN
) as counted
FROM PROGS AS A
WHERE ORGUUID = @orguuid
AND
1 =
CASE WHEN @field = '*all' THEN 1
ELSE
CASE WHEN @field = A.CODE THEN 1
ELSE 0
END
END


AND (SELECT COUNT([TYPE]) FROM FA_Lns
WHERE [TYPE] = 'M'
--AND [TEXT] LIKE CONCAT(A.[CODE],' - %')
AND LEFT([TEXT],6) = A.[CODE]
AND ORGUUID = @orguuid
AND NUMBER IN
(SELECT [NUMBER] FROM FA_Hdr
WHERE [DATE] BETWEEN @periodbegin AND @periodend
AND ORGUUID = @orguuid
AND
1 =
CASE WHEN @source BETWEEN 1 AND 5 THEN
CASE WHEN SOURCE =@source THEN 1
ELSE 0
END
ELSE
1
END
AND TYPE ='C' ) --'E' FOR CHILDREN
) >0


GROUP BY A.
Code:

which works consistently between SSMS and when being called from DF.

My conclusion is that there is some sort of problem (some may even use the word "bug") when calling a Stored Procedure in SQL that uses the LEFT() function in part of the returned data set.