Solved

sql return 0 / null values

Posted on 2011-03-05
2
394 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
Comment Utility
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
Comment Utility
derekkromm: mmm, I see ... well, your final advise (scripting related) resolved the issue anyhow !
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

18 Experts available now in Live!

Get 1:1 Help Now