Execute a SQL stored proc from a SQL view

views:

651

answers:

+3 Q:

Execute a SQL stored proc from a SQL view

I am finding that calling a stored proc in Excel is not as easy as it should be, but calling a view, or a direct table is very easy. So, how can I create a view that will call a stored proc with no params?

I know I won't be able to pass any values into the view, and I don't need or want to, Just want to wrap a stored proc in a view.

something like select exec MyStoredProc() would be great.

You should be able to put a trigger on a dummy table, and call the proc inside the trigger.

This is definitely a hack, and you would want to really lock down permissions on the table and proc.

Guy Starbuck 2008-12-08 16:12:45

But could you then return the results of the trigger (assuming that is what Russ wants)?

wcm 2008-12-08 16:16:35

Here's one scenario where I would need to use a view.In LINQ to Entities, the process to map a result set from a stored procedure to a custom entity requires that you manually update the .edmx file, which is not good because the changes get lost each time you update the model from the database.So, it would be nice to map the result set to a view.

AbeP 2009-11-05 19:09:40

+2 A:

you can with a bit of a hack with OPENROWSET. look here.

Mladen Prajdic 2008-12-08 16:12:52

I found the openrowset hack just after I put this question in, but I was hopping for something that SQL did't have locked down as a security risk.I'm going to mark this as the answer since it seems to be the only possible solution, but I don't know if I am willing to impliment it.

Russ 2008-12-08 16:19:12

well then the real question is if you really have to do what you want to. :) you could implement a table returning UDF and call that from your sproc or from your code. you don't need a view at all.

Mladen Prajdic 2008-12-08 16:22:22

+4 A:

iirc (I don't have a copy to hand) if you happen to be in T-SQL it should be possible to select * from a user defined table function (which are for most intents and purposes identical to sprocs) which returns a table variable.

annakata 2008-12-08 16:18:58

This is a great solution and avoids the security issues that Russ is worried about.

wcm 2008-12-08 16:23:22

Typicaly I would go for this option, but the stored proc in question is the top of several nested stored proc's and converting them all to functions isn't a realistic option in this specific case. In this case, I have several nested stored procs, that perform several xpath queries.

Russ 2008-12-08 16:37:48

yeah here's hoping he notices :)

annakata 2008-12-08 16:37:49

ansaurus

tags:

views:

answers:

Execute a SQL stored proc from a SQL view

related questions