Getting the “Column name or number of supplied values does not match table definition” when I use CTEs in an IF statement inside a stored procedure

Question

I have a Stored Procedure that has 3 parameters. The stored procedure looks like this:

ALTER PROC [dbo].[Trans_sp_test] (@Load bit = '',@ShowMsg bit = '', @File bit = '')
AS

SET NOCOUNT ON
DECLARE @transmsg varchar (80)

IF @Load = 1
BEGIN

;with cte1 as (

 select top 100 percent
    field1,
    field2,
    field3
 from dbo.table1

 order by field1
), cte2 as (
  select top 100 percent
     field4,
     field5
  from dbo.table2

  order by field4
)

select distinct
 _cte1.*,
 _cte2.* 
from cte1 _cte1
inner join cte2 _cte2 on _cte2.field4 = _cte1.field1

END

The stored procedure compiles just fine: however, when I try to execute it using the parameter @Load= 1, for example, (exec Trans_sp_test @Load = 1), I get an error:

"Column name or number of supplied values does not match table definition".

I have verified the columns multiple times and ensured that I have the correct number of columns, but I am still getting the error. At this point, I'm not sure what am I missing, or if there's an error in my code. I appreciate all your help.

Answer 1

This error message is generally associated with an insert, which you don't have in your sample code. Assuming this is representative of your proc, I would guess you are using an INSERT INTO ... EXEC with the proc. Since that take the first results set, it would mean that this supplants another result set that is returned when @Load = 0.

Under that assumption, since one works, and the other does not, it would also mean those have different columns. If it was your intent to have this result set replace the other, then the solution is to make the columns of the conditional query match those of the second.

Otherwise, your code is not representative of the problem you are encountering.