Intercept Result

Hi,

I am trying to write a system (Stored Proc, View, CLR Proc, ?), where if you query a specific table and it returns data from only 1 column for the submitted SELECT statement then substitute data obtained from an external web service.

Does anyone have any suggestions about how best to implement this? I have searched through the BOL but nothing directly deals with what I want to do.

Thanks,

Blair

P.S. I am using SQL SERVER 2005 ENT Edition with everything available.

Hi,

I'm not very clear with your question.

For substituting a result, you can use CASE within your SELECT statement.

Assuming Table1 table with Col1 as column, and a scalar valued function CLRFunction() which will call a web service,

SELECT CASE Col1
WHEN 'abc' THEN CLRFunction(1)
WHEN 'def' THEN CLRFunction(2)
END 'Col1'
FROM Table1

Here 'abc' and 'def' are the values stored in Col1 of Table1

Hope this answers for your query, else please explain your requirement with some more detail.

Regards

Babu

|||

Thanks that is exactly what I was looking for. I now know what to do.

blair

|||

Hello...

I think calling a webservie inside a SP is not a very good behavior. You should write your SPs so that they finish as fast as possible. If you try to access an outside resource you never know how long it will take(and a webservice is even worse). You should consider moving this logic into your application. Also you need to access external resources in that function, so you have to declare it as "unsafe" which will also have some implications on security...

Also it will be more complicated to make your app responsive while your code is waiting on the SQL Server. Calling a webservice async is very easy... Calling SQL is a little more work (but still no big deal)