Int Value - error prompt - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Int Value - error prompt

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-12-10 : 06:07:33

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-10 : 06:13:54

>> 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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-10 : 06:36:40

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-10 : 06:44:17

small typo ........
(select sum(convert(bigint,i)) from @t where 1=1)

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-10 : 07:39:58

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources