top参数化问题
各位大侠：
USE [lifewood]
GO
/****** 对象: UserDefinedFunction [dbo].[PaginationSearch] 脚本日期: 07/19/2010 10:24:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[PaginationSearch]
(
@startIndex int,
@endIndex int,
@strWhere varchar(30)
)
returns table
as
return
(
select * from
(
select top (@endIndex) row_number() over(order by id) rn,surname,years,frequency
from english_surname where surname like '%'+@strWhere+'%'
and frequency>10)t where rn>=@StartIndex
union
select * from
(
select top (@endIndex) row_number() over(order by id) rn,givenname,years,frequency
from english_givenname where givenname like '%'+@strWhere+'%'
and frequency>10)t1 where rn>=@StartIndex
union
select * from (
select top (@endIndex) row_number() over(order by id) rn, region_address,block_address,street_address from HongKong_address.dbo.hongkong_address
where block_address like '%'+@strWhere+'%' or street_address like '%'+@strWhere+'%' or
region_address like '%'+@strWhere+'%')t2 where rn>@startIndex
)
这是我写的一个函数，但是在top后面跟参数的时候，我加()以后，还是有问题，请问各位我应该改进这个top参数化问题？
[解决办法]
一般用动态执行的方法来解决，字符串累加生成SQL语句再执行
