question

Dinesh Kumar avatar image
Dinesh Kumar asked

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-2005sql-server-2000
10 |1200 characters needed characters left characters exceeded

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

Squirrel 1 avatar image
Squirrel 1 answered

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

10 |1200 characters needed characters left characters exceeded

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

Kristen avatar image
Kristen answered

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 characters needed characters left characters exceeded

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

Dinesh Kumar avatar image
Dinesh Kumar answered

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 characters needed characters left characters exceeded

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

Kristen avatar image
Kristen answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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