SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dr223
Constraint Violating Yak Guru

434 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
2224 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
2224 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000