stored procedure returns Null value

Sivakumar

11y1.2k

Hi,

This is My storedprocedure :

GO
ALTER PROCEDURE [dbo].[uspGetVote](@TeamID INT,@CardID INT)

AS

BEGIN

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=@TeamID and CardID=@CardID and LastModifiedDateTime between getDate()-7 and getDate()
end

When execute this procedure returns Null.

This is my table values :

When i pass TeamID and CardID 1,1 its shows a Null.

Please give a correct query for that.

Thank You.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sujeet Suman11y ago
Shivakumar your all query is right but as per your query condition you will get only last 7 day's result. getDate() gives today's system date. So I think from here your condition being false.

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=@TeamID and CardID=@CardID and LastModifiedDateTime between getDate()-7 and getDate()

So you can checkout by commenting green colored query:

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=@TeamID and CardID=@CardID and LastModifiedDateTime between getDate()-7 and getDate()
Rojalin Sahoo11y ago
As per your condition LastModifiedDataTime columns content the date which are not included.

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=@TeamID and CardID=@CardID and LastModifiedDateTime between getDate()-7 and getDate()

where TeamID = 1 and CardID=1 will retrive the data but your next condition will give false which bound the statement to give Null.

You can use like :

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=1 and CardID=1 or LastModifiedDateTime between DATEADD (day , -7 , GETDATE())and getDate()

or specify the date between which you need data

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=1 and CardID=1 or LastModifiedDateTime between DATEADD (day , -7 , '2015-01-10')and getDate()
Raja T11y ago
Hi, try like

before alter the sp, execute query in sqlserver like

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=1 and CardID=1 and LastModifiedDateTime between getDate()-7 and getDate()

here check results is coming or not

if results is null change query like bellow

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=1 and CardID=1 and DATEDIFF(DAY,[LastModifiedDateTime],GETDATE()) <= 7

Afer that alter the sp like
GO
ALTER PROCEDURE [dbo].[uspGetVote](@TeamID INT,@CardID INT)

AS

BEGIN

SELECT AVG(VoteValue) AS [AverageVoteValue] FROM Vote
WHERE TeamID=1 and CardID=1 and DATEDIFF(DAY,[LastModifiedDateTime],GETDATE()) <= 7

end