DECLARE @lastName nvarchar(20)=N'ЛОКАЦКАЯ'
DECLARE @firstName nvarchar(20) =N'ТАТЬЯНА'
DECLARE @secondName nvarchar(20)=N'НИКОЛАЕВНА'
DECLARE @sex nvarchar(20)=N'Ж'
DECLARE @birthDate date
set @birthDate ='18-11-1950'
------------------------------------------------------------------------------------------
DECLARE @AfsMessage nvarchar(1000)
------------------------------------------------------------------------------------------
DECLARE @TipPass21 nvarchar(10)=N'21'
DECLARE @seriesNumber21 nvarchar(10) =N'6502938694'
DECLARE @docdate21 date=N'15.05.2002'
DECLARE @whopass21 nvarchar(60)=N'ВЕРХНЕПЫШМИНСКИМ ГОВД СВЕРДЛОВСКОЙ ОБЛАСТИ'
DECLARE @BPID nvarchar(20)
DECLARE @BPIDID nvarchar(20)
DECLARE @BP_ID nvarchar(20)
DECLARE @OTDNUM nvarchar(20)
DECLARE @date date
DECLARE @oldFIO nvarchar(20)
DECLARE @birthPlace nvarchar(60)
DECLARE @worktel nvarchar(20)
DECLARE @mobiltel nvarchar(20)
DECLARE @contacttel nvarchar(20)
DECLARE @TipPass22 nvarchar(10)
DECLARE @seriesNumber22 nvarchar(10)
DECLARE @docdate22 varchar(20)
DECLARE @whopass22 nvarchar(60)
DECLARE @TipPass7 nvarchar(10)
DECLARE @seriesNumber7 nvarchar(10)
DECLARE @docdate7 varchar(20)
DECLARE @whopass7 nvarchar(60)
DECLARE @TipPass31 nvarchar(10)
DECLARE @seriesNumber31 nvarchar(10)
DECLARE @docdate31 varchar(20)
DECLARE @whopass31 nvarchar(60)
DECLARE @TipPass4 nvarchar(10)
DECLARE @seriesNumber4 nvarchar(10)
DECLARE @docdate4 varchar(20)
DECLARE @TipPass32 nvarchar(10)
DECLARE @seriesNumber32 nvarchar(10)
DECLARE @TipPass81 nvarchar(10)
DECLARE @seriesNumber81 nvarchar(10)
SELECT
@BPIDID=ANKETA_ID,
@BPID=ANKETA_ID,
@BP_ID=BP_ID,
@OTDNUM=CASE a.ANKETA_OTDNUM WHEN 90 THEN '2' ELSE '1' END,
@lastName=ANKETA_FAM,
@firstName=ANKETA_IM,
@secondName=ANKETA_OT,
@oldFIO=UBRR_ANKETA_OLDFIO,
@birthDate=ANKETA_BIRTH_DATE,
@birthPlace=ANKETA_BIRTHPLACE,
@sex=CASE ANKETA_SEX WHEN N'Ж' THEN 0 ELSE 1 END,
@date=CONVERT(nvarchar(10),GETDATE(),104),
------------------------------------------------------------------------------------------
@TipPass4='4',
@seriesNumber4=ISNULL(SUBSTRING(ANKETA_SERIESOFFICER,1,2)+SUBSTRING(ANKETA_SERIESOFFICER,4,2)+ANKETA_NUMBEROFFICER,''),
@docdate4=ANKETA_DATEOFFICER,
------------------------------------------------------------------------------------------
@TipPass7='7',
@seriesNumber7=ISNULL(SUBSTRING(ANKETA_SERIESCARD,1,2)+SUBSTRING(ANKETA_SERIESCARD,4,2)+ANKETA_NUMBERCARD,''),
@docdate7=ANKETA_DATECARD,
@whopass7=ANKETA_WHOCARD,
@TipPass31='31',
@seriesNumber31=ISNULL(SUBSTRING(ANKETA_SERIESDRIVER,1,2)+SUBSTRING(ANKETA_SERIESDRIVER,4,2)+ANKETA_NUMBERDRIVER,''),
@docdate31=ANKETA_DATEDRIVER,
@whopass31=ANKETA_WHODRIVER,
@TipPass22='22',
@seriesNumber22=ANKETA_NUMBERZAGR,
@docdate22=ANKETA_DATEZAGR,
@whopass22=ANKETA_WHOZAGR,
------------------------------------------------------------------------------------------
@TipPass21='21',
@seriesNumber21=ISNULL(SUBSTRING(ANKETA_SERIESPASSPORT,1,2)+SUBSTRING(ANKETA_SERIESPASSPORT,4,2)+ANKETA_NUMBERPASSPORT,''),
@docdate21=ANKETA_DATEPASSPORT,
@whopass21=ANKETA_WHOPASSPORT,
------------------------------------------------------------------------------------------
@TipPass32='32',
@seriesNumber32=ANKETA_NUMBERSTATEINC,
------------------------------------------------------------------------------------------
@TipPass81='81',
@seriesNumber81=ANKETA_INN,

