?
Solved

What am I Doing Wrong with this Stored Procedure?

Posted on 2014-02-06
2
Medium Priority
?
211 Views
Last Modified: 2014-02-10
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
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 600 total points
ID: 39840417
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1400 total points
ID: 39840527
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

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

765 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