How to call a stored procedure in a View

Question

Post reply

How to call a stored procedure in a View

Atik

SSC Veteran

Points: 288
More actions
January 20, 2006 at 2:59 am

#364769

i made a stored procedure list data for a given tabelname
I would like to use this procedure in a view. There i have problem how can i call this procedure in a view ? is that so difficult. ?

CREATE PROCEDURE ShowCfTable @tabel varchar(100)
AS
DECLARE @sSQL NVARCHAR(255)
SET @sSQL = 'SELECT * FROM ' + @tabel
EXEC sp_executesql @sSQL
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

It's a bit more than difficult, it's impossible. Can't be done.

Views are built on <select statement> - you cannot 'select procedure', so it won't work.

Please have a look in BOL at 'CREATE VIEW' for more information on views.

Often views are used because you want some isolation of security.

Even procedures may be used for that purpose.

Though, if the procedure you show is an actual procedure ment to be placed into production,

it will not give you any securitylayer at all, since it contains dynamic SQL.

As written, it is not something that I'd recommend placing into any production environment.

'Going dynamic' should be considered as a last resort when all other options fail, and even then

it may not be the best choice..

/Kenneth

Chad Michael SSC-Addicted Points: 427 More actions · Answer 2

It's not technically impossible but is probably not recommended in most situations. Here is a way to do it (without the table parameter actually working). There is also a way to get the parm info to the proc but that is quite a bit more challenging. If you really want to know how to do that. I could probably get you setup.

CREATE PROCEDURE ShowCfTable @tabel varchar(100) = 'sysprocesses'

AS

DECLARE @sSQL NVARCHAR(255)

SET @sSQL = 'SELECT * FROM ' + @tabel

EXEC sp_executesql @sSQL

GO

CREATE VIEW vw_sysprocesses AS

SELECT * FROM OPENQUERY([LOOPBACK], 'SET NOCOUNT ON;EXEC master.dbo.ShowCfTable;')

GO

SELECT * FROM vw_sysprocesses

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

There are table-valued functions that combine advantages of SPs and views. They accept parameters and you can select from them.

Regards,Yelena Varsha

Atik SSC Veteran Points: 288 More actions · Answer 4

Thnk you very much for your helps

I think i should think opposite way. Namely call views from procedures. Problem is here to call a tabel name dynamiccally.

So i ll try to structure sql fri\om the procedure and save it as view.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Well, if you think that the solution to your (unknown?) problem is to use dynamic SQL, I strongly recommend reading The curse and blessings of dynamic SQL first.

It's crucial that you understand the consequences of dynamic SQL - and they are many.

/Kenneth