But why would it do that with the extra memory? Is there a way around this?
Its almost as if I shouldnt have added the additional memory.......
Here is the proc:
CREATE PROCEDURE [dbo].[GetContractNumberFromFilter]
-- Add the parameters for the stored procedure here
@ProgramID Varchar(25) = '',
@ProdOfficeCode Varchar(4) = '' ,
@CedantID int = 0,
@UWCode Varchar(4) = '',
@LegalCode Varchar(4) = '',
@BrokerCode Varchar(3) = '',
@ProdTypeID int = 0,
@ContTypeID int =0,
@StatusID int =0,
@IRISPolicyNum Varchar(25) = '',
@SubmissionID Varchar(25) = NULL,
@EffectiveDateFrom varchar(10) = NULL,
@EffectiveDateTo varchar(10) = NULL,
@IncDate VARCHAR(2) = NULL ,--contains <, > <= conditions
@BrokerReference Varchar(12) = '',
@TreatyTitle Varchar(100) = NULL,
@ProfitCenterID int = 0,
@DocTypID int =0,
@IsPureTerror bit =0,
@SQLUNMODELLED bit= 0,
@MaxRecords int = 100000,
@DOCUMENTSEARCH bit =0,
@SQLMODELLED bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
--@EffectiveDateFrom DateTime = NULL,
--@EffectiveDateTo DateTime = NULL,
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @SQL_UNMODELLED varchar(100)
Declare @Where varchar(8000)
Declare @Join varchar (8000)
DECLARE @MaxRec VARCHAR(50)
IF @DocumentSearch = 1
BEGIN
SET @SQLUNMODELLED = 0
SET @SQLMODELLED = 0
END
Select @Where = ''
Select @Join = ''
Select @SQL_UNMODELLED = ' Not Exists (Select C.ProgramID From tbl_Contract C Where C.ProgramID = P.ProgramID) '
If (@IsPureTerror = 1)
Begin
Select @Where = [dbo].[BuildCondition] (@Where,' dbo.IsPureTerror(C.PerilCode) > 0 ')
End
IF Len(@ProgramID) > 0
Begin
IF RIGHT(@ProgramID,1)='*'
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ProgramID LIKE ''' + REPLACE(@ProgramID,'*','%') +'''' )
End
Else
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ProgramID = ''' + @ProgramID + '''' )
End
End
IF Len(@IRISPolicyNum) > 0
Begin
IF RIGHT(@IRISPolicyNum,1)='*'
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.IRISPolicyNum LIKE ''' + REPLACE(@IRISPolicyNum,'*','%') +'''' )
End
Else
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.IRISPolicyNum = ''' + @IRISPolicyNum + '''' )
End
End
IF Len(@ProdOfficeCode) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.ProdOfficeCode = ' + '''' + @ProdOfficeCode + '''')
End
IF @CedantID > 0
Begin
--Select @Where = [dbo].[BuildCondition] (@Where, @Where + 'P.CedantID = ' + + CONVERT(varchar, @CedantID) )
Select @Where = [dbo].[BuildCondition] (@Where, 'P.CedantID = ' + CONVERT(varchar, @CedantID) )
End
IF Len(@UWCode) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.UWCode = ' + '''' + @UWCode + '''')
End
-- IF Len(@LegalCode) > 0
-- Begin
-- Select @Where = [dbo].[BuildCondition] (@Where,'P.UWCode = ' + '''' + @LegalCode + '''')
-- End
IF Len(@BrokerCode) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.BrokerCode = ' + '''' + @BrokerCode + '''')
End
IF @ProdTypeID > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ProdTypeID = ' + CONVERT(varchar, @ProdTypeID) )
End
IF @ContTypeID > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ContTypeID = ' + CONVERT(varchar, @ContTypeID) )
End
IF @StatusID > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.StatusID = ' + CONVERT(varchar, @StatusID) )
End
/*IF @IRISPolicyNum > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.IRISPolicyNum = ' + CONVERT(varchar, @IRISPolicyNum) )
End*/
IF Len(@SubmissionID) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'C.SubmissionID LIKE ''' + REPLACE(@SubmissionID,'*','%') + '''')
End
IF (ISNULL(@EffectiveDateFrom,'') <> '')
BEGIN
IF (ISNULL(@EffectiveDateTo,'') <> '')
BEGIN
--Between
IF Len(@Where) > 0
Begin
Select @Where = @Where + ' AND ' + '(P.EffectiveDate BETWEEN ' + '''' + CONVERT(varchar,@EffectiveDateFrom) + ''''
+ ' AND ' + '''' + @EffectiveDateTo + '''' + ')'
End
Else
Begin
Select @Where = '(P.EffectiveDate BETWEEN ' + '''' + CONVERT(varchar,@EffectiveDateFrom) + ''''
+ ' AND ' + '''' + CONVERT(varchar,@EffectiveDateTo) + '''' + ')'
END
END
ELSE
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.EffectiveDate ' + @IncDate + '''' + CONVERT(varchar,@EffectiveDateFrom) + '''')
End
END
IF Len(@BrokerReference) > 0
Begin
Select @Join = @Join + ' INNER JOIN BrokerInfo AS B ON P.ProgramID = B.ProgramID '
Select @Where = [dbo].[BuildCondition] (@Where,'B.BrokerReference LIKE ' + '''' + @BrokerReference + '''')
End
IF Len(@TreatyTitle) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'C.TreatyTitle LIKE ' + '''' + @TreatyTitle + '''')
End
IF @ProfitCenterID > 0
Begin
--Select @Where = 'P.ProgramID = ' + CONVERT(varchar, @ProgramID)
Select @Where = [dbo].[BuildCondition] (@Where, 'C.ProfitCenterID = ' + CONVERT(varchar, @ProfitCenterID) )
End
IF @DocTypID > 0
Begin
--Select @Where = 'P.ProgramID = ' + CONVERT(varchar, @ProgramID)
Select @Join = @Join + ' INNER JOIN DMSDocument AS D ON D.ContractID = C.ContractID '
Select @Where = [dbo].[BuildCondition] (@Where, 'D.DocumentTypeID = ' + CONVERT(varchar, @DocTypID) )
End
IF Len(@LegalCode) > 0
Begin
IF @DocTypId = 0
BEGIN
Select @Join = @Join + ' INNER JOIN DMSDocument AS D ON D.ContractID = C.ContractID '
END
--Common for both cases - DocTypeId = 0 and docTypeID > 0
Select @Join = @Join + ' INNER JOIN DMSLegalAnalystReview AS LAR ON LAR.DocId = D.ID '
Select @Where = [dbo].[BuildCondition] (@Where,'LAR.LegalAnalystCode = ' + '''' + @LegalCode + '''')
End
If @SQLUNMODELLED = 1
BEGIN
Select @WHERE = [dbo].[BuildCondition] (@Where,@SQL_UNMODELLED)
END
IF Len(@Where) > 0
Begin
Select @Where = ' Where ' + @Where
End
SELECT @MaxRec = CONVERT(VARCHAR, @MaxRecords)
IF @DOCUMENTSEARCH = 1
BEGIN
/*when the search is document search, we need to get Programid, contractid from RWS and then use that
combination to get the appropriate records from axis_dms database*/
CREATE TABLE #TEMPDMS
(ProgramId INT NULL,
ContractId INT NULL,
TreatyTitle VARCHAR(100) NULL,
ProfitCenter VARCHAR(50) NULL,
LineOfBusiness VARCHAR(50) NULL,
)
EXEC ('INSERT INTO #TEMPDMS (ProgramId, ContractId, TreatyTitle, ProfitCenter, LineOfBusiness) Select TOP ' + @MaxRec + ' P.ProgramID, C.ContractID
,C.TreatyTitle, PC.ProfitCenter, PT.Description FROM tbl_Program AS P INNER JOIN
tbl_Contract AS C ON P.ProgramID = C.ProgramID LEFT JOIN tbl_ProfitCenter AS PC ON C.ProfitCenterID = PC.ProfitCenterID LEFT JOIN tbl_ProdType PT ON P.ProdTypeId = PT.ProdTypeId ' + @Join + @Where + ' ORDER BY P.ProgramId DESC' )
EXEC [GetDMSDocumentInformationForSearch]
DROP TABLE #TEMPDMS
END
Else
BEGIN
--SELECT @MaxRec = CONVERT(VARCHAR, @MaxRecords)
IF @SQLMODELLED =1
Exec('Select TOP ' + @MaxRec + ' * FROM vw_Program P WHERE P.ProgramID IN (Select P.ProgramID FROM vw_Program P INNER JOIN
tbl_Contract AS C ON P.ProgramID = C.ProgramID' + @Join + @Where + ') ORDER BY P.ProgramId DESC' )
ELSE
Exec('Select TOP ' + @MaxRec + ' * FROM vw_Program P WHERE P.ProgramID IN (Select P.ProgramID FROM vw_Program P LEFT JOIN
tbl_Contract AS C ON P.ProgramID = C.ProgramID' + @Join + @Where + ') ORDER BY P.ProgramId DESC' )
END
END