Help: how to generate sql statements according to parameters in a table-valued function

Transact-SQL

Ricky Wang


I need to enchance the following function:

create function dbo.GetCustomersByRegion (@PageNo integer, @PageSize integer)

returns table

as

return (

select *

from (select row_number() over(order by customerid) as row_num, *

from customers) a

where row_num >= @PageSize * @PageNo and

row_num < @PageSize * (@PageNo + 1)

)

How can I replace customers with a select sql statement and customerid with a parameter in the above function They are literals themselves and seem cannot be replaced with parameters.

Or I must resort to stored procedure

Regards,

Ricky




Top

Re: Help: how to generate sql statements according to parameters in a table-valued function

Louis Davidson


You can't. Functions in SQL cannot be based on dynamic SQL. You would need to create a function for each query you want to build like this. It may seem like more work initially (and it is) but it is best for performance as a plan will be saved for the query.

To do it in a stored procedure, you will have to use dynamic SQL.






Top