Невозможно получить доступ к временным таблицам из функции

Я хотел бы получить количество конкретных записей. Поэтому мой запрос будет выглядеть следующим образом:

SELECT
    ID, 
    NAME,
    (SELECT...) AS UserCount // Stmt1
FROM MyTable

Проблема заключается в том, что 'Stmt1' является сложным выражением и не может быть записан как innerquery. Ну, я могу использовать функции, но инструкция включает "CREATE TABLE", поэтому я получаю следующее сообщение об ошибке

Cannot access temporary tables from within a function.

Каков наилучший способ выполнения задачи?

Ответы

Ответ 1

Вы можете использовать тип таблицы, определенный пользователем, чтобы решить вашу проблему.

Вы просто создаете переменную таблицы, например

CREATE TYPE [dbo].[yourTypeName] AS TABLE(
    [columeName1] [int] NULL,
    [columeName2] [varchar](500) NULL,
    [columeName3] [varchar](1000) NULL
)
GO

и вы можете объявить эту переменную таблицы в своей функции, например

    CREATE FUNCTION [dbo].[yourFunctionName] 
( 
    @fnVariable1 INT ,
    @yourTypeNameVariable yourTypeName READONLY
) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 

    SELECT .................
        FROM @yourTypeNameVariable 
        WHERE ........
    RETURN @r 
END

В вашей процедуре вы можете объявить свой тип таблицы, например

DECLARE @yourTypeNamevaribale AS yourTypeName

И вы можете вставить значения в эту таблицу, например

insert into @yourTypeNamevaribale (col,col,..)values(val,val,..)

передайте это своей функции, например

dbo.yourFunctionName(fnVariable1 ,@yourTypeNamevaribale )

пожалуйста, зайдите на этот метод, спасибо

Ответ 2

Да, вы не можете использовать таблицу #temp.

Как вы используете SQL Server 2008, почему бы вам не использовать табличную переменную вместо #temp-таблиц? Попробуйте.

Ответ 3

Я столкнулся с этим сообщением, когда начал использовать табличные переменные и переключился на временные таблицы по соображениям производительности только для того, чтобы найти временные таблицы не могли быть использованы в функции.

Я бы не решался использовать табличные переменные, особенно если вы играете с большими наборами результатов, поскольку они хранятся в памяти. См. Этот пост...

http://totogamboa.com/2010/12/03/speed-matters-subquery-vs-table-variable-vs-temporary-table/

Другие альтернативы будут...

  • Извлечение результата временной таблицы в другую функцию таблицы.
  • Преобразование кода в использование подзапросов

Ответ 4

В 99,99% случаев нет необходимости в каких-либо трюках с временными таблицами или подзапросами, но используйте функции агрегации, такие как COUNT, SUM или AVG в сочетании с OVER статья и (часто) PARTITION BY.

Я не уверен, чего пытался выполнить OP, но я предполагаю, что UserCount каким-то образом связан со значениями в MyTable. Таким образом, должен быть способ соединения MyTable с любой таблицей, создающей UserCount.

Самый простой пример - показать всех пользователей и общее количество пользователей

SELECT id
    , name
    , user_count = COUNT(*) OVER()
FROM MyUsers