存储过程中动态SQL

--=======================================================
--Created By: yzh
--Created Date: 2015-12-31
--Description: 查看会员权益
--Test： exec P_GetEquityPlayNum 'F2015',11
--=======================================================
ALTER proc [dbo].[P_GetEquityPlayNum]
(
@MembershipCardNo varchar(200)
,@ClubID int
)
as
begin
declare @sqlStr nvarchar(max)
set @sqlStr='
select
ClubID
,RegionInnerWeekdayNumber
,RegionInnerHolidayNumber
,LimitRegionInnerNumber
,RegionOuterWeekdayNumber
,RegionOuterHolidayNumber
,LimitRegionOuterNumber
from T_EquityPlayNum e
inner join T_MemberShip m on e.MemberType = m.MembershipTypeID '
--球会ID
if not(@ClubID is null or @ClubID=0)
begin
set @sqlStr = @sqlStr + case when charindex('WHERE',@sqlStr) > 0 then ' AND' else ' WHERE' end;
set @sqlStr=@sqlStr + ' e.ClubID = ' + Convert(varchar,@ClubID);
end
--会籍证号
if not(@MembershipCardNo is null or @MembershipCardNo='')
begin
set @sqlStr = @sqlStr+ case when charindex('WHERE',@sqlStr) > 0 then ' AND' else ' WHERE' end;
set @sqlStr=@sqlStr+' m.MembershipCardNo = ''' + @MembershipCardNo +'''';
end
--print @sqlStr
exec sp_executesql @sqlStr
end

GO