Monday, March 12, 2012

Problem with Saving a Stored Procedure

I have had the same problem. In the past I had the sql commands in the code. Last project was with SQL2000 and I decided to add store procedures part as a learning tool and part to better control the code. With the current project I am using SQL2005 and have a ummm fun time with the learning curve.

Below is my stored procedure.

USE [AdventureWorks]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[WDR_CountryAdd]

(

@.CountryName nvarchar(50),

@.CountryID int OUTPUT

)

AS

INSERT INTO Country

(

CountryName

)

VALUES

(

@.CountryName

)

SELECT

@.CountryID = @.@.Identity

When I hit the execute button, I get the following error.

Msg 208, Level 16, State 6, Procedure WDR_CountryAdd, Line 24

Invalid object name 'WDR_CountryAdd'.

I I know that it does not exist as that is what I am trying to do, create it. I have checked to make sure that I am on the currect database. So how do I associate this procedure with the data base? Do I need to write a SQL query to create the procedure? Maybe that is the answer and then just modify it.

If it doesn't exists, you need to use a CREATE PROCEDURE statement instead of an ALTER PROCEDURE statement.|||

Arnie

I appreciate the fast response.

I used a create and it ran successfully. However, it still did not show up under the stored procedures. So if it does not show up in the list, how do I access it from my web application? With SQL 2000 I just did a right click create new, did the code and it saved it to the list and then I accessed it. I was planning on a code along the lines of WDR_CountryAdd("USA") (of course in the proper codes)

Now here is a beginners question, When I first execute this procedure, do I need to supply the parameters for it to successfully attach to the database?

Jerry

|||

Never Mind. I ran the CREATE again and it worked very well. Must of had a brain dead moment there.

Thanks for the help. I have learned a lot from this forum.

Jerry

No comments:

Post a Comment