Как вызвать хранимую процедуру из пользовательской функции В SQL Server 2000. Как вызвать процедуру в sql

sql-server - Как вызвать хранимую процедуру из пользовательской функции В SQL Server 2000

У меня недавно была аналогичная проблема. На самом деле сообщение об ошибке не отформатировано должным образом, поскольку sp_executesql является расширенной хранимой процедурой, которую вы можете проверить с помощью следующих script: select objectproperty(object_id('sp_executesql'),'IsExtendedProc')

возвращает 1

Поскольку мы не можем использовать sp_executesql даже его XP, мне пришлось найти другое обходное решение, используя sp_OAMethod. Мой сценарий заключался в том, как динамически найти число строк в таблице в соответствии с некоторыми критериями (null значения в моем сценарии). Используя sp_OAMethod, я построил следующую функцию:

IF object_id(N'dbo.fc_ContaRegistros_x_Criterio') is not null DROP FUNCTION [dbo].[fc_ContaRegistros_x_Criterio] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.fc_ContaRegistros_x_Criterio ( @str_TBName VARCHAR(100), @str_Criter VARCHAR(500) ) RETURNS BIGINT AS BEGIN -- Objetivo : Contar numero de registros de uma determinada tabela de acordo com o critério passado -- Criação : Josué Monteiro Viana - 09/07/09 /* Exemplo: DECLARE @count INT SET @count = dbo.fc_ContaRegistros_x_Criterio('master.dbo.sysobjects', '') PRINT @count SET @count = dbo.fc_ContaRegistros_x_Criterio('crk.dbo.acao', 'where cod_acao is null') PRINT @count */ DECLARE @int_objSQL INT, @int_erros INT, @int_objSelectCountResult INT, @bint_SelectCount BIGINT, @sql NVARCHAR(2000) EXEC @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT EXEC @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE EXEC @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.' --SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' WHERE ' + @str_Criter SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' ' + @str_Criter SET @sql = 'ExecuteWithResults("' + @sql + '")' EXEC @int_erros = sp_OAMethod @int_objSQL, @sql, @int_objSelectCountResult OUTPUT EXEC @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT EXEC @int_erros = sp_OADestroy @int_objSQL -- debug info: not valid inside a fc --if @int_erros <> 0 EXEC sp_OAGetErrorInfo @int_objSQL else print 'ok' if @int_erros <> 0 SET @bint_SelectCount = @int_erros RETURN @bint_SelectCount END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

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

С наилучшими пожеланиями, Жозе Монтейро Виана

qaru.site

sql - Как вызвать хранимую процедуру в табличной функции в SQL Server 2005?

Я хочу вызвать Хранимую процедуру, которая возвращает таблицу в Таблице значений. Как создать табличную функцию?

Например: "sp_GetTable" - это хранимая процедура. Он возвращает таблицу. Здесь я хочу написать одну функцию под названием "fn_GetTable"

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

Вот моя хранимая процедура:

