November 27, 2014 at 4:26 am
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
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.
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
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
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.
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
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 27, 2014 at 5:13 am
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
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
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
Hi Neil,
Noted!
Thanks again,
Neil McK
November 27, 2014 at 5:53 am
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.
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
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
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
December 2, 2014 at 1:05 am
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
Jason-299789 (12/2/2014)
MS don't make it easy for people from BOLWhen 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)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 2, 2014 at 1:34 am
Phil Parkin (12/2/2014)
Jason-299789 (12/2/2014)
MS don't make it easy for people from BOLWhen 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
True, but that does not demonstrate the 30 character problem.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply