Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Чайниковские вопросы по хранимым процедурам

Коллеги, есть несколько глупых вопросов по хранимым процедурам, помогите!
1. Существует ли способ узнать параметры возвращаемого хранимой процедурой набора данных без запуска хранимой процедуры? Т.е. наименование, тип и порядок возвращаемых столбцов.
1а. Для параметров хранимой процедуры, как я понимаю, такой способ заведомо есть. Они должны, наверное, быть где то в sys.objects. Или я ошибаюсь?
2. Существует ли способ сохранить возвращаемые процедурой данные в xml? Не переписать хранимку, а переформатировать у готовой процедуры result set в xml и уже его присвоить переменной или сохранить в таблицу.
По exec ... RESULT SETS (as for xml) не могу найти примера категорически. И сам не могу победить. И вообще, оно вроде не для этого?
Пока не смог ничего другого придумать, как сделать запускач-обертку.
Хранимую процедуру, которая принимает параметр имя sp, набор входных параметров в виде имя - значение в xml, и описание выходных в виде название - тип, тоже в хмл.
А внутри создаю с помощью динамического sql временную таблицу, рисую опять же динамически insert ... Exec в эту временную таблицу, а потом делаю из нее select * from... for xml raw

Но куча ограничений, и куча ручной работы.
И вообще, мне кажется я перемудрил.

Помогите, а?
Ответ:
uaggster
Идея была радикально ускорить вывод этих данних следующим образом:
1. Создаем на сервере некую очередь из этих хранимых процедур. Возможно, просто таблицу название - параметры.
2. Создаем job, который будет запускать процедуры из этого пула по очереди, и пишет их вывод в таблицу процедура - дата - параметры - результат (параметры - результат - в хмл, конечно).
Для решения таких задач придумали Service Broker.
uaggster
2. Существует ли способ сохранить возвращаемые процедурой данные в xml? Не переписать хранимку, а переформатировать у готовой процедуры result set в xml и уже его присвоить переменной или сохранить в таблицу.
Отдаю на растерзание -
Вопрос: SQL Server 2008 R2 изменение хранимой процедуры

Имеется в наличии база данных на SQL Server 2008 R2 установленная на сервере.
Досталась в наследство.

Я открываю базу с ярлыка на рабочем столе "Среда Microsoft SQL Server management" и БЕЗ ПАРОЛЯ, пароль вводить НЕ предлагает.
Мне необходимо внести изменения в одну из хранимых процедур.
Хранимая процедура открывается на изменение по правой кнопке мыши, я изменяю текст хранимой процедуры, но при сохранении текст хранимой процедуры сохраняется в отделный файл на диск в папку "Мои документы\SQL Server management\Proects\SQLQery1.sql"

Однако структуру таблиц я изменить и сохранить могу.

Вопрос: Как изменить и сохранить хранимую процедуру в Среде Microsoft SQL Server management ?
Ответ: Нажал кнопку "F5" в списке появилась хранимая процедура

Извините за мою глупость, раньше я не работал раньше с SQL Server , но работал с другими базами данных, там другой интерфейс.

Теперь если в будущем вносить изменения в эту процедуру "add_anzap" то это делается Командой "ALTER PROCEDURE" в этом-же тексте ("сценарий" он называется ?) ?!
Вопрос: Возврат значения из хранимой процедуры (Access & SQL)

В SQL создана хранимая процедура. Я хочу получить значение @patient_count в Access, только не нашла как это можно сделать.

ALTER PROCEDURE [dbo].[proc_count_patients_visit]
	@date_1 Date,
	@date_2 Date,
	@patient_count int output
AS
SET NOCOUNT ON; 

select @patient_count = Count(id_patient_history)	
FROM patients_history 
WHERE momartvis_tarigi Between @date_1 And @date_2
return @patient_count
GO


Dim t As Integer
Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("sproc_count_patients_visit")
qdf.SQL = "EXEC proc_count_patients_visit @date_1='" & Format(Me.date_1, "yyyymmdd") & "', @date_2='" & Format(Me.date_2, "yyyymmdd") & "'"
qdf.ReturnsRecords = True

t = "@patient_count" ?????
Ответ:
NickBell
guest_rusimport,

Да, такой пример для Дао подошел. Большое спасибо за помощь.


В хранимой процедуре для SQL заменила следующим образом:
ALTER PROCEDURE [dbo].[proc_count_patients_visit]
	@date_1 Date,
	@date_2 Date
AS
SET NOCOUNT ON; 

