Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Int Value - error prompt
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 12/10/2012 :  06:07:33  Show Profile  Reply with Quote
Hi,

What does the line mean in the code below;

SELECT isnull(SUM(files.fileSize), 0) AS totalFileSize


ALTER FUNCTION [dbo].[fnc_statsTotalFileSizeByCompany] 
(
@companyid int,
@fromDate datetime = '01 Jan 1900 00:00'
)  
RETURNS bigint AS  
BEGIN 
	
	declare @totalFileSize bigint
	
	set  @totalFileSize = 	(
				SELECT isnull(SUM(files.fileSize), 0) AS totalFileSize
				FROM users 
				INNER JOIN files ON users.userid = files.userid 
				INNER JOIN statsDownload ON files.fileid = statsDownload.fileid
				WHERE users.companyid = @companyid AND downloaddate > @fromDate
				)
	
	return @totalFileSize

END



If, fileSize is declared as int, and the value apparently, goes beyond the range of int.. will this function need to be changed in anyway..

I have changed the filesize to Bigint in the table in the SQL database. But will this function also create any errors at runtime if no changes are done....

Sorry I dont have the DEV version to test on my machine..

Thanks

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/10/2012 :  06:13:54  Show Profile  Reply with Quote
>> What does the line mean in the code below; SELECT isnull(SUM(files.fileSize), 0) AS totalFileSize


Here,
1) you are calculating total fileSize
2) If that SUM is NULL, then replaces result with 0 (zero)






--
Chandu
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/10/2012 :  06:36:40  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Try it and see
declare @t table (i int)
insert @t select 800000000
insert @t select 800000000
insert @t select 800000000
declare @i bigint
set @i = (select sum(i) from @t where 1=1)
select @i

declare @t table (i int)
insert @t select 800000000
insert @t select 800000000
insert @t select 800000000
declare @i bigint
set @i = (select sum(convert,bigint,i)) from @t where 1=1)
select @i

note - if the query does not return any rows it will leave @totalFileSize as null.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/10/2012 :  06:44:17  Show Profile  Reply with Quote
small typo ........
(select sum(convert(bigint,i)) from @t where 1=1)


--
Chandu
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/10/2012 :  07:39:58  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Thanks - I had a bracket there but my usual error is to put a bracket where I should have a comma so I uncorrected it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000