How do I create a new stored procedure?

23 Mar 2006 3:46 AM

needin4mation

Hi, I am using SQL Server 2005 Express trying to learn to write a
stored procedure. In my database, I right-clicked on progammability ->
stored procedures -> new stored procedure. I put in my procedure, hit
execute, but it just keeps saying:

Msg 208, Level 16, State 6, Procedure GetProductsByCategoryId, Line 21
Invalid object name 'dbo.GetProductsByCategoryId'.

I have put the below code in, but I have also put in code from another
SP (which I did not write in this DB, and I know works), and it gives
the same message. I do not understand what it wants. It is a
permission thing? I can change other SPs, hit execute, and it saves
the modifications. I just can't add a brand new one.

Thank you for any help.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetProductsByCategoryId]
    @PageIndex INT,
    @NumRows INT
AS

BEGIN
    /*
       The below statements use the new ROW_NUMBER() function in SQL 2005
to return only the

specified
       rows we want to retrieve from the Products table
    */

    Declare @startRowIndex INT;
    set @startRowIndex = (@PageIndex * @NumRows) + 1;

    With ProductEntries as (
        SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row,
ProductID,
    Name,
    branddescription,
    buyurl,
    salepricelist,
    smallimagelist
        FROM Shoes_inv
    )

    Select
    ProductID,
    Name,
    branddescription,
    buyurl,
    salepricelist,
    smallimagelist
    FROM ProductEntries
    WHERE Row between
    @startRowIndex and @StartRowIndex+@NumRows-1
END

23 Mar 2006 6:18 AM

Uri Dimant

Hi
Change ALTER Procedure to CREATE Procedure

<needin4mat***@gmail.com> wrote in message
Show quote

news:1143085570.303302.59670@e56g2000cwe.googlegroups.com...
> Hi, I am using SQL Server 2005 Express trying to learn to write a
> stored procedure. In my database, I right-clicked on progammability ->
> stored procedures -> new stored procedure. I put in my procedure, hit
> execute, but it just keeps saying:
>
> Msg 208, Level 16, State 6, Procedure GetProductsByCategoryId, Line 21
> Invalid object name 'dbo.GetProductsByCategoryId'.
>
> I have put the below code in, but I have also put in code from another
> SP (which I did not write in this DB, and I know works), and it gives
> the same message. I do not understand what it wants. It is a
> permission thing? I can change other SPs, hit execute, and it saves
> the modifications. I just can't add a brand new one.
>
> Thank you for any help.
>
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE [dbo].[GetProductsByCategoryId]
> @PageIndex INT,
> @NumRows INT
> AS
>
> BEGIN
> /*
>    The below statements use the new ROW_NUMBER() function in SQL 2005
> to return only the
>
> specified
>    rows we want to retrieve from the Products table
>    */
>
> Declare @startRowIndex INT;
> set @startRowIndex = (@PageIndex * @NumRows) + 1;
>
> With ProductEntries as (
> SELECT ROW_NUMBER() OVER (ORDER BY ProductId ASC) as Row,
> ProductID,
>    Name,
> branddescription,
> buyurl,
> salepricelist,
> smallimagelist
> FROM Shoes_inv
> )
>
> Select
> ProductID,
>    Name,
> branddescription,
> buyurl,
> salepricelist,
> smallimagelist
> FROM ProductEntries
> WHERE Row between
> @startRowIndex and @StartRowIndex+@NumRows-1
> END
>