C# 和 SQL 服务器 : execute stored procedure

Question

我在存储过程中有一个 SQL 查询，但出现错误。

这里是查询:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fPartnersDebt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fPartnersDebt]

/****** Object:  UserDefinedFunction [dbo].[fPartnersDebt]    Script Date: 03/07/2012 16:02:01 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

exec('CREATE PROCEDURE [dbo].[fPartnersDebt]')
(   
    @StartDate datetime
    ,@EndDate datetime
    ,@CompanyName nvarchar(100)
    ,@Account nvarchar(100)
    ,@Reference nvarchar(max)
    ,@ShowClosedDocuments bit
)
RETURNS TABLE 
AS
    RETURN 
        (SELECT 
             t1.Name, t1.Bulstat, t1.Amount, t1.IsVat
         FROM 
             (SELECT 
                  c.CompanyID, c.Name AS [Name], c.IsVat, c.Bulstat AS [Bulstat],
                  SUM((ad.Amount + ad.RefAmount) * ad.[Sign]) AS [Amount]
              FROM 
                  Companies c
              JOIN 
                  AccountingDetails ad 
              JOIN 
                  Accounts a 
              JOIN 
                  AccountCategories ac ON ac.AccountCategoryID = a.AccountCategoryID
                  ON ad.AccountID = a.AccountID
              JOIN 
                  Accountings acc ON ad.AccountingID = acc.AccountingID
                  ON ad.CompanyID = c.CompanyID
              LEFT JOIN 
                  [References] r ON acc.OptionalReferenceID = r.ReferenceID
              WHERE 
                  ac.TypeIdentifier IN (4, 6, 7, 8, 9, 20, 33, 54, 10, 12, 13, 14, 15, 19, 34, 55) 
                  AND Name LIKE @CompanyName 
                  AND (a.Number = @Account OR a.Number LIKE @Account + '/%' OR (Len(@Account) < 3 AND a.Number LIKE @Account + '%'))
                  AND acc.AccountingDate >= @StartDate 
                  AND acc.AccountingDate <= @EndDate
                  AND (ISNULL(r.[Description], '') LIKE @Reference + '%')
              GROUP BY 
                  c.CompanyID, c.Name, c.Bulstat, c.IsVat) t1
    WHERE 
        @ShowClosedDocuments = 1 OR t1.Amount <> 0
)

错误:

Incorrect syntax near '@StartDate'.

Must declare the scalar variable "@CompanyName".

我尝试用多种方式声明，但没有一个不起作用。抱歉，我是新手，解决这个问题对我来说有点困难。

Answer 1

使用 GO 分离批处理，删除动态 SQL 并将 CREATE PROCEDURE 替换为 CREATE FUNCTION:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fPartnersDebt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fPartnersDebt]
GO     -- here


CREATE FUNCTION [dbo].[fPartnersDebt]  -- here
(   
    @StartDate datetime
    ,@EndDate datetime
    ,@CompanyName nvarchar(100)
    ,@Account nvarchar(100)
    ,@Reference nvarchar(max)
    ,@ShowClosedDocuments bit
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT 
        t1.Name
        ,t1.Bulstat
        ,t1.Amount   
        ,t1.IsVat
    FROM 
        (SELECT 
            c.CompanyID
            ,c.Name AS [Name]
            ,c.IsVat
            ,c.Bulstat AS [Bulstat]
            ,SUM((ad.Amount + ad.RefAmount) * ad.[Sign]) AS [Amount]
        FROM Companies c
            JOIN AccountingDetails ad 
                JOIN Accounts a 
                    JOIN AccountCategories ac ON ac.AccountCategoryID = a.AccountCategoryID
                ON ad.AccountID = a.AccountID
                JOIN Accountings acc ON ad.AccountingID = acc.AccountingID
            ON ad.CompanyID = c.CompanyID
            LEFT JOIN [References] r ON acc.OptionalReferenceID = r.ReferenceID
        WHERE ac.TypeIdentifier IN (4,6,7,8,9,20,33,54,10,12,13,14,15,19,34,55) 
              AND Name LIKE @CompanyName 
              AND (a.Number = @Account OR a.Number LIKE @Account + '/%' OR (Len(@Account)<3 AND a.Number LIKE @Account + '%'))
              AND acc.AccountingDate >= @StartDate AND acc.AccountingDate <= @EndDate
              AND (ISNULL(r.[Description], '') LIKE @Reference + '%')
        GROUP BY 
            c.CompanyID
            ,c.Name
            ,c.Bulstat
            ,c.IsVat)t1
    WHERE @ShowClosedDocuments = 1 OR t1.Amount <> 0
)

编辑:

GO 是在 SSMS 中工作的批处理分隔符。如果您想从 C# 执行它，请进行两个单独的调用。首先 DROP FUNCTION 然后重新创建它。