Хранимые процедуры. :-(

[Scuder]

Люди! Скажите мне, что я не прав!!
Вот код запуска хранимой процедуры.

Код: Выделить всё

Public ATempDB As New ADODB.Connection
Public ATempRS As New ADODB.Recordset
Public ADOCmd As New ADODB.Command
Public ADOPrm As New ADODB.Parameter
Public sParmName As String

[...]

Set ADOCmd.ActiveConnection = ATempDB
ADOCmd.CommandType = adCmdStoredProc
ADOCmd.CommandText = "SP_Add_Document"

sParmName = "Return"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
        adParamReturnValue, , 0)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = -1

sParmName = "Comment"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adBSTR, adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Size = 250
ADOCmd.Parameters(sParmName).Value = "some text"

Set ATempRS = ADOCmd.Execute

Dim CurDocID As Long
CurDocID = ADOCmd.Parameters("Return").Value * -1

Set ADOCmd = Nothing
Set ADOPrm = Nothing

Код сильно урезан. Реально только в этой процедуре 13 входящих параметров. Это не предел. Для каждого параметра уходит 4-5 строчек кода. Ну можно сократить до 3-4 (в зависимости от типа данных). Но представьте, какой у меня получается листинг! 4 строки на параметр умножить на 13 плюс пустые строки между ними + объявления и запуск процедуры - около 75 строк кода!! Программа использует только на данный момент около 15 процедур! Это уже больше 1000 строк! Ну скажите мне - это нормально?? Это ведь не предел. Программа в стадии разработки. А что будет потом? Я же просто запустаюсь в этом мусоре.. Неужели нельзя вызвать процедуру, которая возвращает значение, а не рекордсет, с меньшими потерями?

[Konst_One]

если ты работаешь с MS SQL Server, то можно выполнять подготовленный запрос, например так:

Код: Выделить всё

Dim cn as ADODB.Connection
Dim Param1 as Integer
Dim Param2 as String
...
...
Param1=2
Param2="test"
SQL="declare @ret int " & vbcrlf
SQL=SQL & "exec @ret=Procedure1 " & Param1 & ", '" & Param2 & "'" & vbcrlf
SQL="select @ret"
Set rs=cn.Execute(SQL)
Debug.print rs(0).Value



[Scuder]

Нет, это всё понятно. Но я же написал - в случае, если процедура возвращает не рекордсет, а значение внутренней переменной. Нельзя это значение присваивать рекордсету. Приходится использовать результирующий параметр Return. И как следствие - объявлять каждый параметр вручную, как это описано в моём примере.

Вот я и спрашиваю - неужели это единственный способ выполнить такую процеДУРУ?

[Scuder]

Хочу также добавить, что в начале процедуры стоит

Код: Выделить всё

SET NOCOUNT ON

Т.е. никакого рекордсета не будет.

[alibek]

А написать функцию SetParameters, которая будет парсить строку и добавлять параметры, нельзя?
Напиши универсальную функцию, которая будет открывать параметры, запускать рекордсет и возвращать результат.

[Andrey Fedorov]

А написать функцию SetParameters, которая будет парсить строку и добавлять параметры, нельзя?
Напиши универсальную функцию, которая будет открывать параметры, запускать рекордсет и возвращать результат.

Да оно не всегда возможно - универсальную-то. Ведь возвращаться может один или несколько параметров...

Но чего страшного их описать-то (при желании можно в отдельном модуле)?
Только я бы при описании With использовал - оно более читабельно получается...

[Rainbow]

Не, ну написать-то можно, например, чтобы только входные параметры создавались... Только вот все равно где-то придется все эти параметры перечислять - с свою ли структуру запихивать, чтобы потом вызвать эту написанную функцию по заполнению входных параметров, заполнять ли объект command...

Scuder, а почему у тебя создание параметра занимает целых 4 строчки? Ведь размер и значение можно передать прямо в CreateParameter. Это раз. А во-вторых, при очень большом желании можно написать и так:

Код: Выделить всё

Command.Parameters.Append Command.CreateParameter(...)

Только я бы так все-таки не советовала - отлаживаться гораздо тяжелее будет.

Еще есть (наверное) вариант достать текст процедуры, отредактировать ее текст (вставить параметры) и исполнить. Но это, мне кажется, вариант еще хуже...

[Scuder]

Всем спасибо. Я просто думал, что существует кардинально отличающийся от приведённого подход.. Но раз так, будем юзать as is..

Насчёт функции.. Я что-то не совсем понял.. Чтобы заставить функцию задать параметры, она должна все типы параметров дёрнуть из процедуры.. Гемор, имхо.. Хотя, если кому не лень, киньте код (получить входящие параметры и текст хранимой процедуры), плз. Может, пригодится когда-нибудь..

