Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

What am I Doing Wrong with this Stored Procedure?

I'm trying to write a stored procedure. I've got the sql that works, having run the syntax as a basic query, but when I go to save it as a procedure, I get an error.

Here's my attempt:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Bruce Gust>
-- Create date: <February 6, 2014,>
-- Description:      <basic cer_model query>
-- =============================================
CREATE PROCEDURE <cer_modellist, BIProd, cer_modellist>
      -- Add the parameters for the stored procedure here
      @UserID int
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      select cer.assettypeid, cer.statusid, cer.createuserid, cer.entityid, cer.createdate, cer.description
    from cer_model cer
      where cer.createuserid = @UserID
      order by cer.createuserid
            
END
GO

...and here's the error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '<'.
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@UserID".


What am I doing wrong?
0
brucegust
Asked:
brucegust
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
CREATE PROCEDURE <cer_modellist, BIProd, cer_modellist> 
      -- Add the parameters for the stored procedure here
      @UserID int
AS

Open in new window

Which of the above is the name of the stored procedure?

CREATE PROC your_sp_name (@UserID int) AS
-- =============================================
-- Author:            <Bruce Gust>
-- Create date: <February 6, 2014,>
-- Description:      <basic cer_model query>
-- =============================================

Open in new window

Also, you'll want the above comments AFTER the CREATE PROC line, so that it saves in the proc.
0
 
Scott PletcherSenior DBACommented:
The general CREATE PROCEDURE syntax is very straight forward.  Here it is for your proc without all the fluff from the template:


CREATE PROCEDURE dbo.BIProd
    @UserID int
AS
SET NOCOUNT ON;

select cer.assettypeid, cer.statusid, cer.createuserid, cer.entityid, cer.createdate, cer.description
from cer_model cer
where cer.createuserid = @UserID
order by cer.createuserid

GO


And, more generally:

CREATE PROCEDURE schema.proc_name
    @param_name1 <datatype> [= default_value]
    [, @param_name2 <datatype> [= default_value]
    [,...]
AS
SET NOCOUNT ON;
<procedure code goes here>
GO --optional, but good to confirm the end of proc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now