使用参数获取存储过程记录 [英] Getting store procedure records with parameter

问题描述

我正在尝试从Sales表中记录两个基于月份的参数。它没有获得所选择的确切月份，而是激发了我所有的记录。我该怎么办呢？



当我在一个WHERE子句中包含Monat列时，我得到一个空结果



我的sp：

Hi,
I am trying to records from my Sales table with two parameters based on the month. Instead of the getting the exact months chosen, it fires me all the records. How can I go about it?

When I include the column Monat in a WHERE Clause, then I get an empty results

My sp:

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], --[t].[Project] AS [Entrance],

EXECUTE sp_executesql @Dynamictbl

END
GO
-- 
EXEC spMonthInfo @Periode1 = '02.2011', @Periode2 = '03.2011'

问题： [ ^ ]

推荐答案

存储过程为什么要这样做的完美示例让你免受 SQL注入 [ ^ ]漏洞。



从不使用字符串连接来构建SQL查询。 始终使用参数化查询。



在SQL中，使用 sp_executesql [ ^ ]使用参数执行动态查询。



您的动态查询中还有额外的尾随逗号，需要删除。

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

在这个特定示例中，您没有通过使用动态查询获得任何东西。您可以使用 @Dynamictbl 字符串的内容替换存储过程的整个主体。

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.

这篇关于使用参数获取存储过程记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！