Welcome to weblogs.com.pk Sign in | Join | Help

Can we use stored procedures in View?

Well I have not gone through all the options yet; but I was able to did something that solved the problem at hand. The database at hand was exposed only through stored procedures; but one developer wanted to have a view; being new to Perl on Unix; he hasn’t yet explored how to execute stored procedures. He asked me to setup few views for him so that he can treat them as a tables. (Mostly SELECT operations). I didnt’ wanted to expose any base thing; and wanted to setup wrapper over existing exposed objects (stored procs). It came with a surprise that you cannot use stored procedures in view definition. Next I tried creating user defined functions that return a table and in view selecting from this returned table; but I was stuck with problem that we can’t populate table data types with stored procedure. After googling for a while; I decided to follow a suggestion to use OPENQUERY that needs linked servers; and for that we defined a LOOPBACK linked server. It’s working pretty fine; but I am not happy with what I have done.

It appears that with SQL 2005; you can populate table data type using stored procedure. I will try it out later. Unfortunately my assignment was with SQL 2000.

Published Tuesday, March 21, 2006 4:27 PM by khurram
Filed under: ,


No Comments

New Comments to this post are disabled