Solved

sql return 0 / null values

Posted on 2011-03-05
2
397 Views
Last Modified: 2012-05-11
when running this stored procedure -- its keeps crashing the web-page.

I suspect its because the value is zero -- how do I prevent this from happening (via SQL)
@COSID	       int,
@StartDateTime  datetime,
@FinishDateTime datetime

AS
	DECLARE @table1Value int
	
	SELECT @table1Value = (
			SELECT SUM(ChargeableAmount) 
			FROM [TDRData] 
			WHERE (TransactionKey = 6) AND (COSID =@COSID) 
			  AND (TransactionTime BETWEEN @StartDateTime AND @FinishDateTime))
	
	SELECT TotalValue = @table1Value

Open in new window

0
Comment
Question by:amillyard
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35044974
0 is not the same as null

if its returning null, do "SELECT TotalValue = isnull(@table1Value, 0)"

there's nothing wrong with the code itself from what has been provided. you can always change it to have an output parameter rather than simply selecting a value at the end
@COSID	       int,
@StartDateTime  datetime,
@FinishDateTime datetime,
@TotalValue decimal(19,5) output

AS
	DECLARE @table1Value int
	
	SELECT @table1Value = (
			SELECT SUM(ChargeableAmount) 
			FROM [TDRData] 
			WHERE (TransactionKey = 6) AND (COSID =@COSID) 
			  AND (TransactionTime BETWEEN @StartDateTime AND @FinishDateTime))
	
	SELECT @TotalValue = isnull(@table1Value, 0)

Open in new window


Then, when you call the proc, include an "@TotalValue out" parameter
0
 

Author Closing Comment

by:amillyard
ID: 35044995
derekkromm: mmm, I see ... well, your final advise (scripting related) resolved the issue anyhow !
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

932 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now