Solved

tsql syntax for DECLARE

Posted on 2013-11-06
6
321 Views
Last Modified: 2013-11-06
What is the proper syntax?

Thanks,

USE [stockprosql]
GO
/****** Object:  StoredProcedure [dbo].[usp_getExceptions]    Script Date: 11/06/2013 10:23:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
ALTER PROCEDURE [dbo].[usp_getExceptions]
      -- Add the parameters for the stored procedure here
      -- Current sample is the most recent 5 days
      -- Previous sample is the 8 day range immediately prior to the current sample
--      @MarketID smallint,      @CurrFromDate DateTime, -- Oldest current 5 day quote date
      @CurrToDate DateTime,  -- Most recent current 5 day quote date
      @PrevFromDate DateTime, -- Oldest previous group quote date
      @PrevToDate DateTime,  -- Most recent previous group quote date
      @Min5DayAvg Decimal(5,2),
      @Max5DayAvg Decimal(5,2),
      @Curr5Prev8Min Decimal(5,2),
      @Curr5Prev8Max Decimal(5,2),
      @ResearchSiteURL varchar(100),
      @FromDate DateTime,
      @SiteEvalType varchar(100),      
      @ToDate DateTime  ,
      @AsOfDate DateTime,
      @DaysBack SmallInt

------

exec usp_getExceptions
--@Increases = 15,
@CurrFromDate = 2013-10-08 ,
@CurrToDate = 2013-10-14,
@PrevFromDate = 2013-09-26,
@PrevToDate = 2013-10-07,
@ResearchSiteURL = 'http://investing.money.msn.com/investments/stock-price?Symbol=msft'

-- Error @CurrFromDate is not a parameter for procedure usp_getExceptions
0
Comment
Question by:Dovberman
[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
  • 2
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 150 total points
ID: 39627921
Looks like you have commented out the line for parameter "@CurrFromDate DateTime", please have @CurrFromDate in another line.

--      @MarketID smallint,      @CurrFromDate DateTime, -- Oldest current 5 day quote date
change to
--      @MarketID smallint,      
@CurrFromDate DateTime, -- Oldest current 5 day quote date
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 39627965
Proper syntax ... for what?    Mind readers we ain't.

Looking at the exec line at the bottom of the question, is it your intent to allow some parameters to not be called explicitly in the calling exec statement?  If so, the CREATE/ALLTER PROC has to define those parameters with a default of NULL.  

For example..

      @Min5DayAvg Decimal(5,2) DEFAULT NULL,
      @Max5DayAvg Decimal(5,2) DEFAULT NULL,
0
 

Author Comment

by:Dovberman
ID: 39627967
Thanks.  I just got another error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '-'.

What have I missed?

exec usp_getExceptions
@Increases = 15,
@CurrFromDate = 2013-10-08 ,
@CurrToDate = 2013-10-14,
@PrevFromDate = 2013-09-26
@PrevToDate = 2013-10-07
@ResearchSiteURL = 'http://investing.money.msn.com/investments/stock-price?Symbol=msft'
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 39627983
^^^ The three parameters in the above exec are missing a comma separating them.
0
 
LVL 15

Accepted Solution

by:
pateljitu earned 150 total points
ID: 39627992
change exec statement to, missing commas and added single quotes around dates parameter
exec usp_getExceptions
@Increases = 15,
@CurrFromDate = '2013-10-08',
@CurrToDate = '2013-10-14',
@PrevFromDate = '2013-09-26',
@PrevToDate = '2013-10-07',
@ResearchSiteURL = 'http://investing.money.msn.com/investments/stock-price?Symbol=msft'

Open in new window

0
 

Author Closing Comment

by:Dovberman
ID: 39628038
Thank you.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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