In this blog-post, I am going to create a stored procedure that accepts a parameter with SQL Azure.
So I have a table that lists firstname, lastname of the customer along with location details. Here’s what it looks like:
And just note that I running the queries on SQL Azure management portal and not on SSMS. You can do so in SSMS too. But for this blog-post – I used the SQL Azure management portal.
So now we want to write a Stored Procedure which gives this information when it is called; Not only that, we want that stored procedure to return information about customers from the country that’s specified by the person calling the procedure. And such a stored procedure would empower the person who is going to call the stored procedure to filter the customer list based on the country of their choice. So let’s go ahead and write the code:
create procedure saleslt.spcustomerlocation @countryname nvarchar(50)
select firstname,lastname,city,stateprovince,countryregion from customertable
where countryregion = @countryname
Now once we have created the stored procedure, It can be called by running the following code:
exec saleslt.spcustomerlocation 'United States'
And it would give us list of customers in United States like shown below:
So that’s about it for this post.
Republished from Paras Doshi's Blog [61 clicks].
Read the original version here [1 clicks].