[Andrey Fedorov]

Насчёт функции.. Я что-то не совсем понял.. Чтобы заставить функцию задать параметры, она должна все типы параметров дёрнуть из процедуры.. Гемор, имхо.. Хотя, если кому не лень, киньте код (получить входящие параметры и текст хранимой процедуры), плз. Может, пригодится когда-нибудь..

IMHO не стоит заморачиваться. Ибо это лишние запросы к серверу (а ведь сам смысл в том чтобы их избежать). Процедура должна описываться на клиенте.

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

[Scuder]

IMHO не стоит заморачиваться. Ибо это лишние запросы к серверу (а ведь сам смысл в том чтобы их избежать). Процедура должна описываться на клиенте.

Согласен, в принципе. Но, имхо, самый главный плюс в том, что процедуру можно править прямо на сервере, а не переделывать EXE'шник. А вот насчёт лишних запросов к серверу можно поспорить. Поскольку, и SQL-запрос в чистом виде, и запрос через хранимую процедуру обрабатываются на серваке, в любом случае клиент получает только результат.

[Andrey Fedorov]

Согласен, в принципе. Но, имхо, самый главный плюс в том, что процедуру можно править прямо на сервере, а не переделывать EXE'шник.

Да, но кардинально поправив процедуру на сервере тебе все одно придется лезть в Exe-шник чтобы поправить в нем параметры (добавить новые или удалить старые)... Так что...

[Scuder]

Да, но кардинально поправив процедуру на сервере тебе все одно придется лезть в Exe-шник чтобы поправить в нем параметры (добавить новые или удалить старые)... Так что...

Как правило, получается наоборот. Если нужна дополнительная опция в программе - один хрен код менять придётся. А вот если, например, появилась какая-нибудь дополнительная таблица, и нужно изменить сам запрос, привязав, допустим, эту таблицу ко всему остальному, то в этом случае, EXE'шник останется прежним.

[Andrey Fedorov]

Но и параметры процедуры при этом не меняются.

[Scuder]

Но и параметры процедуры при этом не меняются.

Параметры - нет. А содерижимое - да. В этом и плюс - поправил саму процедуру и всё. А пользователи и не заметили ничего. И обновляться им не нужно..

[Andrey Fedorov]

Дык а при чем тут параметры? Оно в любом случае все работать будет...

[Rainbow]

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

текст у меня не получилось быстро получить. А вот параметры - пожалуйста!

Код: Выделить всё

Private Sub GetProcedureParams(sProcedureName As String)
    Dim cnn As New Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDB;Data Source=(local)"
    cnn.CursorLocation = adUseClient
    cnn.Open
   
    Dim rst As New Recordset
    Set rst = cnn.OpenSchema(adSchemaProcedureParameters, Array(Empty, Empty, sProcedureName, Empty))
   
    Dim i As Long
    For i = 0 To rst.RecordCount - 1
        Debug.Print rst!PARAMETER_NAME
        rst.MoveNext
    Next

End Sub

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

подробнее о полях результирующего рекордсета читайте тут:
http://msdn.microsoft.com/library/defau ... _oledb.asp

[Scuder]

Rainbow, огромнейшее спасибо за код и ссылку!

Код: Выделить всё

Debug.Print rst!PARAMETER_NAME
Debug.Print rst!TYPE_NAME
Debug.Print rst!CHARACTER_MAXIMUM_LENGTH

Таким образом получаем название параметра, тип данных и длину, если string (nvarchar). Зная всё это уже действительно можно написать универсальную процедуру для установки параметров.

Ещё раз thx!

[Rainbow]

Таким образом получаем название параметра, тип данных и длину, если string (nvarchar). Зная всё это уже действительно можно написать универсальную процедуру для установки параметров.

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

Ещё раз thx!

Да пожалуйста! Удачи!

Ой, только ты там внимательно посмотри - TYPE_NAME ли? Может, DATA_TYPE?

[Scuder]

Ага, абсолютно согласен.

А вот нашёл как текст выдернуть:

Код: Выделить всё

Set rst = cnn.Execute("sp_helptext '" & sProcedureName & "'")

Dim i As Long
   
For i = 0 To rst.RecordCount - 1
        Debug.Print rst(0)
        rst.MoveNext
Next

[Rainbow]

О! Забавно
Спасибо.

[Ennor]

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

select @Par1 as [Par1], @Par2 as [Par2], ...

В этом случае тебя вообще практ. ничто не ограничивает.