Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Adding stored procedure to MSDE database

Posted on 2004-03-24
11
Medium Priority
?
1,023 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 200 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 1800 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

926 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