How can I check existence of a stored procedure and view and function in seed method of Entity Framework?

Question

I have an ASP.NET MVC web application and Entity Framework code first. I use a stored procedure and views and functions for my report section in my project. Then I want to after updating my database these stored procedure and views and functions dos not clean and drop after updating. I use this way and I want check if not exist this object then create it but when I run this code Nuget package manger said 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

I don't know what is wrong in my code any tips or trick would be welcome I haven't many time to waiting for this question please send me answer immediately thanks in advance

context.Database.ExecuteSqlCommand(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2')
  SET ANSI_NULLS ON
     SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[stp_Report1-2] 
    @startDate char(10),
    @endDate char(10),
    @ContractorTitle nvarchar(100)

    AS
BEGIN
    -- setting the smallest date to filter
    if(@startDate is null) SET @startDate='1300/01/01'
    --setting the end date the current date if it has not been set
    if(@endDate is null)SET @endDate=dbo.GetShamsiDate( GetDate())
    if(@ContractorTitle is null)SET @ContractorTitle=''
    SELECT *
    from Raja.dbo.[Report1-2]
       where [contractor_title]=@ContractorTitle and export_date between @startDate and @endDate
       Order by export_date desc


END

");

and when I use this code instead of up code, I get this error in Nuget

Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Code:

 context.Database.ExecuteSqlCommand(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2')
  SET ANSI_NULLS ON
GO
     SET QUOTED_IDENTIFIER ON
     GO
CREATE PROCEDURE [dbo].[stp_Report1-2] 
    @startDate char(10),
    @endDate char(10),
    @ContractorTitle nvarchar(100)

    AS
BEGIN
    -- setting the smallest date to filter
    if(@startDate is null) SET @startDate='1300/01/01'
    --setting the end date the current date if it has not been set
    if(@endDate is null)SET @endDate=dbo.GetShamsiDate( GetDate())
    if(@ContractorTitle is null)SET @ContractorTitle=''
    SELECT *
    from Raja.dbo.[Report1-2]
       where [contractor_title]=@ContractorTitle and export_date between @startDate and @endDate
       Order by export_date desc


END

");

and if I use this code these error

Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@startDate".
Must declare the scalar variable "@startDate".
Must declare the scalar variable "@endDate".
Must declare the scalar variable "@endDate".
Must declare the scalar variable "@ContractorTitle".
Must declare the scalar variable "@ContractorTitle".
Must declare the scalar variable "@ContractorTitle".

Code:

context.Database.ExecuteSqlCommand(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2')

CREATE PROCEDURE [dbo].[stp_Report1-2] 
    @startDate char(10),
    @endDate char(10),
    @ContractorTitle nvarchar(100)

    AS
BEGIN
    -- setting the smallest date to filter
    if(@startDate is null) SET @startDate='1300/01/01'
    --setting the end date the current date if it has not been set
    if(@endDate is null)SET @endDate=dbo.GetShamsiDate( GetDate())
    if(@ContractorTitle is null)SET @ContractorTitle=''
    SELECT *
    from Raja.dbo.[Report1-2]
       where [contractor_title]=@ContractorTitle and export_date between @startDate and @endDate
       Order by export_date desc
END
");

You can execute only **one** command at a time with *ExecuteSqlCommand*. — juergen d, Dec 07 '13 at 08:25
This may help: http://stackoverflow.com/questions/8809485/how-can-i-define-a-stored-procedure-in-entity-framework-code-first — Mike, Dec 07 '13 at 09:09
You have to send the two commands separately. First the check, and in your C# code decide whether the create command should be executed. — Gert Arnold, Dec 07 '13 at 10:49
thanks for your answer i separate existence code and creating store procedure but i don't know how can i return true or false when this store procedure exist then by if statement i check it ? — sara Sodagari, Dec 07 '13 at 11:31

score 2 · Answer 1 · answered Aug 19 '14 at 22:06

You can check that doing the following:

protected override void Seed(DbContext context)
{
   if (context.Database.SqlQuery<int>("SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND name = @uspName",
                                       new SqlParameter("@uspName", "your stored procedure name")).Single() == 0)
    {
        -- // On the example below I am using a resource file named StoredProcedures, which has the t-sql
        context.Database.ExecuteSqlCommand(DatabaseScripts.StoredProcedures.your stored procedure name);
    }
}

score 0 · Answer 2 · answered Dec 07 '13 at 19:20

0

First execute this statemment:

SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2'

It will Return 0 or 1.

Then in Your C# code check the Return value, if it is 0, run Your create stored procedure code.

answered Dec 07 '13 at 19:20

Shiraz Bhaiji

64,065
34
143
252

thanks for your answer i use this code for checking the existence of procedure – sara Sodagari Dec 08 '13 at 05:18
var ExistVW4 = context.Database.SqlQuery(@"SELECT CASE WHEN NOT exists (SELECT * FROM sys.views WHERE name ='Report1-4') THEN 0 ELSE 1 END "); if (ExistVW4.First() == 0) – sara Sodagari Dec 08 '13 at 05:19

score 0 · Answer 3 · edited May 23 '17 at 11:57

You can avoid having to return the result of the IF EXISTS call to C# by always doing a DROP and CREATE and you can avoid the

should be the first statement in a batch file

error by putting the sql inside an EXEC command:

Sql(EXEC('IF  EXISTS (SELECT * 
                      FROM sys.objects 
                      WHERE object_id = OBJECT_ID(N'dbo.Foos')
                      AND type in (N'P', N'PC'))
             DROP PROCEDURE dbo.Foos')

Sql(EXEC('Create PROCEDURE dbo.Foos As etc'))

The problem with the GO statement is that

it is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor

References:

Issue with Sql GO

MSDN

How can I check existence of a stored procedure and view and function in seed method of Entity Framework?

3 Answers3