?
Solved

What am I Doing Wrong with this Stored Procedure?

Posted on 2014-02-06
2
Medium Priority
?
213 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
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 70

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

615 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