• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1026
  • Last Modified:

Adding stored procedure to MSDE database

I have MSDE installed on my computer with Northwind sample database. I want to add stored procedure to it.
Suppose I have text of this procedure:

CREATE PROC SampleProc
(
   ...
)
AS
BEGIN    
    ...
END
GO

I know how to run SQL file using command line:
osql -Usa -Psa -Salexmxp\VSDOTNET -iStoreProc.sql

Please show me how can I write StoreProc.sql file to add SampleProc procedure to Northwind database in MSDE. I want to run this file number of times, I guess it should delete this procedure first, and then add it. Thanks.
0
AlexFM
Asked:
AlexFM
  • 5
  • 5
2 Solutions
 
adwisemanCommented:
Add to the beginning of your script

If object_id(N'YOURPROCEDURENAMEHERE') IS NOT NULL
    DROP PROCEDURE YOURPROCEDURENAMEHERE

GO

Your create procedure HERE...




Or you could just build the procedure once, and then change the create in your script to Alter, depending on what your needs are.
0
 
danblakeCommented:
You just need to specify in your osql command line the databse you are intending to use by adding the :
-d db_name

The easiest way to check prior to building a SP, it to perform the existance test -- if version already exists -- delete, and build new version else build new version.

/****** Object:  Stored Procedure dbo.SPName Script Date: 24/03/2004 13:58:43 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SPName]


0
 
AlexFMAuthor Commented:
This is update.sql file:

if exists (select * from dbo.sysobjects where id = object_id(N'[Northwind].[UpdateEmp]') and

OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Northwind].[UpdateEmp]


CREATE PROC UpdateEmp
(
        @ID           int,
        @NewFirstName Varchar(100),
        @NewLastName  Varchar(100),
        @OldFirstName Varchar(100),
        @OldLastName  Varchar(100),
        @DBFirstName  Varchar(100) OUTPUT,
        @DBLastName   Varchar(100) OUTPUT,
        @Status   int OUTPUT
)
AS
BEGIN    
     DECLARE @EmpFirstName varchar(100)
     DECLARE @EmpLastName varchar(100)

        SELECT @EmpFirstName = FirstName,
               @EmpLastName  = LastName
          FROM Employees
         WHERE EmployeeID = @ID

        Set @Status = 0;

        If @FirstName <> "" AND @LastName <> "" Then
          Set @DBFirstName = @OldFirstName
          Set @DBLastName  = @OldLastName
          If @EmpFirstName <> @OldFirstName or @EmpLastName <> @OldLastName Then
             Set @Status = 1;
           
             Update Employees
                Set FirstName = @NewFirstName, LastName = @NewLastName
              Where EmployeeID = @ID

              Set @DBFirstName = @NewFirstName
              Set @DBLastName  = @NewLastName
           Else
              Set @Status = 2;      
          End If      
     End If;
END
GO

I run it using command line:

osql -Usa -Psa -Salexmxp\VSDOTNET -iupdate.sql -dNorthwind

Result:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> Msg 111, Level 15, State 1, Server ALEXMXP\VSDOTNET, Line 5
'CREATE PROCEDURE' must be the first statement in a query batch.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 24
Must declare the variable '@ID'.
Msg 137, Level 15, State 1, Server ALEXMXP\VSDOTNET, Line 26
Must declare the variable '@Status'.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 28
Must declare the variable '@FirstName'.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 29
Must declare the variable '@OldFirstName'.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 30
Must declare the variable '@OldLastName'.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 31
Must declare the variable '@OldFirstName'.
Msg 137, Level 15, State 1, Server ALEXMXP\VSDOTNET, Line 32
Must declare the variable '@Status'.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 35
Must declare the variable '@NewFirstName'.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 38
Must declare the variable '@NewFirstName'.
Msg 137, Level 15, State 2, Server ALEXMXP\VSDOTNET, Line 39
Must declare the variable '@NewLastName'.
Msg 137, Level 15, State 1, Server ALEXMXP\VSDOTNET, Line 41
Must declare the variable '@Status'.
1>
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
adwisemanCommented:
Your missing the

GO

Between the if and drop procedure script and the create procedure script.  This executes each separatly.  It's not SQL syntex, but is recognized syntex by Query analyzer, and osql.
0
 
AlexFMAuthor Commented:
Thanks, it is working, but I have syntax errors in the procedure text. I added 100 points and ask to help again. For simplicity, I keep only CREATE PROCEDURE in the file now.

update.sql file:

CREATE PROC UpdateEmp
(
        @ID           int,
        @NewFirstName Varchar(100),
        @NewLastName  Varchar(100),
        @OldFirstName Varchar(100),
        @OldLastName  Varchar(100),
        @DBFirstName  Varchar(100) OUTPUT,
        @DBLastName   Varchar(100) OUTPUT,
        @Status   int OUTPUT
)
AS
BEGIN    
     DECLARE @EmpFirstName varchar(100)
     DECLARE @EmpLastName varchar(100)

        SELECT @EmpFirstName = Employees.FirstName,
               @EmpLastName  = Employees.LastName
          FROM Employees
         WHERE Employees.EmployeeID = @ID

        Set @Status = 0

        If @EmpFirstName <> "" AND @EmpLastName <> "" Then
          Set @DBFirstName = @OldFirstName
          Set @DBLastName  = @OldLastName
          If @EmpFirstName <> @OldFirstName or @EmpLastName <> @OldLastName Then
             Set @Status = 1
           
             Update Employees
                Set FirstName = @NewFirstName, LastName = @NewLastName
              Where EmployeeID = @ID

              Set @DBFirstName = @NewFirstName
              Set @DBLastName  = @NewLastName
           Else
              Set @Status = 2
          End If      
     End If
END
GO

Results:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> Msg 156, Level 15, State 1, Server ALEXMXP\VSDOTNET, Procedure UpdateEmp, Line 24
Incorrect syntax near the keyword 'Then'.
Msg 156, Level 15, State 1, Server ALEXMXP\VSDOTNET, Procedure UpdateEmp, Line 27
Incorrect syntax near the keyword 'Then'.
Msg 156, Level 15, State 1, Server ALEXMXP\VSDOTNET, Procedure UpdateEmp, Line 36
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Server ALEXMXP\VSDOTNET, Procedure UpdateEmp, Line 39
Incorrect syntax near the keyword 'End'.
1>

What is wrong?
0
 
adwisemanCommented:
In SQL, there are no THEN's in if statements
An iff statement only executes the line following the if, or else if or else.  So if more than one line is to be executed you must include Begin and end, to wrap more lines into the if.



0
 
adwisemanCommented:

CREATE PROC UpdateEmp
(
        @ID           int,
        @NewFirstName Varchar(100),
        @NewLastName  Varchar(100),
        @OldFirstName Varchar(100),
        @OldLastName  Varchar(100),
        @DBFirstName  Varchar(100) OUTPUT,
        @DBLastName   Varchar(100) OUTPUT,
        @Status   int OUTPUT
)
AS
BEGIN    
     DECLARE @EmpFirstName varchar(100)
     DECLARE @EmpLastName varchar(100)

        SELECT @EmpFirstName = Employees.FirstName,
               @EmpLastName  = Employees.LastName
          FROM Employees
         WHERE Employees.EmployeeID = @ID

        Set @Status = 0

        If @EmpFirstName <> "" AND @EmpLastName <> ""
        BEGIN
          Set @DBFirstName = @OldFirstName
          Set @DBLastName  = @OldLastName
          If @EmpFirstName <> @OldFirstName or @EmpLastName <> @OldLastName
          BEGIN
             Set @Status = 1
           
             Update Employees
                Set FirstName = @NewFirstName, LastName = @NewLastName
              Where EmployeeID = @ID

              Set @DBFirstName = @NewFirstName
              Set @DBLastName  = @NewLastName
           END
           Else
              Set @Status = 2
          End If
     END
     End If
END
GO
0
 
adwisemanCommented:
Note I did not put a Begin, End in the Else section.  It only has one command and does not need it.  BUT, as good coding practices, I would also include one here.
0
 
AlexFMAuthor Commented:
CREATE PROC UpdateEmp
(
        @ID           int,
        @NewFirstName Varchar(100),
        @NewLastName  Varchar(100),
        @OldFirstName Varchar(100),
        @OldLastName  Varchar(100),
        @DBFirstName  Varchar(100) OUTPUT,
        @DBLastName   Varchar(100) OUTPUT,
        @Status   int OUTPUT
)
AS
BEGIN    
    DECLARE @EmpFirstName varchar(100)
    DECLARE @EmpLastName varchar(100)

    SELECT @EmpFirstName = Employees.FirstName,
           @EmpLastName  = Employees.LastName
           FROM Employees
           WHERE Employees.EmployeeID = @ID

    Set @Status = 0

    If @EmpFirstName <> "" AND @EmpLastName <> ""
    BEGIN
        Set @DBFirstName = @OldFirstName
        Set @DBLastName  = @OldLastName
        If @EmpFirstName <> @OldFirstName or @EmpLastName <> @OldLastName
        BEGIN
            Set @Status = 1
           
            Update Employees
                Set FirstName = @NewFirstName, LastName = @NewLastName
                Where EmployeeID = @ID

            Set @DBFirstName = @NewFirstName
            Set @DBLastName  = @NewLastName
        END
        Else
            Set @Status = 2
        End If      
    END                                                                      <- line 42 (my comment, not from file)
    End If
END
GO

State 1, Server ALEXMXP\VSDOTNET, Procedure UpdateEmp, Line 42
Incorrect syntax near the keyword 'END'.

0
 
AlexFMAuthor Commented:
Thanks, now it works:

CREATE PROC UpdateEmp
(
        @ID           int,
        @NewFirstName Varchar(100),
        @NewLastName  Varchar(100),
        @OldFirstName Varchar(100),
        @OldLastName  Varchar(100),
        @DBFirstName  Varchar(100) OUTPUT,
        @DBLastName   Varchar(100) OUTPUT,
        @Status   int OUTPUT
)
AS
BEGIN    
    DECLARE @EmpFirstName varchar(100)
    DECLARE @EmpLastName varchar(100)

    SELECT @EmpFirstName = Employees.FirstName,
           @EmpLastName  = Employees.LastName
           FROM Employees
           WHERE Employees.EmployeeID = @ID

    Set @Status = 0

    If @EmpFirstName <> "" AND @EmpLastName <> ""
    BEGIN
        Set @DBFirstName = @OldFirstName
        Set @DBLastName  = @OldLastName
        If @EmpFirstName <> @OldFirstName or @EmpLastName <> @OldLastName
        BEGIN
            Set @Status = 1
           
            Update Employees
                Set FirstName = @NewFirstName, LastName = @NewLastName
                Where EmployeeID = @ID

            Set @DBFirstName = @NewFirstName
            Set @DBLastName  = @NewLastName
        END
        Else
            Set @Status = 2
    END
END
GO
0
 
AlexFMAuthor Commented:
This is my final result:

USE Northwind

drop procedure UpdateEmp
GO

CREATE PROC UpdateEmp
(
        @ID           int,
        @NewFirstName Varchar(100),
        @NewLastName  Varchar(100),
        @OldFirstName Varchar(100),
        @OldLastName  Varchar(100),
        @DBFirstName  Varchar(100) OUTPUT,
        @DBLastName   Varchar(100) OUTPUT,
        @Status   int OUTPUT
)
AS
BEGIN    
    DECLARE @EmpFirstName varchar(100)
    DECLARE @EmpLastName varchar(100)

    SELECT @EmpFirstName = Employees.FirstName,
           @EmpLastName  = Employees.LastName
           FROM Employees
           WHERE Employees.EmployeeID = @ID

    Set @Status = 0

    If @EmpFirstName <> "" AND @EmpLastName <> ""
    BEGIN
        Set @DBFirstName = @OldFirstName
        Set @DBLastName  = @OldLastName
        If @EmpFirstName <> @OldFirstName or @EmpLastName <> @OldLastName
        BEGIN
            Set @Status = 1
           
            Update Employees
                Set FirstName = @NewFirstName, LastName = @NewLastName
                Where EmployeeID = @ID

            Set @DBFirstName = @NewFirstName
            Set @DBLastName  = @NewLastName
        END
        Else
            Set @Status = 2
    END
END
GO
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now