A perfect example of why stored procedures don't make you immune to
SQL Injection[
^] vulnerabilities.
NEVER use string concatenation to build a SQL query.
ALWAYS use a parameterized query.
Within SQL, use
sp_executesql[
^] to execute a dynamic query with parameters.
You also have extra trailing commas within your dynamic query which need to be removed.
CREATE PROC [dbo].[spMonthInfo]
(
@Periode1 NVARCHAR(255),
@Periode2 NVARCHAR(255)
)
AS
BEGIN
DECLARE @Dynamictbl nvarchar(MAX) = N'SELECT
[t].[Monat], [t].[Project],[t].[Description],
[t].[Finance], --[t].[Project] AS [Entrance],
SUM(CASE WHEN [t].Monat = @Periode1 THEN [t].[Amount1] END) AS [Amount1Feb],
SUM(CASE WHEN [t].Monat = @Periode2 THEN [t].[Amount1] END) AS [Amount1March],
SUM(CASE WHEN [t].Monat = @Periode1 THEN [t].[Amount2] END) AS [Amount2Feb],
SUM(CASE WHEN [t].Monat = @Periode2 THEN [t].[Amount2] END) AS [Amount2March]
FROM
[dbo].[tblSales]
GROUP BY
[t].[Monat],
[t].[Project],
[t].[Description],
[t].[Finance]
;';
EXECUTE sp_executesql @Dynamictbl,
N'@Periode1 NVARCHAR(255), @Periode2 NVARCHAR(255)',
@Periode1 = @Periode1,
@Periode2 = @Periode2
;
END
GO
In this particular example, you're not gaining anything by using a dynamic query. You could replace the entire body of the stored procedure with the contents of your
@Dynamictbl
string.