問題描述
BizTalk 數據庫查找 functoid 固定條件 (BizTalk Database Lookup functoid fixed condition)
Is there a way to further restrict the lookup performed by a database lookup functoid to include another column?
I have a table containing four columns.
Id (identity not important for this) MapId int Ident1 varchar Ident2 varchar
I'm trying to get Ident2 for a match on Ident1 but wish it to only lookup where MapId = 1.
The functoid only allows the four inputs any ideas?
UPDATE
It appears there is a technique if you are interested in searching across columns that are string data types. For those interested I found this out here...
Google Books: BizTalk 2006 Recipes
Seeing as I wish to restrict on a numberic column this doesn't work for me. If anyone has any ideas I'd appreciate it. Otherwwise I may need to think about my MapId column becoming a string.
‑‑‑‑‑
參考解法
方法 1:
I changed the MapId to MapCode of type char(3) and used the technique described in the book I linked to in the update to the original question.
The only issue I faced was that my column collations where not in line so I was getting an error from the SQL when they where concatenated in the statement generated by the map.
exec sp_executesql N'SELECT * FROM IdentMap WHERE MapCode+Ident1= @P1',N'@P1 nvarchar(17)',N'<MapCode><Ident2>'
Sniffed this using the SQL Profiler