FROM port.Ankets a

DECLARE  @URL  SysName
,@Body xml

-- Создание запроса
;WITH XMLNAMESPACES (DEFAULT 'http://mbtc.ru/afs/')

SELECT  @URL    = 'http://sas-afs.lan.ubrr.ru:8080/afs/ws/ubrrService'
    ,@Body  = (
SELECT  
'LoyaltyWS' as [auth/login],
'LoyaltyWS1!' as [auth/password],
'match' as [action],
'STOP_LIST_1' as [ruleSetId],
@BPID as [Application/id],
'1' as [Application/version],
@date as [Application/date],
@OTDNUM as [Application/app/bank],
@BPIDID as [Application/app/formId],
@BP_ID as [Application/app/applicant/id],
@lastName as [Application/app/applicant/person/lastName],
@firstName as [Application/app/applicant/person/firstName],
@secondName as [Application/app/applicant/person/secondName],
@oldFIO as [Application/app/applicant/person/prevFullName],
@birthDate as [Application/app/applicant/person/birthDate],
@birthPlace as [Application/app/applicant/person/birthPlace],
@sex as [Application/app/applicant/person/sex],
----------------------------------------DOC--------------------------------------------------------------
(SELECT f.*
FROM
(
values
(@TipPass21,@seriesNumber21,@docdate21,@whopass21),
(@TipPass22,@seriesNumber22,@docdate22,@whopass22),
(@TipPass7,@seriesNumber7,@docdate7,@whopass7),
(@TipPass31,@seriesNumber31,@docdate31,@whopass31),
(@TipPass4,@seriesNumber4,@docdate4,''),
(@TipPass32,@seriesNumber32,'',''),
(@TipPass81,@seriesNumber81,'','')
)
f([type], seriesNumber, date, issued)for xml path('doc'), type)
as [Application/app/applicant],
---------------------------------------ADDRES------------------------------------------------------------
(SELECT a.*
FROM
(
values
(1, @regionReg, @indexReg,@regarea, @cityReg,@regplace, @streetReg, @houseReg, @corpReg, @flatReg),
(2, @regionLife, @indexLife,@lifearea, @cityLife,@lifeplace, @streetLife, @houseLife, @corpLife, @flatLife)
)
a([type], region, postalCode,area, city,place, street, house, corp, flat)for xml path('address'), type)
as [Application/app/applicant/addresses],
@residenceChange as [Application/app/applicant/addresses/residenceChange],
@buildingType as [Application/app/applicant/addresses/buildingType],
@owner as [Application/app/applicant/addresses/owner],
----------------------------------------PHONE------------------------------------------------------------
(SELECT s.*
FROM
(
values
(1,@housetel ),
(2,@worktel ),
(3,@mobiltel)
)
s([type],number)for xml path('phone'), type)
as [Application/app/applicant]
---------------------------------------------------------------------------------------------------------
FOR    XML Path('afsRequest'),Type)
EXEC    dbo.sp_SOAPMethodCall
   @URL
  ,NULL
  ,@Body  OUT
;
SET @AfsMessage=ISNULL(@Body.value('declare namespace p1="http://mbtc.ru/afs";
(/p1:afsResponse/p1:matchResult/p1:match/p1:description)[1]','nvarchar(1000)'),N'По клиенту ничего не найдено.');
with xmlnamespaces(default 'http://mbtc.ru/afs')
select
@AfsMessage=ISNULL(stuff(t.n.query('for $d in (match/description) return concat("; ", $d/text()[1])').value('.', 'nvarchar(max)'), 1, 2, ''),N'По клиенту ничего не найдено.')
from
@Body.nodes('/afsResponse/matchResult') t(n);
SELECT @AfsMessage

Public Sub CommandTextWithParameters()
    Const strConn = "Driver={SQL Server Native Client 11.0};Server= (localdb)\MSSQLLocalDB=database_name;Trusted_Connection=yes;"
    Dim pConn As New ADODB.Connection
    Dim pCom As New ADODB.Command
    Dim pParam As ADODB.Parameter
    Dim pRSet As ADODB.Recordset
    
    pConn.CursorLocation = adUseClient
    pConn.Open strConn
    Set pCom.ActiveConnection = pConn
    pCom.CommandType = adCmdText
    pCom.CommandText = "Declare @myparam As smallint = ?; Select * From dbo.Entity Where [Type] = @myparam;"
    Set pParam = pCom.CreateParameter("@order1", adSmallInt, adParamInput)
    pCom.Parameters.Append pParam
    
    pCom.Parameters("@order1").Value = 181
    
    Set pRSet = pCom.Execute
    ‘………
End Sub