Solved

Adding stored procedure to MSDE database

Posted on 2004-03-24
11
1,011 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
  • 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now