ALTER PROC sp_GetTable AS BEGIN ------------ Creating TempTable(#MasterTable) ---------------------- CREATE TABLE #MasterTable (ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50), D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50)) ------------ Creating TempTable(#TempItems) ---------------------- CREATE TABLE #TempItems(ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50), D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50)) ------------ Creating & Inserting TempTable(@Dim) ---------------------- DECLARE @Dim TABLE (DCodes VARCHAR(100)) INSERT INTO @Dim SELECT D1_Code FROM MAS_SizeType WHERE D1_Code <> '' INSERT INTO @Dim SELECT D2_Code FROM MAS_SizeType WHERE D2_Code <> '' INSERT INTO @Dim SELECT D3_Code FROM MAS_SizeType WHERE D3_Code <> '' ------------ Inserting data into TempTable(#MasterTable) ---------------------- INSERT INTO #MasterTable SELECT STR_Item.ItemID, STR_Item.ItemName, STR_Item_Specifications.SpecificationName, STR_Item.D1, STR_Item.D2, STR_Item.D3, MAS_SizeType.D1_Code, MAS_SizeType.D2_Code, MAS_SizeType.D3_Code FROM STR_Item INNER JOIN MAS_SizeType ON STR_Item.SizeTypeID = MAS_SizeType.SizeTypeID INNER JOIN STR_Item_Specifications ON STR_Item.SpecificationID = STR_Item_Specifications.SpecificationID -------------------- Inserting Data into #TempItems Table ---------------------------- INSERT INTO #TempItems SELECT * FROM #MasterTable ------------------Cursor for All dimensions details into single row for each items ---------------------- DECLARE @DCode VARCHAR(MAX), @Cnt INT SET @Cnt = 0 DECLARE ColAdd CURSOR FOR SELECT DISTINCT DCodes FROM @Dim OPEN ColAdd FETCH NEXT FROM ColAdd INTO @DCode WHILE (@@FETCH_STATUS = 0) BEGIN SET @Cnt = 1 EXECUTE ('ALTER TABLE #TempItems ADD ' + @DCode + ' VARCHAR(50)') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D1 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D1_Code = ''' + @DCode + '''') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D2 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D2_Code = ''' + @DCode + '''') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D3 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D3_Code = ''' + @DCode + '''') FETCH NEXT FROM ColAdd INTO @DCode END CLOSE ColAdd DEALLOCATE ColAdd IF (@Cnt = 1) BEGIN SELECT @DCode = STUFF(( SELECT DISTINCT '],[' + ltrim(DCodes) FROM @Dim ORDER BY '],[' + ltrim(DCodes) FOR XML PATH('') ), 1, 2, '') + ']' EXECUTE ('SELECT ItemID, ItemName, Specifications, ' + @DCode + ' FROM #TempItems ORDER BY ItemName') END DROP TABLE #TempItems DROP TABLE #MasterTable END

qaru.site

Как вызвать хранимую процедуру в таблице, имеющей значение в SQL Server 2005?

Например: «sp_GetTable» - это хранимая процедура. Он возвращает таблицу. Здесь я хочу написать одну функцию под названием «fn_GetTable»

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

Вот моя хранимая процедура:

ALTER PROC sp_GetTable AS BEGIN ------------ Creating TempTable(#MasterTable) ---------------------- CREATE TABLE #MasterTable (ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50), D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50)) ------------ Creating TempTable(#TempItems) ---------------------- CREATE TABLE #TempItems(ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50), D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50)) ------------ Creating & Inserting TempTable(@Dim) ---------------------- DECLARE @Dim TABLE (DCodes VARCHAR(100)) INSERT INTO @Dim SELECT D1_Code FROM MAS_SizeType WHERE D1_Code <> '' INSERT INTO @Dim SELECT D2_Code FROM MAS_SizeType WHERE D2_Code <> '' INSERT INTO @Dim SELECT D3_Code FROM MAS_SizeType WHERE D3_Code <> '' ------------ Inserting data into TempTable(#MasterTable) ---------------------- INSERT INTO #MasterTable SELECT STR_Item.ItemID, STR_Item.ItemName, STR_Item_Specifications.SpecificationName, STR_Item.D1, STR_Item.D2, STR_Item.D3, MAS_SizeType.D1_Code, MAS_SizeType.D2_Code, MAS_SizeType.D3_Code FROM STR_Item INNER JOIN MAS_SizeType ON STR_Item.SizeTypeID = MAS_SizeType.SizeTypeID INNER JOIN STR_Item_Specifications ON STR_Item.SpecificationID = STR_Item_Specifications.SpecificationID -------------------- Inserting Data into #TempItems Table ---------------------------- INSERT INTO #TempItems SELECT * FROM #MasterTable ------------------Cursor for All dimensions details into single row for each items ---------------------- DECLARE @DCode VARCHAR(MAX), @Cnt INT SET @Cnt = 0 DECLARE ColAdd CURSOR FOR SELECT DISTINCT DCodes FROM @Dim OPEN ColAdd FETCH NEXT FROM ColAdd INTO @DCode WHILE (@@FETCH_STATUS = 0) BEGIN SET @Cnt = 1 EXECUTE ('ALTER TABLE #TempItems ADD ' + @DCode + ' VARCHAR(50)') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D1 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D1_Code = ''' + @DCode + '''') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D2 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D2_Code = ''' + @DCode + '''') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D3 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D3_Code = ''' + @DCode + '''') FETCH NEXT FROM ColAdd INTO @DCode END CLOSE ColAdd DEALLOCATE ColAdd IF (@Cnt = 1) BEGIN SELECT @DCode = STUFF((SELECT DISTINCT '],[' + ltrim(DCodes) FROM @Dim ORDER BY '],[' + ltrim(DCodes) FOR XML PATH('') ), 1, 2, '') + ']' EXECUTE ('SELECT ItemID, ItemName, Specifications, ' + @DCode + ' FROM #TempItems ORDER BY ItemName') END DROP TABLE #TempItems DROP TABLE #MasterTable END

sql sql-server sql-server-2005 stored-procedures user-defined-functions27k

stackoverrun.com

c# - Как вызвать функцию/процедуру в предложении SQL where?

Я застрял в последнее время, пытаясь вызвать функцию в моем sql-запросе. В идеале я хотел бы сделать следующее:

from c in db.items where calcDistance(c.Latitude, c.Longitude, Latitude, Longitude) =< radius select ...

Однако, насколько я знаю, я не могу вызвать локальную функцию С# внутри предложения where. Я провел несколько исследований и попытался создать хранимую процедуру для вызова пользовательской функции, которая выполняет эквивалент, такой как:

CREATE PROCEDURE dbo.DistanceProc @lat1 decimal, @long1 decimal, @lat2 decimal, @long2 decimal AS DECLARE @return FLOAT SET @return = dbo.Distance(@lat1, @long1, @lat2, @long2) SELECT @return

где dbo.Distance - моя пользовательская функция с той же логикой, что и моя оригинальная функция С#. Затем я попытался добавить импорт функции, но без скалярного типа FLOAT я попытался вернуть double. Затем попытался использовать этот код:

from c in db.items where radius >= db.DistanceProc(c.Latitude, c.Longitude, Latitude, Longitude) select ...

Однако db.DistanceProc() возвращает тип System.Data.Entity.Core.Objects.ObjectResult <double?>, А не только обычный двойной. Я попытался добавить к этому объект.Cast <double>(). FirstOrDefault(), но затем он не может преобразовать в выражение хранилища.

На данный момент я почти зациклен на идеях. Есть ли простой способ достичь этого? Все, что мне нужно, это выполнить эту дополнительную логику вычисления расстояния в предложении where для каждого элемента в БД. Я также смотрел, как просто возвращаю все элементы в IEnumerable, а затем запускаю логику на нем после локального использования, но по мере того как БД достаточно велик, невозможно возвратить каждый элемент.

Благодарю!

РЕДАКТИРОВАТЬ:

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

LINQ to Entities не распознает метод "System.Nullable'1 [System.Double] Distance (System.Decimal, System.Decimal, System.Decimal, System.Decimal)", и этот метод не может быть переведен в выражение хранилища,

Есть идеи?

qaru.site

server-2012 - Как правильно вызвать хранимую процедуру из EXEC (SQL)?

You can use it either way, however there is a one thing you should keep in mind EXEC() function is SQL injection prone

Take a look at this:

DECLARE @Data varchar(50) SET @Data= ''''';TRUNCATE TABLE corruptTable' execute (N'select * from corruptTable where Data = ' + @Data)

Executing this command, will give you no results (since we added that our data equals empty string) however, if you query the table ,table will be empty.

Where as in sp_executesql you are explicitly declaring parameters and

@Param = ''''';TRUNCATE TABLE corruptTable' will be compared as it is

So in the example:

DECLARE @Param varchar(50) SET @Param= ''''';TRUNCATE TABLE corruptTable' execute sp_executesql N'select * from corruptTable where Data = @Data',N'@Data varchar(50)',@Data = @Param

It wont yield any results, but it wont execute truncate table, because like i said, it is only treated as a parameters. Other than that, performance vise, there is no difference.

Note that you should call procedure with schema name included, and should be aware that dynamic SQL executions will create a new plan individually from stored procedure each time you call it.

Update

Since you are testing it on production,and the tables are busy, there are many reasons why "sometimes" its slow. I cannot give you exact details because i dont know logic and environment in the first procedure, but here are some things to watch for:

Statistics Update in process: before plan is created, query optimizer checks whether statistics are up to date - since table is busy, statistics will be updated automatically at certain insert/update threshold and it can happen that it waits for statistics to be updated first before it can proceed, OR it can update statistics by itself so again you have to wait
Locks : I assume your first procedure is retrieving records from a certain table. It can happen that while it tried to get certain records, these records/table are already locked by some update/insert/delete statement. So again you had that slight delay before it actually executed.

Also if you are getting a lot of records from first procedure, consider using temp table instead, table variable is not the best choice.

And lastly you can always diagnose procedure execution using Extended Events, and log it to a table/file if it exceeds certain threshold, along with execution plan,and waits so you can compare it with the regular executions.

dba.stackovernet.com

Как вызвать хранимую процедуру из пользовательской функции В SQL Server 2000 MS SQL Server

Либо вам нужно изменить хранимую процедуру как функцию, определенную пользователем, либо наоборот.

Одним из грубых способов добиться того, что вы ищете, является наличие инструкции exec в пакетном скрипте и вызов этого пакетного скрипта из вашей функции. Что-то вроде этого:

create function <functionName> exec master.sys.xp_cmpshell 'C:\storedProc.bat' .... .... return @return end

Подробнее о xp_cmpshell на MSDN .

Вы не можете вызывать регулярные хранимые процедуры из функций – только другие функции или некоторые расширенные хранимые процедуры. См. Здесь статью BOL (с SQL 2005). Попытка вызвать стандартную хранимую процедуру из UDF приведет к следующей ошибке …

Msg 557, уровень 16, состояние 2, строка 1 Только функции и некоторые расширенные хранимые процедуры могут выполняться внутри функции.

У меня недавно была аналогичная проблема. Фактически сообщение об ошибке не отформатировано должным образом, поскольку sp_executesql является расширенной хранимой процедурой, которую вы можете проверить по следующему сценарию: select objectproperty(object_id('sp_executesql'),'IsExtendedProc')

возвращает 1

Поскольку мы не можем использовать sp_executesql даже это XP, мне пришлось найти другое обходное решение, используя sp_OAMethod . Мой сценарий состоял в том, чтобы найти количество строк динамически в таблице в соответствии с некоторыми критериями ( null значения в моем сценарии). Используя sp_OAMethod я построил следующую функцию:

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

С наилучшими пожеланиями, Жозе Монтейру Виана

sqlserver.bilee.com

Как вызвать хранимую процедуру в табличной функции в SQL Server 2005? MS SQL Server

Я хочу вызвать Хранимую процедуру, которая возвращает таблицу в Таблице Значения. Как создать табличную функцию?

Например: «sp_GetTable» – это хранимая процедура. Он возвращает таблицу. Здесь я хочу написать одну функцию под названием «fn_GetTable»

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

Вот моя хранимая процедура:

Solutions Collecting From Web of "Как вызвать хранимую процедуру в табличной функции в SQL Server 2005?"

Это можно сделать из Scalar Function, что невозможно сделать из функции Table Valued. Скалярнозначные функции возвращают одно значение типа данных и не могут также возвращать таблицы. Поскольку вы не можете выбрать из хранимой процедуры (вам нужно EXEC ), в этой таблице нет места для функции с табличной оценкой.

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

От: Перезапись хранимых процедур как функций

Повторная запись хранимой процедуры в виде таблицы-значения даст вам максимальную производительность.

Не могу. Вы должны переписать свой SP как UDF.

редактировать

По-видимому, есть некоторые уродливые обходные пути, использующие openquery . Но я бы рекомендовал переписать ваш SP в функцию, затем вы можете вызвать новую функцию из старого SP (если вам нужно)

sqlserver.bilee.com