Блог

Главная - Вопросы по программированию - Скалярнозначная функция SQL

Скалярнозначная функция SQL

#sql-server #tsql #parameters #user-defined-functions

#sql-сервер #tsql #параметры #определяемые пользователем функции

Вопрос:

Я пытаюсь получить значение прибыли (FilmBoxOfficeDollar — FilmBudgetDollars) на основе студии, указанной в качестве параметра функции.

 USE Movies;

GO

CREATE FUNCTION fnmovieProfits(@StudioName nvarchar(255))
  RETURNS int
AS
BEGIN
  RETURN (SELECT SUM(FilmBoxOfficeDollars - FilmBudgetDollars)
      FROM Film JOIN Studio
      ON Film.FilmStudioID = Studio.StudioID
      WHERE StudioName = @StudioName);  
END;

GO
SELECT [dbo].[fnmovieProfits]('Dreamworks');

Всякий раз, когда я запускаю это, чтобы извлечь часть данных, я получаю следующую ошибку:

 Msg 8115, Level 16, State 2, Line 13
Arithmetic overflow error converting expression to data type int.

Буду признателен за любую помощь!

Комментарии:

1. Возвращаемый параметр представляет собой int тип данных. Попробуйте объявить переменную, чтобы сначала поместить SUM() в нее, а затем вернуть переменную. Является ли FilmBoxOfficeDollar и FilmBudgetDollars оба типа данных int ?

2. На самом деле, при ближайшем рассмотрении вам нужно AS после RETURNS int и перед BEGIN .

3. @WEI_DBA Спасибо за отзыв! Да, оба FilmBoxOfficeDollars и FilmBudgetDollars являются int типами данных. Я также добавил AS now.

4. @WEI_DBA Я пытался использовать DECLARE для установки переменной, а затем SET it = to FilmBoxOfficeDollars - FilmBudgetDollars но по какой-то причине получаю сообщение об ошибке. Есть идеи?

Ответ №1:

Проблема, с которой вы сталкиваетесь, заключается в том, что вы превышаете допустимое значение 32-разрядного числа (INT); если вы приведете / преобразуете в 64-разрядное число (BIGINT) и вернете этот тип данных, проблема будет исправлена. Доказательство концепции, показывающее проблему:

 DECLARE @BigNumber INT=2000000000
select CONVERT(BIGINT,@BigNumber)   CONVERT(BIGINT,@BigNumber) --returns 4,000,000,000
select (@BigNumber   @BigNumber) --errors with "Arithmetic overflow error converting expression to data type int."

НО сделайте себе одолжение и используйте вместо этого view. Подобные скаляры ужасно влияют на производительность отчетов. Скалярные функции никогда не следует использовать, если они просто не выполняют вычисления на основе входных значений (т. Е. Не затрагивают базовые, сохраненные данные).

 CREATE VIEW dbo.v_StudioProfits
AS
   SELECT
      StudioName,
      SUM(CONVERT(BIGINT,FilmBoxOfficeDollars) - CONVERT(BIGINT,FilmBudgetDollars)) AS [Profit]
   FROM Film
   INNER JOIN Studio ON Film.FilmStudioID = Studio.StudioID
   GROUP BY StudioName
GO

SELECT * FROM dbo.v_StudioProfits WHERE StudioName='Dreamworks'

Соответствующее чтение типов данных SQL Server. В частности, целочисленные типы данных.

Ответ №2:

Ваша сумма превышает int диапазон. Вы должны определить возвращаемый тип как bigint :

 CREATE FUNCTION fnmovieProfits(@StudioName nvarchar(255))
  RETURNS bigint
AS.......

Максимальное значение, которое вы можете вернуть с int типом возвращаемого значения, является 2147483647 . Ваш sum , вероятно, больше этого.

Один из примеров функции, которая превышает возвращаемый тип:

 CREATE FUNCTION testFunction()
  RETURNS int
AS
BEGIN
  RETURN (SELECT 2147483647   1);  
END;

GO
SELECT [dbo].[testFunction]();

При ее выполнении вы получите следующую ошибку:

 Msg 8115, Level 16, State 2, Line 8
Arithmetic overflow error converting expression to data type int.

Таким образом, решение состоит в том, чтобы просто увеличить диапазон возвращаемых типов, заменив int на bigint .