Solved

What am I Doing Wrong with this Stored Procedure?

Posted on 2014-02-06
2
208 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 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 350 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

Industry Leaders: 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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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