Solved

Adding stored procedure to MSDE database

Posted on 2004-03-24
11
1,015 Views
Last Modified: 2010-08-05
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
Comment
Question by:AlexFM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 10667645
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
 
LVL 13

Assisted Solution

by:danblake
danblake earned 50 total points
ID: 10667681
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
 
LVL 48

Author Comment

by:AlexFM
ID: 10667791
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 14

Expert Comment

by:adwiseman
ID: 10667845
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
 
LVL 48

Author Comment

by:AlexFM
ID: 10668197
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
 
LVL 14

Expert Comment

by:adwiseman
ID: 10668235
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
 
LVL 14

Accepted Solution

by:
adwiseman earned 450 total points
ID: 10668242

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
 
LVL 14

Expert Comment

by:adwiseman
ID: 10668253
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
 
LVL 48

Author Comment

by:AlexFM
ID: 10668369
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
 
LVL 48

Author Comment

by:AlexFM
ID: 10668570
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
 
LVL 48

Author Comment

by:AlexFM
ID: 10668624
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question