select Count(id_patient_history) AS RETURN_VALUE
FROM patients_history 
WHERE momartvis_tarigi Between @date_1 And @date_2

---------------
Private Sub cmd_count_visit_Click()
Dim rst As DAO.Recordset
Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("sproc_count_patients_visit")
qdf.SQL = "EXEC proc_count_patients_visit @date_1='" & Format(Me.date_1, "yyyymmdd") & "', @date_2='" & Format(Me.date_2, "yyyymmdd") & "'"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Me.count_visit = rst.Fields(0)
End Sub
Вопрос: Хранимая процедура и транзакция.

Здравствуйте!

Всегда предполагал что содержимое хранимой процедуры выполняется внутри неявной транзакции. Но простой тест показал что это не так. Создал хранимую процедуру с двумя DELETE первый выполняется успешно, второй падает. После запуска процедуры вижу что первый DELETE таки удалил данные из базы. Т.е. транзакция не откатилась. Значит нужно явно писать BEGIN TRAN и END TRAN?
Ответ:
Новичек sql
Glory
Что для вас означает "автоматически" ?
Где должен быть этот автомат, который будет решать, когда начинать транзакцию, что в нее включать и когда и как завершать ?

Автоматически для меня означает что содержимое хранимой процедуры либо выполнится целиком, либо не выполнится вообще (если там есть запросы на модификацию содержимого, они откатятся). Начинать транзакцию с началом хранимой процедуры, завершать в конце. Если ошибок не происходило, то commit-ом, если что-то пошло не так, то rollback-ом.

вот это и напишите в своей хранимке.

вы сейчас напрограммировали "два с половиной дерева" и думаете что это "весь лес" и есть.
организация транзакции выполняется тривиально, кроме демагогии сделать это не мешает ничто.

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

ХП - не вьюха - ее программировать надо.

есть такая опция IMPLICIT_TRANSACTIONS - в целом, включайте себе, потом расскажете, к чему пришли. и насколько это круто "неявные транзакции".
Вопрос: Оборачивание запросов в хранимые процедуры

Всем привет, есть такое задание:
создать хранимые процедуры на SELECT, INSERT/UPDATE и DELETE. Есть таблица(my_table) для этих процедур с двумя столбцами ID и NAME.
Не могу понять как например запрос
MySQL
1
select * from my_table;
превратить в хранимую процедуру. В голову пришло только:
MySQL
1
2
3
4
5
CREATE PROCEDURE sel ()
begin
select * from my_table;
end
//
Подскажите пожалуйста что не так, и где про это почитать?
Ответ: Сам отвечу - по умолчанию дампятся только триггеры, хранимые процедуры можно задампить с помощью параметра
--routines - задампит хранимые процедуры и функции
Вопрос: Создание хранимой процедуры. В чём может быть проблема?

Есть простой запрос. Из этого запроса необходимо создать хранимую процедуру.
Запрос:
SELECT
  id, name, category, ROUND((
    1000 * 6371 * ACOS (
      COS ( RADIANS(51.42689) )
      * COS( RADIANS( latitude ) )
      * COS( RADIANS( longitude ) - RADIANS(31.42689) )
      + SIN ( RADIANS(51.522256) )
      * SIN( RADIANS( latitude ) )
    )
  ), 3) AS distance, address, latitude, longitude, 
  (SELECT name FROM localities WHERE id = locality_id) AS locality_name, locality_id
FROM shops
HAVING distance < 150
ORDER BY distance;


Создание хранимой процедуры из вышеописанного запроса:
DROP PROCEDURE IF EXISTS `get_nearest_shops`;
DELIMITER //
CREATE PROCEDURE get_nearest_shops(
  IN p_lat DOUBLE, 
  IN p_lng DOUBLE, 
    IN p_radius DOUBLE,
    OUT p_id INT(10),
    OUT p_name VARCHAR(45),
    OUT p_category ENUM('undefined','auto','children_prod','food','game','book',
                        'electronics','beuty&health','fashin','footwear','clothing',
                        'sports','homewere','pet_prod','services','gift&flowers'),
    OUT p_distance DOUBLE, 
    OUT p_latitude DOUBLE,
    OUT p_longitude DOUBLE,
    OUT p_locality VARCHAR(45),
    OUT p_locality_id INT(10))
