Generic function to return whole table

Question

Generic function to return whole table

RS-210331

SSC Rookie

Points: 33
More actions
September 8, 2005 at 5:08 pm

#102780

Hi,
I am trying to develop a generic function which will accept table name and return entire records as 'select * from tablename'. In this case, I may not able to use the In-line table function or multi-statement table functions. Since the In-line table can have a single SQL statement and multi-statement table function have to define (declare) the table structure which can’t be. Is there any other option!!!

Viewing 7 posts - 1 through 7 (of 7 total)

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

philcart SSC-Forever Points: 47797 More actions · Answer 1

Care to explain your requirement for this and why you need a function to do it?

CREATE procedure dbo.usp_select
    @tbl sysname
AS
BEGIN
    DECLARE @SQL nvarchar(100)
    SET @SQL = 'SELECT * FROM ' + @tbl
    EXEC sp_executesql @sql
END
GO
EXEC usp_select 'dbo.sysobjects'
GO
DROP PROCEDURE dbo.usp_select
GO

--------------------
Colt 45 - the original point and click interface

RS-210331 SSC Rookie Points: 33 More actions · Answer 2

Thanks for your reply.

It is very much doable with stored proc. But I am planning to call in a view and view will be given to the users. Users will be accessing view thru ODBC link. While accessing thru ODBC, I would like to enforce some security control (security control is maintained in user tables to support business process).

philcart SSC-Forever Points: 47797 More actions · Answer 3

"security control is maintained in user tables"

Are you talking about SQL Security here, or is this application security?

Anything I can think would out of necessity require the use of dynamic SQL, like I've done in the procedure. Once you involve dynamic SQL you will need to specifically grant permissions on all the table regardless of whether you are accessing them via a view or not.

--------------------
Colt 45 - the original point and click interface

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ya don't do it. Make an sp for each required select in the application and you'll be much better off.

RS-210331 SSC Rookie Points: 33 More actions · Answer 5

Are you talking about SQL Security here, or is this application security?

Answer to your question. Yes, it is defined in the application. Enforcing the same security level even if the user try to access thru ODBC link. In a simple statement, the kind of security level they have in the application, the same level of security I would like to enforce by giving views.

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

Again, if you use dynamic SQL, you must grant permissions on the base tables. It is not enough with permissions on just the view or procedure. In effect, the view or proc will then not serve as a layer of security.

If the users know the login and password the app is using, you have no means at all to prevent anyone logging in with another tool (eg QA) and have access to the basetables, completely avoiding your view/proc should they want.

Apart from the security aspect, you must also consider the risk of SQL injection, another 'feature' you may get when walking the dynamic SQL path.

Do read The curse and blessings of dynamic SQL first, before making any potentially fatal design choices.

/Kenneth