C# & SQL Server : execute stored procedure

Question

I have a SQL query inside a stored procedure and I get an error.

Here is the query:

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
)

Errors:

Incorrect syntax near '@StartDate'.

Must declare the scalar variable "@CompanyName".

I try to declare with many ways, but nothing does not work. Sorry but I'm new and it is a little hard to me to resolve this problem.

Lukasz Szozda · Accepted Answer · 2017-08-10 11:58:20Z

6

Use GO to separate batches, remove dynamic SQL and replace CREATE PROCEDURE with 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
)

EDIT:

GO is batch separator which works inside SSMS. If you want to execute it from C# make two separate calls. First DROP FUNCTION and then recreate it.

edited Aug 10, 2017 at 11:58

answered Aug 10, 2017 at 11:53

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Димитър Грудев Over a year ago

Errors: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. 'CREATE FUNCTION' must be the first statement in a query batch. Must declare the scalar variable "@CompanyName".

Lukasz Szozda Over a year ago

@ДимитърГрудев So you are executing it from C# if yes then call it with two calls instead of one.

Димитър Грудев Over a year ago

Yes i execute with c#. Sorry but I'm new. Can you show me with code?

Lukasz Szozda Over a year ago

@ДимитърГрудев Use the same C# code that you use. Simply first call will be

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]

and the second with CREATE ...

Collectives™ on Stack Overflow

C# & SQL Server : execute stored procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related