0

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.

2
  • 1
    Just a note that you can skip those TOP 100 PERCENT and ORDER BY in those CTEs, they are removed by the optimizer and don't do anything. ORDER BY for sorting belongs to the outer query - when data leaves SQL Server going to the client app. As for the question in hand, the error seems strange for the SQL you posted. Can you produce a repro? – Tibor Karaszi Sep 9 '20 at 7:30
  • Can you run the code outside of a stored procedure? – Jonathan Fite Sep 9 '20 at 14:19
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.