PROCEDURE Return always 0 for string statement

I have a procedure that can be changed dynamically by user for multi column and I write it in SQL when I run it. Everything is OK in SQL and Server Explorer in Visual Studio but when I want use it in C# and call it, it just return 0 always. Can anybody help me?!

CREATE PROCEDURE [dbo].[PDaynamicActualBy2Column]
    @Colname1 nvarchar(100),
    @VarCol1 nvarchar(100),
    @Colname2 nvarchar(100),
    @VarCol2 nvarchar(100),
    @VarWeekNum nvarchar(100)
as
    DECLARE @temp nvarchar(1500)
    set @temp='SELECT SUM([dbo].[WeekActualTemp].[ACTUAL]) from [dbo].[MAINTB] join [dbo].[WeekActualTemp] on [dbo].[MAINTB].[UQ]=[dbo].[WeekActualTemp].[UQ] 
    where [dbo].[WeekActualTemp].[WeekNO]='+@VarWeekNum+' And [dbo].[MAINTB].'+@Colname1+' = '''+@VarCol1+''' And [dbo].[MAINTB].'+@Colname2+' = '''+@VarCol2+''''
    exec (@temp)

Answer

In LINQ you cannot call an SP that have a meta output that is dynamic, you have to write your SP with “select” output and make a model and then go to SP and edit it again.

Alter PROCEDURE [dbo].[PDaynamicActualBy2Column]
    @Colname1 nvarchar(100),
    @VarCol1 nvarchar(100),
    @Colname2 nvarchar(100),
    @VarCol2 nvarchar(100),
    @VarWeekNum nvarchar(100)
as
    DECLARE @temp nvarchar(1500)
    set @temp='SELECT SUM([dbo].[WeekActualTemp].[ACTUAL]) from [dbo].[MAINTB] join [dbo].[WeekActualTemp] on [dbo].[MAINTB].[UQ]=[dbo].[WeekActualTemp].[UQ] 
    where [dbo].[WeekActualTemp].[WeekNO]='+@VarWeekNum+' And [dbo].[MAINTB].'+@Colname1+' = '''+@VarCol1+''' And [dbo].[MAINTB].'+@Colname2+' = '''+@VarCol2+''''
  //  exec (@temp)

SELECT top 0
SUM([dbo].[WeekActualTemp].[ACTUAL]) as sum
from [dbo].[MAINTB] join [dbo].[WeekActualTemp] on [dbo].[MAINTB].[UQ]=[dbo].[WeekActualTemp].[UQ] 

then import SP in your LINQ then comment “select” and uncomment “exec” .