My Store producres:
uspUpdateModelFileByAnalystBank
ALTER PROCEDURE [dbo].[uspUpdateModelFileByAnalystBank]
@ModelPerRequestIDs NVARCHAR(500),
@FileNameOnDisk NVARCHAR(MAX),
@AnalystBankID NVARCHAR(500),
@inputClientID int
AS
BEGIN
print @ModelPerRequestIDs
print @FileNameOnDisk
print @AnalystBankID
print 'p'
DECLARE @stmt1 AS NVARCHAR(4000)
DECLARE @ParamDefinition1 NVARCHAR(4000)
SET @ParamDefinition1 = N'@ModelPerRequestIDs NVARCHAR(500),@FileNameOnDisk NVARCHAR(MAX),@AnalystBankID NVARCHAR(500)'
SET @stmt1 = 'UPDATE tbModelXTickerXAnalystBank
SET FileNameOnDisk = @FileNameOnDisk,
IsDownloaded = 0,
DownloadDate = null,
IsDeleted = 0,
AddedOn = getdate()
WHERE ModelPerRequestID In (' + @ModelPerRequestIDs + ')
and AnalystBankId = @AnalystBankID'
print @stmt1
EXEC sp_executesql @stmt1, @ParamDefinition1, @ModelPerRequestIDs ,@FileNameOnDisk ,@AnalystBankID ;
SET @stmt1 = NULL
print 'p0--------'
print @stmt1
print @ParamDefinition1
print @ModelPerRequestIDs
print @FileNameOnDisk
print @AnalystBankID
print 'p00----------'
DECLARE @stmt AS NVARCHAR(4000)
DECLARE @ParamDefinition as NVARCHAR(4000)
DECLARE @TickerID AS INT
DECLARE @CallDate AS DATETIME
DECLARE @TentativeStartDate AS DATETIME
DECLARE @TPRID AS INT
SET @ParamDefinition ='@TickerIDOUT int OUTPUT'
SET @stmt = 'SELECT @TickerIDOUT = TickerID FROM tbmodelsperrequest where id in (' + @ModelPerRequestIDs + ')'
print @stmt
exec sp_executesql @stmt , @ParamDefinition, @TickerIDOUT=@TickerID OUTPUT
DECLARE @QuarterMonth1 AS INt
DECLARE @QuarterYear1 AS INT
DECLARE @NewQuarterMonth1 AS INt
DECLARE @NewQuarterYear1 AS INT
print 'P1-----'
SET @QuarterMonth1 = DATEPART(MONTH,GETDATE())
SET @QuarterYear1 = DATEPART(YEAR,GETDATE())
IF (@QuarterMonth1 IN (1,2,3))
BEGIN
SET @NewQuarterMonth1 = 4
Set @NewQuarterYear1 = @QuarterYear1-1
END
IF (@QuarterMonth1 IN (4,5,6))
BEGIN
SET @NewQuarterMonth1 = 1
Set @NewQuarterYear1 = @QuarterYear1
END
IF (@QuarterMonth1 IN (7,8,9))
BEGIN
SET @NewQuarterMonth1 = 2
Set @NewQuarterYear1 = @QuarterYear1
END
IF (@QuarterMonth1 IN (10,11,12))
BEGIN
SET @NewQuarterMonth1 = 3
Set @NewQuarterYear1 = @QuarterYear1
END
print @NewQuarterMonth1
print @NewQuarterYear1
print 'P2----------'
SET @CallDATE = (SELECT TOP 1 PublishedDate from tbearnings where tickerid = @TickerID AND quartermonthid = @NewQuarterMonth1 And Quarteryearid = @NewQuarterYear1
and IsDeleted = 0 ORDER BY EarningsID DESC)
print @CallDATE
SET @TentativeStartDate = (SELECT TOP 1 TentativeStartDate from tbearnings where tickerid = @TickerID AND quartermonthid = @NewQuarterMonth1 And Quarteryearid = @NewQuarterYear1
and IsDeleted = 0 ORDER BY EarningsID DESC)
print @TentativeStartDate
print @TickerID
print 'P3----------'
CREATE TABLE #TEMP
(
TickerPerREquestID INT,
RequestID INT,
ClientID INt,
GroupID INT,
SENTDATE DATETIME,
DueDATE DATETIME
)
INSERT INTO #TEMP
SELECT tbtickerperrequest.id, tbrequest.id, tbrequest.clientid,
tbrequest.groupid, tbtickerperrequest.sentdate, tbtickerperrequest.duedate
FROM tbtickerperrequest INNER JOIN tbrequest
ON tbrequest.id = tbtickerperrequest.RequestID
where tbtickerperrequest.tickerid = @tickerID and tbtickerperrequest.requesttype = 2
and tbTickerPerRequest.IsDeleted = 0 and tbrequest.IsDeleted = 0 and StatusId =11
print 'P4----------'
DECLARE @NewID INT
DECLARE @AdhocDueDate AS DATETIME
DECLARE @NewTPRID AS INT
DECLARE @ClientID AS INT
DECLARE @GroupID AS INT
DECLARE @RequestID AS INT
DECLARE @ProductTypeID AS INT
DECLARE @OldProductTypeID AS INT
DECLARE @Iterations as NVARCHAR(20)
DECLARE @RequestAdded as BIT
DECLARE TempSSR_Cursor CURSOR FOR
SELECT Distinct(ClientID),GroupID, RequestID, TickerPerREquestID FROM #TEMP;
OPEN TempSSR_Cursor
FETCH NEXT FROM TempSSR_Cursor INTO @ClientID,@GroupID, @RequestID, @TPRID
WHILE @@FETCH_STATUS = 0
BEGIN
print @ClientID
print @GroupID
print @RequestID
print @TPRID
print 'P5----------'
DECLARE @stmtSSR AS NVARCHAR(4000)
DECLARE @ParamDefinitionSSR AS NVARCHAR(4000)
DECLARE @SSRCount AS INT
DECLARE @AfterCallDate2x as INT
DECLARE @NoOfDays2xPost as INT
DECLARE @stmtafterCallDatepost2x as NVARCHAR(4000)
DECLARE @ParamAfterPost2x AS NVARCHAR(4000)
SET @ParamDefinitionSSR = '@CLientID INT,@GroupID INT, @Count int OUTPUT'
SET @stmtSSR = 'SELECT @Count = Count(*) FROM tbSellSideCoverageXAnalystBank
INNER JOIN tbsellsidecoverage ON SSCID = SellSideCoverageID
inner join tbSellSideCoverageXGroup G on tbSellSideCoverageXAnalystBank.SSCID=g.SSCID
WHERE companyid = @clientID AND G.GROUPID=@GroupID and tbsellsidecoverage.analystbankid is null
and tbSellSideCoverageXAnalystBank.Analystbankid in (' + @AnalystBankID + ') AND
tbsellsidecoverage.ISDeleted=0 and ISNULL(tbsellsidecoverage.RelationCheck,1)=1'
print @AnalystBankID
print @stmtSSR
exec sp_executesql @stmtSSR , @ParamDefinitionSSR, @ClientID,@GroupID, @Count=@SSRCount OUTPUT
print @SSRCount
print 'P6----------'
IF (@SSRCount > 0 or @inputClientID=@ClientID)
BEGIN
print 'P6----------P6P Start'
print @TickerID
print @ClientID
print @GroupID
print 'P6----------P6P END'
iF EXISTS(SELECT 1 FROM tbActiveListXTicker
INNER JOIN tbTicker ON tbTicker.TickerID = tbActiveListXTicker.TickerID
INNER JOIN
(
select distinct(tbactivelist.ActiveListID),tbactivelist.clientid, tbCompany.CompanyName,
tbgroup.GroupID, tbgroup.GroupName
from tbActiveList
inner join tbActiveListXTicker on tbactivelist.activelistid = tbactivelistxticker.activelistid
inner join tbTicker on tbActiveListXTicker.TickerID = tbticker.TickerID
inner join tbCompany on tbCompany.CompanyID = tbActiveList.ClientID
inner join tbGroup on tbactivelist.groupid = tbGroup.groupid
INNER JOIN tsYear ON tsYear.YearID = tbActiveList.QuarterYearID
INNER JOIN tsMonth ON tsmonth.MonthID = tbActiveList.QuarterMonthID
INNER JOIN tsactiveListStatus ON tbActiveList.ActiveListStatusID = tsActiveListStatus.ActiveListStatusID
inner join tbrequest on tbrequest.id = tbactivelist.requestid
INNER JOIN tsProductType ON tbRequest.ProductTypeId = tsProductType.ProductTypeID
WHERE tbCompany.IsDeleted = 0
AND tbgroup.IsDeleted = 0
ANd tbactivelist.IsDeleted = 0
and tbticker.isdeleted = 0
) AS li ON lI.ActiveListID=tbActiveListXTicker.ActiveListID
inner join tbSellSideCoverageXGroup R on R.groupid=li.groupid
inner Join [dbo].[tbSellSideCoverageXAnalystBank] A on R.SsCID=A.SSCID
Inner Join tsAnalystBank AN on AN.AnalystBankID=A.AnalystBankID
WHERE
tbTicker.TickerID = @TickerID
AND tbTicker.IsDeleted= 0
AND LI.ClientID=@ClientID
aND li.GroupID=@GroupID
and AN.IsDeleted=0
)
BEGIN
SET @Iterations = (SELECT Iterations from tbRequest where ID = @RequestID)
SET @RequestAdded = (SELECT isrequestadded from tbTickerPerRequest where ID = @TPRID)
SET @OldProductTypeID = (SELECt ProductTypeId from tbrequest where ID = @RequestID)
print @Iterations
print @RequestAdded
print @OldProductTypeID
print @ClientID
print @GroupID
print @OldProductTypeID
print @NewQuarterMonth1
print @NewQuarterYear1
print @CallDate
print @TickerID
print 'P7----------'
Update tbTickerPerRequest
set StatusId = 10, IsDeleted = 1
where RequestId in
(
select tbrequest.id from tbrequest inner join tbTickerPerRequest
on tbrequest.Id = tbTickerPerRequest.RequestId
where tbRequest.ClientId = @ClientID and GroupId = @GroupID and ProductTypeId = @OldProductTypeID
and Iterations like '%1'
and tbTickerPerRequest.RequestType = 2 and tbTickerPerRequest.QuarterMonth= @NewQuarterMonth1
and tbTickerPerRequest.QuarterYear = @NewQuarterYear1
and IsRequestAdded = 0 and tbrequest.IsDeleted = 0 and tbtickerperrequest.IsDeleted = 0
)and SentDate is null and TickerId = @TickerID
and (
(
Convert(date, tbtickerperrequest.duedate,101) > Convert(DAte, @CallDate,101)
OR Convert(date, tbtickerperrequest.duedate,101) > Convert(DAte, @TentativeStartDate,101)
)
or (
Convert(date, GETDATE(),101) > Convert(DAte, @CallDate,101)
OR Convert(date, getdate(),101) > Convert(DAte, @TentativeStartDate,101)
)
)
print 'P8----------'
print @Iterations
print @CallDate
print @TentativeStartDate
print 'P9----------'
IF ((@Iterations like '%1' and (Convert(DAte, @CallDate,101) >= Convert(Date, GETDATE(),101)
or Convert(DAte, @TentativeStartDate,101) >= Convert(Date, GETDATE(),101)))
OR (@Iterations like '%2' and (Convert(DAte,@CallDate,101) >= Convert(Date,GETDATE()
,101) or Convert(DAte, @TentativeStartDate,101) >= Convert(Date, GETDATE(),101))))
OR (@Iterations like 'on-going' and (Convert(DAte,@CallDate,101) >= Convert(Date,GETDATE()
,101) or Convert(DAte, @TentativeStartDate,101) >= Convert(Date, GETDATE(),101)))
BEGIN
print 'P10----------'
print @ClientID
print @GroupID
print @TickerID
print @ClientID
print 'P11----------'
IF EXISTS(SELECT tbtickerperrequest.Id from tbTickerPerRequest inner join tbRequest
on tbrequest.Id = tbTickerPerRequest.RequestId where tbrequest.ClientId = @ClientID
and tbrequest.GroupId=@GroupID and TickerId = @TickerID
and tbTickerPerRequest.IsDeleted = 0 and tbrequest.IsDeleted = 0
and tbTickerPerRequest.RequestType=2
and tbTickerPerRequest.StatusId NOT IN (1,2,4,6,8,10,12,13,14,15,16,17,18,19,20,21))
BEGIN
print 'P12----------'
print @RequestID
print @Iterations
print @ClientID
print 'P13----------'
INSERT INTO tbRequest(ClientId, GroupId, ProductTypeId, RequestType, PrintPreview, GeneralComments, Iterations, RequestedOn)
SELECT @ClientID, groupid, producttypeid, 2, printpreview, GeneralComments, @Iterations, GETDATE() from tbrequest where ID = @RequestID
SET @NewID = SCOPE_IDENTITY()
print @NewID
print 'P14----------'
SET @ProductTypeID = (SELECT ProductTypeID FROM tbrequest WHERE id = @NewID)
print @ProductTypeID
print 'P15----------'
INSERT INTO tbTickerPerRequest(RequestId,RequestType, StatusId,quartermonth, quarteryear, TickerId)
VALUES( @NewID,2, 1,@NewQuarterMonth1, @NewQuarterYear1,@TickerID )
print @NewID
print @NewQuarterMonth1
print @NewQuarterYear1
print @TickerID
print 'P16----------'
SET @NewTPRID = SCOPE_IDENTITY()
print @NewTPRID
print 'P17----------'
SET @AdhocDueDate = (SELECT Duedate FROM fnAddDueDate(@ProductTypeID,@TickerID))
print @ProductTypeID
print @TickerID
print @AdhocDueDate
print 'P18----------'
IF (SELECT DueDate FROM tbtickerperrequest where id = @NewTPRID) IS NULL
BEGIN
print 'P19----------'
Update tbTickerPerRequest
Set DueDate = @AdhocDueDate
FROM tbrequest
Where tbrequest.id = tbtickerperrequest.requestid and tbtickerperrequest.id = @NewTPRID
and tbrequest.id = @NewID
print @NewID
print @NewTPRID
print 'P20----------'
SET @AdhocDueDate = null
END
print 'P21----------'
END
print 'P22----------'
END
ELSE IF (@Iterations like '%2' and (Convert(date,@CallDate,101) < Convert(Date,GETDATE(),101) or Convert(DAte, @TentativeStartDate,101) < Convert(Date, GETDATE(),101)) and @RequestAdded = 0)
BEGIN
print 'P23----------'
print 'P24----------'
BEGIN
print @ClientID
print @GroupID
print @TickerID
print @ClientID
print 'P25----------'
IF EXISTS(SELECT tbtickerperrequest.Id from tbTickerPerRequest inner join tbRequest
on tbrequest.Id = tbTickerPerRequest.RequestId
where tbrequest.ClientId = @ClientID and tbrequest.GroupId=@GroupID
and TickerId = @TickerID
and tbTickerPerRequest.IsDeleted = 0 and tbrequest.IsDeleted = 0
and tbTickerPerRequest.RequestType=2
and tbTickerPerRequest.StatusId NOT IN (1,2,4,6,8,10,12,13,14,15,16,17,18,19,20,21))
BEGIN
print 'P26----------'
print @RequestID
print @ClientID
print @Iterations
INSERT INTO tbRequest(ClientId, GroupId, ProductTypeId, RequestType, PrintPreview, GeneralComments, Iterations, RequestedOn)
SELECT @ClientID, groupid, producttypeid, 2, printpreview, GeneralComments, @Iterations, GETDATE() from tbrequest where ID = @RequestID
print 'P27----------'
SET @NewID = SCOPE_IDENTITY()
SET @ProductTypeID = (SELECT ProductTypeID FROM tbrequest WHERE id = @NewID)
print @NewID
print @ProductTypeID
INSERT INTO tbTickerPerRequest(RequestId,RequestType, StatusId,quartermonth, quarteryear, TickerId, isrequestadded)
VALUES( @NewID,2, 1,@NewQuarterMonth1, @NewQuarterYear1,@TickerID,1 )
print 'P28----------'
print @NewID
print @NewQuarterMonth1
print @NewQuarterYear1
print @TickerID
print 'P29----------'
SET @NewTPRID = SCOPE_IDENTITY()
SET @AdhocDueDate = (SELECT Duedate FROM fnAddDueDate(@ProductTypeID,@TickerID))
print @NewTPRID
print @ProductTypeID
print @TickerID
print @AdhocDueDate
print 'P30----------'
IF (SELECT DueDate FROM tbtickerperrequest where id = @NewTPRID) IS NULL
BEGIN
print 'P31----------'
Update tbTickerPerRequest
Set DueDate = @AdhocDueDate, isrequestadded = 1
where requestid in (select ID FROM tbrequest
Where tbrequest.id = @NewID )
and tbtickerperrequest.id = @NewTPRID
print @AdhocDueDate
print @NewID
print @NewTPRID
print 'P32----------'
update tbTickerPerRequest set IsRequestAdded = 1
where RequestId = @RequestID
print @RequestID
print 'P33----------'
SET @AdhocDueDate = null
END
print 'P34----------'
END
print 'P35----------'
END
print 'P36----------'
END
ELSE IF (@Iterations = 'on-going' and (Convert(date,@CallDate,101) < Convert(Date,GETDATE(),101)
or Convert(DAte, @TentativeStartDate,101) < Convert(Date, GETDATE(),101)))
BEGIN
print @Iterations
print @CallDate
print @TentativeStartDate
print 'P37----------'
print @ClientID
print @GroupID
print @TickerID
print 'P38----------'
IF EXISTS(SELECT tbtickerperrequest.Id from tbTickerPerRequest inner join tbRequest
on tbrequest.Id = tbTickerPerRequest.RequestId where tbrequest.ClientId = @ClientID
and tbrequest.GroupId=@GroupID and TickerId = @TickerID and
tbTickerPerRequest.IsDeleted = 0 and tbrequest.IsDeleted = 0
and tbTickerPerRequest.RequestType=2
and tbTickerPerRequest.StatusId NOT IN (1,2,4,6,8,10,12,13,14,15,16,17,18,19,20,21))
BEGIN
print 'P39----------'
print @RequestID
print @Iterations
print @ClientID
print 'P40----------'
INSERT INTO tbRequest(ClientId, GroupId, ProductTypeId, RequestType, PrintPreview, GeneralComments, Iterations, RequestedOn)
SELECT @ClientID, groupid, producttypeid, 2, printpreview, GeneralComments, @Iterations, GETDATE() from tbrequest where ID = @RequestID
SET @NewID = SCOPE_IDENTITY()
SET @ProductTypeID = (SELECT ProductTypeID FROM tbrequest WHERE id = @NewID)
print @NewID
print @ProductTypeID
print 'P41----------'
print @NewQuarterMonth1
print @NewQuarterYear1
print @TickerID
print 'P42----------'
INSERT INTO tbTickerPerRequest(RequestId,RequestType, StatusId,quartermonth, quarteryear, TickerId, IsRequestAdded)
VALUES( @NewID,2, 1,@NewQuarterMonth1, @NewQuarterYear1,@TickerID,1 )
SET @NewTPRID = SCOPE_IDENTITY()
SET @AdhocDueDate = (SELECT Duedate FROM fnAddDueDate(@ProductTypeID,@TickerID))
print @NewTPRID
print @ProductTypeID
print @TickerID
print @AdhocDueDate
print 'P43----------'
IF (SELECT DueDate FROM tbtickerperrequest where id = @NewTPRID) IS NULL
BEGIN
print 'P44----------'
Update tbTickerPerRequest
Set DueDate = @AdhocDueDate, IsRequestadded = 1
where requestid in (select Id FROM tbrequest
Where tbrequest.id = @NewID )
and tbtickerperrequest.id = @NewTPRID
SET @AdhocDueDate = null
print 'P45----------'
END
END
print 'P46----------'
END
END
END
FETCH NEXT FROM TempSSR_Cursor INTO @ClientID,@GroupID, @RequestID,@TPRID
END
CLOSE TempSSR_Cursor
DEALLOCATE TempSSR_Cursor
END
I executed this procedures I m getting multiple rows.plz help me
Submit an article or tip