User defined function query

Question

question

Dinesh Kumar asked Dec 13, '09

User defined function query

Hi,

Since i cannot use table name as parameter, I would like do this, is it possible or is there a better way of doing it.

The below function return a sql query as a string.

CREATE FUNCTION SQLSELECT(@P_TABLE VARCHAR(50),@P_KEY VARCHAR(5))                    
RETURNS VARCHAR(5000)                    
AS                     
BEGIN                     
DECLARE @MSTR VARCHAR(100)                    
SET @MSTR = 'SELECT * FROM '+ @P_TABLE+' WHERE EMP_NO='''+@P_KEY+''''                    
RETURN @MSTR                    
END

Now i use a function that returns a table value.

CREATE FUNCTION SQLSELECT1 ()                    
RETURNS TABLE                     
AS                    
    RETURN SELECT * FROM (SELECT SQLUSEREN.SQLSELECT('EMPLOYEE','02814')abc)xyz                    
GO

The output of this function is SELECT * FROM EMPLOYEE WHERE EMP_NO='02814'

Can i not use the same function SQLSELECT1 or some other way to return the table value after excution of the sql statement that is returned.

If this is not possible what else is a way to achive this functionality.

Thanks and Regards

Dinesh

sql-server-2005 sql-server-2000

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-13T11:48:00Z

Squirrel 1 answered Dec 13, '09

what you want can't be done with function. You will have to use a stored procedure

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-12-13T16:37:58Z

Kristen answered Dec 13, '09

Why not just use Dynamic SQL if the Table Name is not known until runtime?

Using dynamic SQL executed via sp_ExecuteSQL is very efficient - provided that the WHERE clause is parametrised as far as you are able, AND the other parts of the dynamic SQL re-occur reasonably often - i.e. before the query plan cache becomes "stale"

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-12-14T04:05:27Z

Dinesh Kumar answered Dec 14, '09

In that case can i do this.

DECLARE @P_TABLE VARCHAR(50)            
DECLARE @P_KEY VARCHAR(5)            
DECLARE @MSTR VARCHAR(100)            
            
SET @P_TABLE = 'EMPLOYEE'            
SET @P_KEY = '02814'            
SET @MSTR = 'SELECT * FROM '+ @P_TABLE+' WHERE EMP_NO='''+@P_KEY+''''            
exec sp_executesql N'SELECT * FROM '+ @P_TABLE+ ' WHERE EMP_NO='''+@P_KEY+''''

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-12-14T04:45:22Z

Kristen answered Dec 14, '09

Yeah, I think that's good. It will be more efficient if you parametrise the WHERE clause:

            
DECLARE @P_TABLE VARCHAR(50)            
DECLARE @P_KEY VARCHAR(5)            
DECLARE @MSTR VARCHAR(100)            
            
SET @P_TABLE = 'EMPLOYEE'            
SET @P_KEY = '02814'            
SET @MSTR = 'SELECT * FROM '+ @P_TABLE+' WHERE EMP_NO=@P_KEY'            
exec sp_executesql @MSTR, N'@P_KEY varchar(100)', @P_KEY

You cannot do that with the TABLE parameter, so it needs to be part of the Dynamic SQL String. However, this query SELECT * FROM MyTable WHERE EMP_NO=@P_KEY will be cached, so any subsequent query that is EXACTLY the same will use the cached query plan - regardless of the actual value of @P_KEY. If someone causes another query to be raised for a different table that will be cached too - and the popular ones will remain in the cache.

You should not use SELECT * as that is not efficient, name the columns you want explicitly; this will also prevent you retrieving all columns in the future - e.g. when the table changes to add some massive TEXT column you don't want all the places in the application that use this query retrieving the new columns that the application doesn't use, as that will only slow everything down.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question