?
Solved

tsql syntax for DECLARE

Posted on 2013-11-06
6
Medium Priority
?
327 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 600 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 600 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 66

Assisted Solution

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 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