Solved

tsql syntax for DECLARE

Posted on 2013-11-06
6
315 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 65

Assisted Solution

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

Accepted Solution

by:
pateljitu earned 150 total points
Comment Utility
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
Comment Utility
Thank you.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 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…

762 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

7 Experts available now in Live!

Get 1:1 Help Now