BEGIN 
  DECLARE sign_after_point INT(10) DEFAULT 3;
  SELECT
    id, name, category, ROUND((
    1000 * 6371 * ACOS (
      COS ( RADIANS(p_lat) )
      * COS( RADIANS( latitude ) )
      * COS( RADIANS( longitude ) - RADIANS(p_lng) )
      + SIN ( RADIANS(p_lat) )
      * SIN( RADIANS( latitude ) )
      )
    ), sign_after_point) AS distance, latitude, longitude, 
        (SELECT name FROM localities WHERE id = locality_id) AS locality, 
        locality_id
  INTO
    p_id, p_name, p_category, p_distance, p_latitude, p_longitude, 
        p_locality, p_locality_id
    FROM shops
  HAVING distance < p_radius
  ORDER BY distance;
END;//


В результате хранимая процедура создаётся без единой ошибки но в отличии от простого запроса не выводит информацию, при том что входящие данные одинаковы.
В чем может быть ошибка? Как лучше поступить?
Спасибо.
Ответ:
alexiuscrow

В результате хранимая процедура создаётся без единой ошибки но в отличии от простого запроса не выводит информацию, при том что входящие данные одинаковы.
В чем может быть ошибка? Как лучше поступить?
Спасибо.



Так ты её так написал, что она все поля запихивает в выходные парамертры (причём записей в запросе может быть много,
а возвращаешь ты только как бы одну запись, значения из неё).

убери из запроса фразу INTO -- запрос будет формировать набор данных, и ты сможешь его получить на клиентской стороне как нормальный набор данных (при этом все OUT-параметры тоже не нужны).
Вопрос: Хранимые процедуры

Помогите создать хранимую процедуру. Ни разу их не создавал - по ней и буду учиться.
Дано:
Таблица tema
id: int (11) autoincrement
naim: string(255)
active: boolean

Цель:
создать хранимую процедуру для добавления данных по условиям:
1. Если строки с посылаемым текстом нет - добавить, active =1 и выдать ID добавленной строки
2. Если есть, но active =0 - перевести active в 1 и выдать ID измененной строки
3. Если есть и active = 1 - выдать ID строки
Ответ: Мда... Если послать в хранимую процедуру что либо в кавычках - вот тут
Код SQL
1
CREATE DEFINER=`root`@`localhost` PROCEDURE `addtema`(`itema` VARCHAR(254), OUT iid INT)
выпадет ошибка....
Но если послать \"Проба\", то ошибки не возникает... Но я не могу парсить весь текст в программе!
Как бороться?
Вопрос: Хранимая процедура для увелечения цены на поставки товара на 15%

Есть таблица Поставка в ней есть цена_поставки_изделия
//цена с плавающей запятой
Нужна Хранимая процедура для увелечения цены на поставки товара на 15%.
MS SQL Server 2005
Ответ: Этим запросом вы создали хранимую процедуру. Чтобы она отработала ее нужно вызвать, как в показанном мной втором примере.
Вопрос: Авторизация в mysql только для операций с хранимыми процедурами

Привествую!

Как создать пользователя в мускуле для работы только с хранимыми процедурами и желательно функциями? Что бы он никаких иных действий в мускуле сделать не смог?

Не нашел такой информации в документациях...

И еще один вопрос. Я пишу приложение на C# оно работает с мускулом, но мне не хочется для каждого клиента создавать своего пользователя в мускуле. авторизация проходит внутри БД. Так вот и час вопрос. Я могу создать только одного пользователя для работы с БД? Смогут через него работать пару сотен клиентов одновременно?
Ответ:
Сообщение от The KING
Как создать пользователя в мускуле для работы только с хранимыми процедурами и желательно функциями? Что бы он никаких иных действий в мускуле сделать не смог?
Обычно вопросы прав в БД решаются с пом. GRANT (выдать права) и REVOKE (отнять права).
Вопрос: Выполнение DTSX хранимой процедурой, проблема с правами

Запускаю DTSX пакет с помощью хранимки на TSQL. В пакете есть два подключения ADO к SQL server 2017, одно подключение к серверу где развернут пакет , другое к удаленному серверу. В подключениях используется аутентификация WINDOWS. Проблема проявляется при попутке подключения к удаленному серверу, пишет что нет прав. Если выполнять пакет в режиме отладки, то все работает, а вот когда через хранимую процедуру, возникает указанная ошибка с правами. Если использовать SQL аутентификацию все работает. Кто подскажет как настроить корректную работу?
Ответ: alexeyvg,
Спасибо, стало понятнее.

У меня в пакете запускается хранимая процедура на удаленном сервере, может предложите вариант, как сделать чтобы она запускалась с правами доменного пользователя, а не с SQL авторизацией?