Manual database testing using stored procedures

I am working in a project where we use web services and only 10% of the testing include front end testing. if we say as a non technical tester then there is a request and a response. The request is in form of XML having sets of data and response is also in form of XML. so here in such cases we need to test the internal behaviors of the application. we need to verify the data inserted to the appropriate destination in database. We need to test the functions working fine or not.
To test such applications a tester should be smart enough in identifying the scenarios which covers the testing of every functionality.

Now one and major part of this testing is to test in back end. the data inserted from from end is properly gone to the correct place. we do it manually. for this we need to know:
1- Detail knowledge of application.
2- Good knowledge of database used(overview of database used, good in query, datamodel, reltionship used).
3- Sound skills to identify the scenarios which covers all the functionality.(some time we need to identify the case which really come into action in production only. for this testers some times need to change from code to execute such scenarios.)

so doing verification of data we generally use some query to fetch data from database. We have sets of statement here.
I would always prefer to make some stored procedure which realy help in excecuting the queruies.

I wil give an simple example here which may help you that how to used stored procedure while testing with database.

I am taking an example here which allow a user to to register with a application by sending a request in form of XML. after registerting this the request will give you a key and using that key you can see the result which the application provide.
The flow of the request will be like:
1- The set of user data inserted
2- A key provided to user as in response
3- User again send a request using this key
4-user information recieved in form of XML.

so here the testing will include
1- wheather user data inserted in proper place in database.
2- The information recieved by user is correct or not.
for this we can make some quesries to fetch the data(it can be manually checked but that will take much time and will not be compatible.)

here we can also make some strored procesure(hope you undersatand basic of stored procesure how to create , how to execute).

suppose I want the user data from user table corresponding to a user id. for this you can write a procesure in a way...

1- Open a new query prompt
2- Write..
create procedure userdetail
{
@user_id numeric(10)
}
as
select * from USER
where USER_ID= @user_id

3- Execute this query
4- Now you can see this procesure in you list.
5- now to execute this you can use

exec userdetail '2001'

or simple go to the listing of procesures and execute there by clicking the option in right click.
you will find a window whete to pass user_id.


this is a simple procedure i used here. in same way we can test by passing inputs .