Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Хранимая процедура в SQL запросе - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Хранимая процедура в SQL запросе (Макросы/Sub)
Хранимая процедура в SQL запросе
Elhust Дата: Вторник, 07.02.2017, 15:49 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 184
Репутация: -1 ±
Замечаний: 0% ±

Excel 2010
Добрый день всем знатокам и повелителям VBA , подскажите как такой запрос заставить запускаться , перечитал много всего, у меня SQL server / MS 2010 , через ADO, Пришло понимание что только через Объект Command и CreateParameter , но что то у меня не выходит ((((((( :'(
[vba]
Код

DECLARE @BP_ID nvarchar(10)='0'
DECLARE @lastName nvarchar(20)
DECLARE @firstName nvarchar(20)
DECLARE @secondName nvarchar(20)
DECLARE @birthDate datetime
DECLARE @sex int
DECLARE @seriesNumber nvarchar(10)
DECLARE @docdate datetime
DECLARE @AfsMessage nvarchar(1000)
SELECT
@lastName=a.ANKETA_FAM,
@firstName=a.ANKETA_IM,
@secondName=a.ANKETA_OT,
@birthDate=a.ANKETA_BIRTH_DATE,
@sex=CASE a.ANKETA_SEX WHEN N'Ж' THEN 0 ELSE 1 END,
@seriesNumber=ISNULL(SUBSTRING(ANKETA_SERIESPASSPORT,1,2)+SUBSTRING(ANKETA_SERIESPASSPORT,4,2)+ANKETA_NUMBERPASSPORT,''),
@docdate=ISNULL(ANKETA_DATEPASSPORT,'19000101')
FROM port.Ankets a
WHERE ANKETA_ID=4362;
EXECUTE [MainDWH].[dbo].[sp_AfsSOAP_Request]
@BP_ID
,@lastName
,@firstName
,@secondName
,@birthDate
,@sex
,@seriesNumber
,@docdate
,@AfsMessage OUTPUT
SELECT BP_ID  AfsMessage
[/vba]
Благодарю за внимание
К сообщению приложен файл: 8029823.xls (58.0 Kb)


Каждый сам выбирает правила игры
 
Ответить
СообщениеДобрый день всем знатокам и повелителям VBA , подскажите как такой запрос заставить запускаться , перечитал много всего, у меня SQL server / MS 2010 , через ADO, Пришло понимание что только через Объект Command и CreateParameter , но что то у меня не выходит ((((((( :'(
[vba]
Код

DECLARE @BP_ID nvarchar(10)='0'
DECLARE @lastName nvarchar(20)
DECLARE @firstName nvarchar(20)
DECLARE @secondName nvarchar(20)
DECLARE @birthDate datetime
DECLARE @sex int
DECLARE @seriesNumber nvarchar(10)
DECLARE @docdate datetime
DECLARE @AfsMessage nvarchar(1000)
SELECT
@lastName=a.ANKETA_FAM,
@firstName=a.ANKETA_IM,
@secondName=a.ANKETA_OT,
@birthDate=a.ANKETA_BIRTH_DATE,
@sex=CASE a.ANKETA_SEX WHEN N'Ж' THEN 0 ELSE 1 END,
@seriesNumber=ISNULL(SUBSTRING(ANKETA_SERIESPASSPORT,1,2)+SUBSTRING(ANKETA_SERIESPASSPORT,4,2)+ANKETA_NUMBERPASSPORT,''),
@docdate=ISNULL(ANKETA_DATEPASSPORT,'19000101')
FROM port.Ankets a
WHERE ANKETA_ID=4362;
EXECUTE [MainDWH].[dbo].[sp_AfsSOAP_Request]
@BP_ID
,@lastName
,@firstName
,@secondName
,@birthDate
,@sex
,@seriesNumber
,@docdate
,@AfsMessage OUTPUT
SELECT BP_ID  AfsMessage
[/vba]
Благодарю за внимание

Автор - Elhust
Дата добавления - 07.02.2017 в 15:49
Elhust Дата: Вторник, 07.02.2017, 15:52 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 184
Репутация: -1 ±
Замечаний: 0% ±

Excel 2010
сюда место значения должна встать переменная которая ссылается на (лист1)
[vba]
Код

WHERE ANKETA_ID=4362;
[/vba]
Хранимая процедура
[vba]
Код

EXECUTE [MainDWH].[dbo].[sp_AfsSOAP_Request]
@BP_ID
,@lastName
,@firstName
,@secondName
,@birthDate
,@sex
,@seriesNumber
,@docdate
,@AfsMessage OUTPUT
[/vba]
тут вывод выходит что необходимо вставить в ячейку на против ..
[vba]
Код
SELECT BP_ID  AfsMessage
[/vba]


Каждый сам выбирает правила игры
 
Ответить
Сообщениесюда место значения должна встать переменная которая ссылается на (лист1)
[vba]
Код

WHERE ANKETA_ID=4362;
[/vba]
Хранимая процедура
[vba]
Код

EXECUTE [MainDWH].[dbo].[sp_AfsSOAP_Request]
@BP_ID
,@lastName
,@firstName
,@secondName
,@birthDate
,@sex
,@seriesNumber
,@docdate
,@AfsMessage OUTPUT
[/vba]
тут вывод выходит что необходимо вставить в ячейку на против ..
[vba]
Код
SELECT BP_ID  AfsMessage
[/vba]

Автор - Elhust
Дата добавления - 07.02.2017 в 15:52
Elhust Дата: Вторник, 07.02.2017, 17:43 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 184
Репутация: -1 ±
Замечаний: 0% ±

Excel 2010
:'(


Каждый сам выбирает правила игры

Сообщение отредактировал Elhust - Среда, 08.02.2017, 07:04
 
Ответить
Сообщение:'(

Автор - Elhust
Дата добавления - 07.02.2017 в 17:43
Elhust Дата: Среда, 08.02.2017, 08:37 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 184
Репутация: -1 ±
Замечаний: 0% ±

Excel 2010
Или лучше сделать вложенную хранимую процедуру?? :( только я не знаю как вложенные хранимки писать (


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

Автор - Elhust
Дата добавления - 08.02.2017 в 08:37
anvg Дата: Среда, 08.02.2017, 09:13 | Сообщение № 5
Группа: Друзья
Ранг: Ветеран
Сообщений: 581
Репутация: 271 ±
Замечаний: 0% ±

2016, 365
Доброе время суток.
А с чего вы решили, что приведённый вами код - хранимая процедура? Обычный код, собираемый из строк, вот и подставляейте значение ячейки вместо 4362.
И по Set rs = Conn.Execute "Здесь текст SQL команды, возвращающей в конце по SELECT @AfsMessage"
значение ответа реальной хранимой процедуры sp_AfsSOAP_Request.
Каша у вас в коде, к сожалению. Посмотрите описание и примеры работы.


Сообщение отредактировал anvg - Среда, 08.02.2017, 09:18
 
Ответить
СообщениеДоброе время суток.
А с чего вы решили, что приведённый вами код - хранимая процедура? Обычный код, собираемый из строк, вот и подставляейте значение ячейки вместо 4362.
И по Set rs = Conn.Execute "Здесь текст SQL команды, возвращающей в конце по SELECT @AfsMessage"
значение ответа реальной хранимой процедуры sp_AfsSOAP_Request.
Каша у вас в коде, к сожалению. Посмотрите описание и примеры работы.

Автор - anvg
Дата добавления - 08.02.2017 в 09:13
Elhust Дата: Среда, 08.02.2017, 09:56 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 184
Репутация: -1 ±
Замечаний: 0% ±

Excel 2010
anvg,
Доброго, я согласен что каша просто я в SQL ещё больший профан ... , а на счёт запроса который выше описан , рекордсет не может читать запрос если в нём и запрос и хранимая процедура , то есть как я понял только либо простым запросом либо одной хранимкой
.. буду думать


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

Автор - Elhust
Дата добавления - 08.02.2017 в 09:56
anvg Дата: Среда, 08.02.2017, 10:47 | Сообщение № 7
Группа: Друзья
Ранг: Ветеран
Сообщений: 581
Репутация: 271 ±
Замечаний: 0% ±

2016, 365
Упрощённо. В базе есть хранимая процедура, созданная как
[vba]
Код
Create Procedure dbo.MyProcedure @Where int, @Result nvarchar(max) Output
As
Begin
-- здесь ваш код ХП
End;
[/vba]

В VBA при явно подключенной библиотеке ADODB код
[vba]
Код
Public Sub test()
    Dim rs As New ADODB.Recordset, conn As New ADODB.Connection, pCom As ADODB.Command
    Dim whereId As Long
    conn.Open "Connection string settings"
    
    'Получаем значение параметра для ХП запросом к таблице в базе
    rs.Open "Select UserId From MyDb.dbo.Users Where UserName = '" & ActiveSheet.Range("A2").Value & "';", conn
    whereId = rs(0).Value
    rs.Close
    
    'настраиваем вызов ХП и её параметры
    Set pCom = New ADODB.Command
    Set pCom.ActiveConnection = conn
    pCom.Parameters.Append pCom.CreateParameter("@Where", adInteger, adParamInput, Value:=whereId)
    pCom.Parameters.Append pCom.CreateParameter("@Result", adVarWChar, adParamOutput, 65000)
    pCom.CommandType = adCmdStoredProc
    pCom.CommandText = "MyDb.dbo.MyProcedure"
    'выполняем ХП
    pCom.Execute Option:=adExecuteNoRecords
    
    Debug.Print "Результат выполнения " & pCom.Parameters("@Result").Value
    Set pCom = Nothing
    conn.Close
    Set conn = Nothing
End Sub
[/vba]
 
Ответить
СообщениеУпрощённо. В базе есть хранимая процедура, созданная как
[vba]
Код
Create Procedure dbo.MyProcedure @Where int, @Result nvarchar(max) Output
As
Begin
-- здесь ваш код ХП
End;
[/vba]

В VBA при явно подключенной библиотеке ADODB код
[vba]
Код
Public Sub test()
    Dim rs As New ADODB.Recordset, conn As New ADODB.Connection, pCom As ADODB.Command
    Dim whereId As Long
    conn.Open "Connection string settings"
    
    'Получаем значение параметра для ХП запросом к таблице в базе
    rs.Open "Select UserId From MyDb.dbo.Users Where UserName = '" & ActiveSheet.Range("A2").Value & "';", conn
    whereId = rs(0).Value
    rs.Close
    
    'настраиваем вызов ХП и её параметры
    Set pCom = New ADODB.Command
    Set pCom.ActiveConnection = conn
    pCom.Parameters.Append pCom.CreateParameter("@Where", adInteger, adParamInput, Value:=whereId)
    pCom.Parameters.Append pCom.CreateParameter("@Result", adVarWChar, adParamOutput, 65000)
    pCom.CommandType = adCmdStoredProc
    pCom.CommandText = "MyDb.dbo.MyProcedure"
    'выполняем ХП
    pCom.Execute Option:=adExecuteNoRecords
    
    Debug.Print "Результат выполнения " & pCom.Parameters("@Result").Value
    Set pCom = Nothing
    conn.Close
    Set conn = Nothing
End Sub
[/vba]

Автор - anvg
Дата добавления - 08.02.2017 в 10:47
Elhust Дата: Среда, 08.02.2017, 14:10 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 184
Репутация: -1 ±
Замечаний: 0% ±

Excel 2010
anvg,Большая Благодарность, разобрался и всё получилось, теперь думаю как бы засунуть в цикл , точнее засунул но на мой взгляд кастыльно получилось долго обрабатывает (
[vba]
Код

Dim Conn
Dim Ary()
'------------------------------------------------------------------------------------------------------------------
Sub test_conn()

'Dim Prm       As ADODB.Parameter
Dim cmd       As ADODB.Command
Dim Conn      As ADODB.Connection
Dim rs        As ADODB.Recordset
Dim lLastrow, i As Long
Dim iCell       As Range
Dim whereID     As Long
'------------------------------------------------------------------------------------------------------------------
Application.ScreenUpdating = False
Range("G2:G65536").Clear
lLastrow = Cells(Rows.Count, 2).End(xlUp).Row
i = 1
'------------------------------------------------------------------------------------------------------------------
Set Conn = New ADODB.Connection
Conn.ConnectionString = "driver={SQL Server};server=177.17.3.176;uid=           ;pwd=         ;database=MainDWH"
Conn.Open
'------------------------------------------------------------------------------------------------------------------

With ThisWorkbook.Worksheets(1)

For Each iCell In .Range("B2", Cells(lLastrow, 2))
whereID = iCell.Value
Set cmd = New ADODB.Command
     With cmd
          .ActiveConnection = Conn
          .Parameters.Append cmd.CreateParameter("@ID", adInteger, adParamInput, Value:=whereID)
          .CommandType = adCmdStoredProc
          .CommandText = "port.AFSMESS"
     End With
     Set rs = cmd.Execute()
     i = i + 1
     Cells(i, 7).CopyFromRecordset rs
        Set cmd = Nothing
        If iCell = "" Then
        Cells(i, 7).Value = ""
        End If
Next
End With
Application.ScreenUpdating = True
'------------------------------------------------------------------------------------------------------------------
'Conn.Close
'rs.Close
'Set rs = Nothing
'Set con = Nothing
End Sub
[/vba]


Каждый сам выбирает правила игры
 
Ответить
Сообщениеanvg,Большая Благодарность, разобрался и всё получилось, теперь думаю как бы засунуть в цикл , точнее засунул но на мой взгляд кастыльно получилось долго обрабатывает (
[vba]
Код

Dim Conn
Dim Ary()
'------------------------------------------------------------------------------------------------------------------
Sub test_conn()

'Dim Prm       As ADODB.Parameter
Dim cmd       As ADODB.Command
Dim Conn      As ADODB.Connection
Dim rs        As ADODB.Recordset
Dim lLastrow, i As Long
Dim iCell       As Range
Dim whereID     As Long
'------------------------------------------------------------------------------------------------------------------
Application.ScreenUpdating = False
Range("G2:G65536").Clear
lLastrow = Cells(Rows.Count, 2).End(xlUp).Row
i = 1
'------------------------------------------------------------------------------------------------------------------
Set Conn = New ADODB.Connection
Conn.ConnectionString = "driver={SQL Server};server=177.17.3.176;uid=           ;pwd=         ;database=MainDWH"
Conn.Open
'------------------------------------------------------------------------------------------------------------------

With ThisWorkbook.Worksheets(1)

For Each iCell In .Range("B2", Cells(lLastrow, 2))
whereID = iCell.Value
Set cmd = New ADODB.Command
     With cmd
          .ActiveConnection = Conn
          .Parameters.Append cmd.CreateParameter("@ID", adInteger, adParamInput, Value:=whereID)
          .CommandType = adCmdStoredProc
          .CommandText = "port.AFSMESS"
     End With
     Set rs = cmd.Execute()
     i = i + 1
     Cells(i, 7).CopyFromRecordset rs
        Set cmd = Nothing
        If iCell = "" Then
        Cells(i, 7).Value = ""
        End If
Next
End With
Application.ScreenUpdating = True
'------------------------------------------------------------------------------------------------------------------
'Conn.Close
'rs.Close
'Set rs = Nothing
'Set con = Nothing
End Sub
[/vba]

Автор - Elhust
Дата добавления - 08.02.2017 в 14:10
anvg Дата: Среда, 08.02.2017, 15:39 | Сообщение № 9
Группа: Друзья
Ранг: Ветеран
Сообщений: 581
Репутация: 271 ±
Замечаний: 0% ±

2016, 365
долго обрабатывает
А чего вы собственно хотите? Нельзя больше одного значения засунуть в параметр ХП.
Если у вас извлекаемые данные ХП обрабатывает из таблиц, то проще и быстрее, посмотреть, что за запрос там храниться, сделать временную таблицу со стороны VBA, залить туда данные по ID и выгрузить одним запросом с join по ID временной таблицы.
 
Ответить
Сообщение
долго обрабатывает
А чего вы собственно хотите? Нельзя больше одного значения засунуть в параметр ХП.
Если у вас извлекаемые данные ХП обрабатывает из таблиц, то проще и быстрее, посмотреть, что за запрос там храниться, сделать временную таблицу со стороны VBA, залить туда данные по ID и выгрузить одним запросом с join по ID временной таблицы.

Автор - anvg
Дата добавления - 08.02.2017 в 15:39
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Хранимая процедура в SQL запросе (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!