CODEWITHSUNDEEP
sqlsql-server
Deepak Jha
Deepak Jha

Reputation: 379

How to call web API in MS SQL stored procedure

I have a table their are many records with email address column and i am using web api to verify email id is valid or not. Is it possible to call via MS SQL stored procedure

Upvotes: 1

Views: 7445

Answers (2)

Gavin Campbell
Gavin Campbell

Reputation: 836

There are almost no circumstances in which this kind of thing is a good idea. Wouldn't it be better to validate your email addresses before they get into the table?

As far as bad ideas go, a CLR stored procedure would be one option, as would the sp_OACreate stuff pasted from another stackoverflow question, but surely this kind of thing is better done outside the database?

Why not store the "raw" email addresses in a "staging" environment somewhere - possibly another table in your database, then create the validation processes elsewhere, maybe in a Powershell script, a .Net program, or some other kind of environment where calling a web service is trivially easy? This way you can figure out whether an address is valid as you are writing it, and then take appropriate action (don't write it? set some kind of flag?).

Upvotes: 3

vicky
vicky

Reputation: 1580

Try the following sp

    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);


    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://your-api-address', --Your Web Service Url (invoked)
                 'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

    Select @ResponseText

    Exec sp_OADestroy @Object

Upvotes: 3

Related Questions