BizTalk mapping & SQL stored procedures - notification messages

hi all,

i'm after advice on how handle multiple small transactions , mapped sql stored procedure carry out insert/update functionality.

i receive number of small notification messages web services need map across stored procedure within ms sql. challenge at present stored procedures expects the input values variables... example:

(@prefix_honorific varchar(max)= null,) 

however, issue when i'm not receiving variables @ same time. the stored procedure updates fields whether source message had value or not.

has been able to:

• take in multiple small notification messages
• map them canonical schema
• map down a stored procedure where only small datagrams received within each notification.

all notifications always have a primary key/guid in message know exactly message update via stored procedure. i've tried multiple approaches such mapping every field across if message has null value database updated null rather left current value. did @ returning field in map had values stored procedure expects the join if there no data , insert null value.

any guidance on working notifications , ms sql insert/updated stored procedures welcome.

kind regards,

steven.

---

if answers question please mark accordingly. if post helpful, please vote helpful clicking upward arrow mark next reply.

if fields updated inconsistent, handle in stored procedure.

meaning pass sp record id, field update , value, case or dynamic sql update or insert.

BizTalk Server  >  BizTalk Server General