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
Solved

tsql syntax for DECLARE

Posted on 2013-11-06
6
319 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
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

829 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