Solved

What am I Doing Wrong with this Stored Procedure?

Posted on 2014-02-06
2
205 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:
ScottPletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now