user3005015 user3005015 - 2 months ago 20
SQL Question

Must declare scalar var ... using dynamic tablename

I am trying to use a variable tablename in the following SQL, but I keep getting


Must Declare scalar variable @Projectnr


on executing from C# program.

The values are passed by the program, I'm sure this is somewhere in this SQL that things are mixed up...

this is the SQL:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Insert_ProjectWerktijden]

@Projectnr VARCHAR(50),
@Tasknr VARCHAR(50),
@Datum DATETIME,
@Startuur DATETIME,
@Einduur DATETIME,
@WerktijdDEC float,
@TijdTijd DATETIME,
@Scanner VARCHAR(50),
@StartID int,
@EindID int,
@Functiecode int,
@Naam varchar(100),
@table NVARCHAR(50)



AS
BEGIN
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
SET @sql= 'INSERT INTO **TABLE**
(
Projectnr,
Taaknummer,
Datum,
Start,
Einde,
Tijd,
TijdTijd,
Scanner,
StartID,
EindID,
Functiecode,
Naam
)
VALUES
(
@Projectnr,@Tasknr,@Datum,@Startuur,@Einduur,@WerktijdDEC,@TijdTijd,@Scanner,@StartID,@EindID,@Functiecode,@Naam
)
';
SET @sql = REPLACE(@sql, '**TABLE**', @table);
EXECUTE(@sql);
END


and the C# code

public static void TijdSaven(string projectnr, string tasknr, DateTime datum, DateTime? start, DateTime? einde, double tijd, DateTime tijdtijd,string scanner, int beginid, int eindid,int functiecode,string naam,string table)
{
if (tasknr.Contains("X")) { tasknr = "n.v.t.";}
ctx = new DataClassesDataContext();
ctx.sp_Insert_ProjectWerktijden(projectnr, tasknr, datum, start, einde, tijd, tijdtijd, scanner, beginid, eindid,functiecode,naam,table);
ctx.SubmitChanges();
}

Answer

If you do PRINT(@sql) before executing you will see that query to run is:

INSERT INTO **TABLE**
  ( 
        Projectnr, 
        Taaknummer,
        Datum,
        Start,
        Einde,
        Tijd,
        TijdTijd,
        Scanner,
        StartID,
        EindID,
        Functiecode,
        Naam
)
VALUES
(
@Projectnr,@Tasknr,@Datum,@Startuur,@Einduur,@WerktijdDEC,@TijdTijd,@Scanner,@StartID,@EindID,@Functiecode,@Naam
)

In this query there is no declaration to your variables, that is why you are getting error.

Instead of EXECUTE(@sql); better use parameters:

DECLARE @ParmDefinition nvarchar(MAX); 

SET @ParmDefinition = N'   
@Projectnr VARCHAR(50),
@Tasknr VARCHAR(50),
@Datum DATETIME,
@Startuur DATETIME,
@Einduur DATETIME,
@WerktijdDEC float,
@TijdTijd DATETIME,
@Scanner VARCHAR(50),
@StartID int,
@EindID int,
@Functiecode int,
@Naam varchar(100)
'; 
--I guess table name must be replaced, you cannot use parameter for that one
SET @sql = REPLACE(@sql, '**TABLE**', QUOTENAME(@table));

EXEC sp_executesql @sql, @ParmDefinition, 
                            @Projectnr = @Projectnr,
                            @Tasknr = @Tasknr,
                            @Datum = @Datum,
                            @Startuur = @Startuur,
                            @Einduur = @Einduur,
                            @WerktijdDEC = @WerktijdDEC,
                            @TijdTijd = @TijdTijd,
                            @Scanner = @Scanner,
                            @StartID = @StartID,
                            @EindID = @EindID,
                            @Functiecode = @Functiecode,
                            @Naam = @Naam

More info here.