December 22, 2009 at 1:49 am
I have the given SP which contains variables, but it never returns any rows
The variables are necessary because they are used at different places.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Maturity_Report_And_Detail_GetByReportId]
@ReportId int
AS
--exec Maturity_DetailGetByMaturitiesReports @ReportId;
DECLARE @Maturity_DetaillId int
DECLARE @ReportId1 int
DECLARE @RowId varchar(50)
DECLARE @UpToOneMonths varchar(50)
DECLARE @OneToThreeMonths varchar(50)
DECLARE @ThreeToSixMonths varchar(50)
DECLARE @SixToTwelveMonths varchar(50)
DECLARE @OneToTwoYears varchar(50)
DECLARE @TwoToThreeYears varchar(50)
DECLARE @ThreeToFiveYears varchar(50)
DECLARE @FiveToTenYears varchar(50)
DECLARE @MoreThanTenYears varchar(50)
DECLARE @Total varchar(50)
DECLARE @Total2 varchar(50)
DECLARE @PerOfTotalAssets varchar(50)
DECLARE @delta varchar(50)
DECLARE @Difference varchar(50)
SELECT
@Maturity_DetaillId=[Maturity_DetaillId],
@ReportId1=[ReportId],
@RowId=[RowId],
@UpToOneMonths=[UpToOneMonths],
@OneToThreeMonths=[OneToThreeMonths],
@ThreeToSixMonths=[ThreeToSixMonths],
@SixToTwelveMonths=[SixToTwelveMonths],
@OneToTwoYears=[OneToTwoYears],
@TwoToThreeYears=[TwoToThreeYears],
@ThreeToFiveYears=[ThreeToFiveYears],
@FiveToTenYears=[FiveToTenYears],
@MoreThanTenYears=[MoreThanTenYears],
@Total=[Total],
@PerOfTotalAssets=[PerOfTotalAssets],
@delta=[Delta],
@Total2=(SELECT [Total] FROM Maturity_Detail WHERE ([RowId]=@RowId) AND ([ReportId]=(SELECT Top 1 ReportId FROM MaturitiesReports WHERE [Report_Month] < (SELECT [Report_Month] FROM MaturitiesReports WHERE [ReportId]=@ReportId) ORDER BY [Report_Month] DESC))),
@Difference = CASE WHEN ISNUMERIC(@Total) = 1 THEN
CASE WHEN ISNUMERIC(@Total2) = 1 THEN
CASE WHEN Convert(float,@Total) - Convert(float,@Total2)>0 Then
1-- >0
ELSE
CASE WHEN Convert(float,@Total) - Convert(float,@Total2)<0 Then
2-- <0
ELSE
0-- ==0
END
END
ELSE
-1 -- for not converted
END
ELSE
-1 -- for not converted
END
FROM Maturity_Detail
WHERE
[ReportId]=@ReportId
ORDER BY [RowId]
December 22, 2009 at 1:59 am
You won't get any data because you are not selecting any. In your code you are only assigning values to the variables. To get the output you have to select the variables after assigning values to them.
Is your query only returning one row? Are you sure about it?
-Vikas Bindra
December 22, 2009 at 2:22 am
no it returns many
December 22, 2009 at 3:56 am
What exactly are you trying to do?
The way this proc is written it will fail as soon as you have more than one row having [ReportId]=@ReportId.
Even though you added ORDER BY [RowId], it won't make a difference, unless you'd add a TOP 1 clause at the begining of your select to ensure to get only one row (if that's what you're looking for...).
Please provide more details.
December 22, 2009 at 4:11 am
As Imu said, send us what are you trying to do?
-Vikas Bindra
December 22, 2009 at 4:28 am
Neither ReportId1 is Pk nor RowId. They are both FK's
This SP was working 100% before when i was not using local variables.
The reason for using variable is that @Total is used in finding @Total2 and @Total2 is used to find @Difference
Just see this,
@RowId=[RowId],
@Total=[Total],
@Total2=(SELECT [Total] FROM Maturity_Detail WHERE ([RowId]=@RowId) AND ([ReportId]=(SELECT Top 1 ReportId FROM MaturitiesReports WHERE [Report_Month] < (SELECT [Report_Month] FROM MaturitiesReports WHERE [ReportId]=@ReportId) ORDER BY [Report_Month] DESC))),
@Difference = CASE WHEN ISNUMERIC(@Total) = 1 THEN
CASE WHEN ISNUMERIC(@Total2) = 1 THEN
CASE WHEN Convert(float,@Total) - Convert(float,@Total2)>0 Then
1-- >0
ELSE
CASE WHEN Convert(float,@Total) - Convert(float,@Total2)<0 Then
2-- <0
ELSE
0-- ==0
END
END
ELSE
-1 -- for not converted
END
ELSE
-1 -- for not converted
END
FROM Maturity_Detail
December 22, 2009 at 4:29 am
abcim (12/22/2009)
I have the given SP which contains variables, but it never returns any rowsThe variables are necessary because they are used at different places.
@Difference will always return NULL
@Total2 will always be NULL
If I were you I'd start again with this query, returning a resultset which excludes these two values, and then figure out how they should be calculated. SQL Server variables don't behave in the way you are inticipating. The values of the variables are not assigned = don't exist until the query is run and finished, but some of your calculations are expecting values while the query is running. The subquery which is supposed to assign a value to @Total2 is nonsense. Work with the query in SSMS or QA or whatever until you are getting the results you expect before comitting it to a stored procedure, it's quicker to design this way.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 4:45 am
You could use CTEs (common table expressions) to determine @Total2 (see BOL for details).
This would make the whole query much more readable and most probably would make it more obvious how Chris "figured" that those two variables will always be null. (btw: good catch Chris! 😉 ).
December 22, 2009 at 4:47 am
Yes. A real good catch:-)
-Vikas Bindra
December 22, 2009 at 4:52 am
lmu92 (12/22/2009)
You could use CTEs (common table expressions) to determine @Total2 (see BOL for details).This would make the whole query much more readable and most probably would make it more obvious how Chris "figured" that those two variables will always be null. (btw: good catch Chris! 😉 ).
Thanks Lutz, too kind 🙂
A little formatting can go a long way:
@Total2=(SELECT [Total]
FROM Maturity_Detail
WHERE ([RowId] = @RowId) AND ([ReportId] =
(SELECT Top 1 ReportId
FROM MaturitiesReports
WHERE [Report_Month] <
(SELECT [Report_Month]
FROM MaturitiesReports
WHERE [ReportId] = @ReportId)
ORDER BY [Report_Month] DESC))),
@Difference = CASE
WHEN ISNUMERIC(@Total) = 1 THEN
CASE
WHEN ISNUMERIC(@Total2) = 1 THEN
CASE
WHEN Convert(float,@Total) - Convert(float,@Total2)>0 Then 1-- >0
ELSE
CASE
WHEN Convert(float,@Total) - Convert(float,@Total2)<0 Then 2-- <0
ELSE 0-- ==0
END
END
ELSE -1 -- for not converted
END
ELSE -1 -- for not converted
END
FROM Maturity_Detail
Your suggestion of using a CTE (or two) for this is a good'un.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 5:13 am
What is CTEs (common table expressions) ?
December 22, 2009 at 5:22 am
abcim (12/22/2009)
Are you sure (Good Catch)then why this works 100%,
DECLARE @a int
DECLARE @b-2 int
SELECT @a=1,@b=ISNUMERIC(@a)
SELECT @a,@b
You really don't want to use the "quirky update" trick for this
@ variable
Is a declared variable that is set to the value returned by expression.
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column
Neither do you want to use the syntax for assigning a value to a variable For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.
You want to write a simple query, then when it's working, create a stored procedure from it.
How do you want the values returned to whatever is calling the stored procedure? Normally you would use return parameters or a result set. How many rows do you expect to return?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2009 at 5:35 am
Well, I doesn't look like the results for those two variables will always be NULL...
Reason: the query most probably won't run at all.
I just tested the part with the subselect
SELECT Top 1 ReportId
FROM MaturitiesReports
WHERE [Report_Month] <
(SELECT [Report_Month]
FROM MaturitiesReports
WHERE [ReportId] = @ReportId)
ORDER BY [Report_Month] DESC)
and got the following error (after changing it to table and column name that exist in my DB):
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
@abcim: Are you sure this proc is running at all on your system and not firing any errors?
December 22, 2009 at 6:21 am
>> lmu92 >>@abcim: Are you sure this proc is running at all on your system and not firing any errors?
Yes this is a Sp, means that this is compiled but it returns 0 Rows.
>> Chris Morris-439714 >>For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.
I think this is very nice but the problem is that there is not a single Row. So @local_variable must be used to SELECT Query to return multiple Rows (near to 50-52)
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply