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.
LVL 48
AlexFMAsked:
Who is Participating?
 
adwisemanConnect With a Mentor 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
     End If
END
GO
0
 
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
 
danblakeConnect With a Mentor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.