Post reply

SQL Date Parameters not working

  • November 27, 2014 at 4:26 am

    #397701

    Hi gang,

    I'm having a strange issue with parameters (I think!) in that they are not being passed through to my queries.

    I am trying to produce a result set that pulls in my customer and his average holding for a period.

    The calculation is in a subquery and both outer and inner queries are being passed the parameters etc. Sadly this is not working, however if I replace the parameters with values the query executes properly.

    Regrettably I can't work out why. Would anyone be able to take a look at the code and offer suggestions as to why it's not working correctly?

    DECLARE @client as varchar

    DECLARE @StartDate as date

    DECLARE @EndDate as date

    DECLARE @Fund as varchar

    SET @client = 'C10000'

    SET @StartDate = '2014-04-01'

    SET @EndDate = '2014-06-30'

    SET @Fund = 'Fund1'

    Select

    [Intermediary Code]

    ,[Client No_]

    ,[Fund Code]

    ,AverageHolding=SUM([Total Clients Value Holding])/(

    Select

    COUNT([Fund Code])

    from

    [Table]

    Where

    [Client No_] = @client

    AND

    [Date] BETWEEN @StartDate AND @EndDate

    AND

    [Fund Code] = @Fund

    )

    from

    [Table]

    Where

    [Client No_] = @client

    AND

    [Date] BETWEEN @StartDate AND @EndDate

    AND

    [Fund Code] = @Fund

    Group By

    [Intermediary Code]

    ,[Client No_]

    ,[Fund Code]

    Any help you can offer will be gratefully received.

    Thank you kindly,

    Neil

  • November 27, 2014 at 4:53 am

    #1761266

    Can you post some sample data please?

    What do you mean by 'not working'? I've taken a guess at what your data looks like and I'm getting a 'divide by zero' error. Is that the problem you've got or something else?

    Neil B.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 27, 2014 at 4:58 am

    #1761268

    Hi Neil,

    Thanks for your prompt response! Much appreciated.

    Yes the row count should be 60 and it is when I put in the parameter values. However it returns 0 when I try and pass the values via parameter.

    It's essential that I get the parameters working as I will be using that query as an SP called by another that uses a cte or cursor to iterate through a table updating the parameter values for all customers/clients

    Will try and get some data uploaded too.

    Once again thanks for the reply 🙂

    Neil Mck

  • November 27, 2014 at 5:01 am

    #1761270

    I've had a second look and you need to declare the length of your @client and @fund variables. If you declare them like so

    DECLARE @client as varchar(n)

    DECLARE @StartDate as date

    DECLARE @EndDate as date

    DECLARE @Fund as varchar(n)

    Where n is the maximum length of each varchar parameter it should work.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 27, 2014 at 5:08 am

    #1761275

    You're not the first, and won't be the last, to get caught by this.

    MS should make all declarations of Varchar without size a syntax error, in my opinion.

  • November 27, 2014 at 5:13 am

    #1761276

    Eureka!

    That worked perfectly. Thank you both very much for the help!

    I will endeavour to remember that next time!

    Kindest regards,

    Neil McK

  • November 27, 2014 at 5:32 am

    #1761286

    You're welcome.

    One other thing, be careful using BETWEEN for date range queries. You're usually better using

    where

    [Date] >= @startdate

    and

    [Date] <= @enddate

    Have a look at this article which explains better than I can.

    Neil

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 27, 2014 at 5:34 am

    #1761288

    Hi Neil,

    Noted!

    Thanks again,

    Neil McK

  • November 27, 2014 at 5:53 am

    #1761293

    In true Columbo fashion, just one more thing...

    You could use

    AverageHolding= avg([Total Clients Value Holding])

    rather than

    AverageHolding=sum([Total Clients Value Holding])

    /(

    Select

    COUNT([Fund Code])

    from

    [#Table]

    Where 1=1

    and [Client No_] = @client

    AND

    [Date] >= @startdate

    and

    [Date] <= @enddate

    AND

    [Fund Code] = @Fund

    )

    It does the same thing just more neatly.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 27, 2014 at 6:01 am

    #1761295

    That's brilliant! Does the same thing and saves me doing silly row count stuff!

    I can't thank you enough!

    Very much appreciated!

    Neil

  • December 1, 2014 at 2:22 pm

    #1761870

    Unfortunately, this is legal ANSI/ISO Standard SQL so it has to work. But you ought to get a warning!

    Rather than defaulting to "1," maybe they could make it work like a string in a procedural language: expands to meet the size of the input. That should make someone at MS's head explode.

    Don Simpson


    I'm not sure about Heisenberg.

  • December 2, 2014 at 1:05 am

    #1761958

    MS don't make it easy for people from BOL

    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

    So you have a conflict as developers will use the syntax CONVERT(VARCHAR,@myInt) and it will work fine for upto 30 chars.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • December 2, 2014 at 1:14 am

    #1761960

    Jason-299789 (12/2/2014)

    MS don't make it easy for people from BOL

    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

    So you have a conflict as developers will use the syntax CONVERT(VARCHAR,@myInt) and it will work fine for upto 30 chars.

    As the maximum value for an Int is 2,147,483,647, your example is flawed 🙂

    declare @myInt int = 2147483647

    select convert(varchar, @myInt)

  • December 2, 2014 at 1:34 am

    #1761963

    Phil Parkin (12/2/2014)

    Jason-299789 (12/2/2014)

    MS don't make it easy for people from BOL

    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

    So you have a conflict as developers will use the syntax CONVERT(VARCHAR,@myInt) and it will work fine for upto 30 chars.

    As the maximum value for an Int is 2,147,483,647, your example is flawed 🙂

    declare @myInt int = 2147483647

    select convert(varchar, @myInt)

    I thought it demonstrated it very well, as when you do

    declare @myInt int = 2147483647

    declare @myVarchar varchar

    select convert(varchar, @myInt)

    set @myVarchar=convert(varchar, @myInt)

    Select @myvarchar

    One give you the correct output the other doesn't.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • December 2, 2014 at 1:44 am

    #1761964

    True, but that does not demonstrate the 30 character problem.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy