-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[USP_PM_SearchPatronByCriteria] @IsActive bit = NULL, @PatronNo bigint=NULL, @BirthDate date=NULL, @PNFirstName nvarchar(50)=NULL, @PNLastName nvarchar(50)=NULL, @Gender nvarchar(5)=NULL, @MobileCountryCode nvarchar(5)=NULL, @Mobile nvarchar(15)=NULL, @Email nvarchar(30)=NULL, @MembershipClass nvarchar(5)=Null, @IDType nvarchar(5)=NULL, @IDNumber nvarchar(20)=NULL, @CountryID smallint=NULL, @CityName nvarchar(50)=NULL, @DocType nvarchar(5), @Page int = 1, @PageSize int = 1, @OrderSQL nvarchar(max)=null, @TotalRow int output -- Add the parameters for the stored procedure here as BEGIN SET NOCOUNT ON; SET FMTONLY OFF declare @PatronSqlWhereCommand nvarchar(Max) declare @IdentificationAndAddressSqlWhereCommand nvarchar(Max) declare @SQL nvarchar(Max) declare @PageCommand nvarchar(Max) declare @GetTotalRowSQL nvarchar(Max) declare @Result nvarchar(Max) declare @Start nvarchar(Max) declare @End nvarchar(Max) -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET @PatronSqlWhereCommand = ' where 1=1'; SET @IdentificationAndAddressSqlWhereCommand= ' where 1=1'; if(@PageSize is null or @PageSize=0) begin SET @PageSize=10; end if(@Page is null) begin SET @Page=1; end if(@OrderSQL is null or @OrderSQL='') begin SET @OrderSQL='ModifiedDate desc'; end if(@DocType is null) begin SET @DocType=''; end SET @Start=CONVERT(nvarchar(10),(@Page-1)*@PageSize+1); SET @End=CONVERT(nvarchar(10),(@Page)*@PageSize); -- if(@IsActive is not Null) begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND IsActive=',@IsActive); end -- if(@PatronNo is not Null) begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND Patron_No=',@PatronNo); end -- if(@BirthDate is not Null) begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND Birth_Date=',@BirthDate); end -- if(@PNFirstName is not Null and @PNFirstName <> '') begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND PN_First_Name like ''%',@PNFirstName,'%'''); end -- if(@PNLastName is not Null and @PNLastName <> '') begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND PN_Last_Name like ''%',@PNLastName,'%'''); end -- if(@Gender is not Null and @Gender <> '') begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND Gender=''',@Gender,''''); end -- if(@MobileCountryCode is not Null and @MobileCountryCode <> '') begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND Mobile_Country_Code=''',@MobileCountryCode,''''); end -- if(@Mobile is not Null and @Mobile <> '') begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND Mobile_No=''',@Mobile,''''); end -- if(@Email is not Null and @Email <> '') begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND eMail like ''%',@Email,'%'''); end -- if(@MembershipClass is not Null and @MembershipClass <> '') begin SET @PatronSqlWhereCommand =concat(@PatronSqlWhereCommand,' AND Membership_Class=''',@MembershipClass,''''); end -- if(@IDType is not Null and @IDType <> '') begin SET @IdentificationAndAddressSqlWhereCommand =concat(@IdentificationAndAddressSqlWhereCommand,' AND I.ID_Type=''',@IDType,''''); end -- if(@IDNumber is not null or @IDNumber = '') begin SET @IdentificationAndAddressSqlWhereCommand =concat(@IdentificationAndAddressSqlWhereCommand,' AND I.ID_No=''',@IDNumber,''''); end -- if(@CountryID is not Null) begin SET @IdentificationAndAddressSqlWhereCommand =concat(@IdentificationAndAddressSqlWhereCommand,' AND A.Country_ID=',@CountryID); end -- if(@CityName is not null or @CityName = '') begin SET @IdentificationAndAddressSqlWhereCommand =concat(@IdentificationAndAddressSqlWhereCommand,' AND A.City_Name like ''%',@CityName,'%'''); end SET @SQL='select P.*,I.ID_Type,I.ID_No,A.Country_ID,A.City_Name,DD.File_Name,(Isnull(I.Modified_Date,I.Created_Date)) as ISort_Date,(Isnull(A.Modified_Date,A.Created_Date)) as ASort_Date into #patronDetail from (select * from GSMA_Patron '+@PatronSqlWhereCommand+' ) P left join GSMA_Patron_Identification I on P.Patron_ID=I.Patron_ID and I.IsActive=''true'' left join GSMA_Patron_Address A on P.Patron_ID=A.Patron_ID and A.IsActive=''true'' left join (select * from GSAL_Document_Detail where Doc_Type='''+@DocType+''' and IsActive=''true'') DD on P.Doc_ID=DD.Doc_ID '+@IdentificationAndAddressSqlWhereCommand+' '+' select ar1.Patron_ID as PatronID, ar1.Patron_No as PatronNo, ar1.Property_ID as PropertyID, ar1.Doc_ID as DocID, ar1.PN_Prefix as PNPrefix, ar1.PN_First_Name as PNFirstName, ar1.PN_Middle_Name as PNMiddleName, ar1.PN_Last_Name as PNLastName, ar1.PN_Suffix as PNSuffix, ar1.EX_Prefix as EXPrefix, ar1.EX_First_Name as EXFirstName, ar1.EX_Middle_Name as EXMiddleName, ar1.EX_Last_Name as EXLastName, ar1.EX_Suffix as EXSuffix, ar1.Birth_Date as BirthDate, ar1.Birth_City as BirthCity, ar1.Patron_Country as PatronCountry, ar1.Gender as Gender, ar1.Preferred_Name as PreferredName, ar1.Display_Name as DisplayName, ar1.Occupation as Occupation, ar1.Communication_Lang_Id as CommunicationLangId, ar1.Junket_Operator_ID as JunketOperatorID, ar1.Player_Type as PlayerType, ar1.Membership_Class as MembershipClass, ar1.Mail_Code as MailCode, ar1.Registration_Source as RegistrationSource, ar1.Security_Level as SecurityLevel, ar1.Signup_Host as SignupHost, ar1.Mobile_Country_Code as MobileCountryCode, ar1.Mobile_No as MobileNo, ar1.Home_Phone_Country_Code as HomePhoneCountryCode, ar1.Home_Phone_No as HomePhoneNo, ar1.Business_Phone_Country_Code as BusinessPhoneCountryCode, ar1.Business_Phone_No as BusinessPhoneNo, ar1.Fax_Country_Code as FaxCountryCode, ar1.Fax_No as FaxNo, ar1.eMail as eMail, ar1.Height_Unit as HeightUnit, ar1.Height as Height, ar1.Weight_Unit as WeightUnit, ar1.Weight as Weight, ar1.Hair_Color as HairColor, ar1.Eye_Color as EyeColor, ar1.Excluded_Purge_Validity as ExcludedPurgeValidity, ar1.Exclude_Purge_Flag as ExcludePurgeFlag, ar1.Send_SMS_Flag as SendSMSFlag, ar1.Send_Email_Flag as SendEmailFlag, ar1.Ancillary_Account_Flag as AncillaryAccountFlag, ar1.IsPurged as IsPurged, ar1.IsLinked as IsLinked, ar1.IsMerged as IsMerged, ar1.IsActive as IsActive, ar1.Last_Active_Date as LastActiveDate, ar1.Row_Version as RowVersion, ar1.Device_ID as DeviceID, ar1.Created_Date as CreatedDate, ar1.Created_By as CreatedBy, ar1.Modified_By as ModifiedBy, ar1.Modified_Date as ModifiedDate, ar1.ID_Type as IDType, ar1.ID_No as IDNo, ar1.Country_ID as CountryID, ar1.City_Name as CityName, ar1.File_Name as FileName into #PatronData from (select * from #patronDetail) ar1 inner join (select a.Patron_ID,MAX(a.ISort_Date)as Date1 from #patronDetail a group by a.Patron_ID) ar2 on ar1.Patron_ID=ar2.Patron_ID and isnull(ar1.ISort_Date,GETDATE())=isnull(ar2.Date1,GETDATE()) inner join (select a.Patron_ID,MAX(a.ASort_Date)as Date2 from #patronDetail a group by a.Patron_ID) ar3 on ar1.Patron_ID=ar3.Patron_ID and isnull(ar1.ASort_Date,GETDATE())=isnull(ar3.Date2,GETDATE()) '; SET @GetTotalRowSQL='Select @Rows=count(1) from #PatronData;'; SET @Result=@SQL+@GetTotalRowSQL; Execute sp_executesql @Result, N'@Rows int output', @TotalRow output; SET @SQL=@SQL+ ' select r.*, G.Lkp_Value as GenderName, M.Lkp_Value as MembershipClassName, I.Lkp_Value as IDTypeName, C.Country_Name as CountryName into #result from #PatronData r left join GSRE_GenLKP G on r.Gender =G.Lookup_Cd left join GSRE_GenLKP M on r.MembershipClass =M.Lookup_Cd left join GSRE_GenLKP I on r.IDType =I.Lookup_Cd left join GSRE_Country C on r.CountryID =C.Country_ID select * from ( select ROW_NUMBER() over (order by '+@OrderSQL+') as row_number,* from #result ) result'; --fix EF bug if(isnull(@Start,'') =''or isnull(@End,'') ='') begin SET @PageCommand=' '; end else begin if(@TotalRow<@PageSize) begin SET @PageCommand=' '; end else begin SET @PageCommand=' where result.Row_number BETWEEN '+@Start+' AND '+@End; end end --------------------------------- SET @Result=@SQL+@PageCommand; exec(@Result); END
SET FMTONLY OFF
这样设置了就不会输出是int。。而是输出临时表了