Using ExecuteNonQuery to run a Stored Procedure isn't creating my table, but the table is created when executing the sp in SSMS

Question

Some notes:

1. ExecuteNonQuery returns -1
2. ExecuteNonQuery will drop the table (@droptable), but it will not create the new table (@code)
3. the length of the @code query is 10265 characters
4. The stored procedure runs perfectly fine in SSMS and returns 22 rows in the table

Are there any ideas as to why C#'s ExecuteNonQuery function doesn't seem to be executing the 'exec(@code)' portion of the stored procedure?

ALTER procedure [dbo].[sp_create_EditControlResultsPivot] 
as
begin
    declare @t nvarchar (250); 
    set @t = 'editControlResults'

    declare @newtable nvarchar(250); 
    set @newtable = 'dbo.' + @t + 'Pivot'

    declare @nonPivotColumn1 nvarchar(250); 
    set @nonPivotColumn1 = 'num'

    declare @nonPivotColumn2 nvarchar(25); 
    set @nonPivotColumn2 = 'File_Name'

    declare @droptable nvarchar(max); 
    set @droptable =  
'if EXISTS (select * from sys.objects where object_id = object_id(N''' + @newtable + '''))
begin drop table ' + @newtable + ' end
'

    declare @i int 
    set @i = 1;

    declare @itemList nvarchar(max);
    declare @code nvarchar(max);

    while @i <= (
        select COUNT(*) 
        from sys.columns c 
        left join sys.tables t on c.object_id = t.object_id 
        where 1=1 
          and c.name not like @nonPivotColumn1 
          and c.name not like @nonPivotColumn2
          and t.name = @t
    ) 
    begin

    set @itemList = @itemList + ', ' +
    (
        select col from 
        (
            select c.name as col, ROW_NUMBER () over (order by c.name) as num from
            sys.columns c left join sys.tables t on c.object_id = t.object_id 
            where 1=1
            and c.name not like @nonPivotColumn1
            and c.name not like @nonPivotColumn2
            and t.name = @t 
        ) sub where num = @i
    )
    set @i = @i + 1
  end

  set @itemList = (select substring(@itemList, 2, LEN(@itemList)))

  set @code = '
  SELECT ' + @nonpivotcolumn2 + ', Item
  into ' + @newtable + '
FROM
(SELECT ' + @nonpivotcolumn2 + ', ' + @itemList + '
FROM ' + @t + ') sub
UNPIVOT
(Value FOR Item IN (' + @itemList + ')
) AS sub
where Value = ''true''
'

exec(@droptable)
exec(@code);
--print(len(@code))
END
--exec sp_create_EditControlResultsPivot

Answer 1

The ExecuteNonQuery Method returns the number of rows affected use the ExecuteReader method instead.

SqlCommand.ExecuteReader Method

The only way to return data from ExecuteNonQuery would be via an Output parameter.

Answer 2

I suspect your comment #3. the length of the @code query is 10265 characters...could be an issue...I think the call from C# is chopping it to only 4000 or 8000 chars...

Since you are not expecting a resultset, ExecuteNonQuery is good.

Things to try:

1. Try inserting the content of the @code variable (inside the procedure) in a table and see if you are getting the correct sql...both when executed from SSMS and from C# call

2. If you get a valid sql query in step 1 (which I doubt)...try executing that query in SSMS to see if it really works...