Link to home
Start Free TrialLog in
Avatar of sqlcurious
sqlcuriousFlag for United States of America

asked on

need to implement cursor in stored proc

hi experts
i want to add cusrsor in the attached stored procedure so that i can get the result for each row.
i need to develop the report from the proc where i need to show each rows multiple times l
like in the format below

project name    control number
county name    project date


project name     control number
county name   project date
:
:
:
:

ProjectDetailCost-StoredPrc.txt
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
you don't need a cursor for that ...

do it this way...

insert into a temporary table
and use a select....


if you actually want to do further processing per row....

then again avoid a cursor
and walk through the result set with a do while loop  using the RN identity column... as a row number...
ALTER    PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost1]
(	
	--@projectID int =null,
	@ProjectNumber VARCHAR(50)=Null,
	@ProjectControlNumber VARCHAR(50)= Null,
	@ProjectLettingDateFrom DATETIME,
	@ProjectLettingDateTo DATETIME,
	@ProjectName varchar(50) = Null,
	@DivisionName VARCHAR(Max) = Null
)	
AS	
begin
DECLARE @SQL VARCHAR(8000)
  
  

  set @SQL= 
  'SELECT t1.*,((TotalPrice - TotalCost - TotalFreight)/TotalCost)*100 as Markup,
            ((TotalPrice - TotalCost - TotalFreight)/TotalPrice)*100 as Margin,
	    t2.TotalBidPrice
	    ,identity(int,1,1) as rn
      into #temp  
  FROM 
  (SELECT 
P.ProjectID,
 P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
 convert(varchar(10),p.ProjectStartDate,110)as ProjectStartDate, 
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As UnitPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,       
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalPrice,
--QI.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TotalFreight

FROM TBL_Project P 

inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner   join TBL_Division D ON D.createdby = Q.quotecreatedby
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID
where P.projectID>=1'





if(@projectnumber is not null and @projectnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectnumber =  '''+ convert(varchar(5),@projectnumber ) +''''               
 End             
          
if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectcontrolnumber =  '''+ convert(varchar(5),@Projectcontrolnumber) + ''''               
 End            


if @projectname  <> ''
begin
    set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%''' 
end

If @ProjectLettingDateFrom Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLettingDateFrom As Varchar(30))+''''
	End

If @ProjectLettingDateTo Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLettingDateTo As Varchar(30))+''''
	End

if @DivisionName <> ''


 begin
 
 SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
 end
 
 

SET @SQL = @SQL + ') t1 '


SET @SQL = @SQL + '
cross join (
select SUM(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalBidPrice
FROM TBL_Project P 

inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner join   TBL_Division D ON D.createdby = Q.quotecreatedby
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID
where P.projectID>=1'





if(@projectnumber is not null and @projectnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectnumber =  '''+ convert(varchar(5),@projectnumber ) +''''               
 End             
          
if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectcontrolnumber =  '''+ convert(varchar(5),@Projectcontrolnumber) + ''''               
 End            


--if @projectname  <> ''
--begin
--    set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%''' 
--end


if @projectname  <> ''
begin
SET @SQL= @Sql + ' AND P.ProjectName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @ProjectName + ''','',''))'
 end
 



If @ProjectLettingDateFrom Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLettingDateFrom As Varchar(30))+''''
	End

If @ProjectLettingDateTo Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLettingDateTo As Varchar(30))+''''
	End

if @DivisionName <> ''


 begin
 
 SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
 end
 
 

SET @SQL = @SQL + ') t2'
print @sql
exec (@sql)

select x.name as [Type],Case Seq 
When 1 then convert(varchar(max),Project id)
When 2 then convert(varchar(max),Projectname)
When 3 then convert(varchar(max),ProjectControlNumber)
When 4 then convert(varchar(max),ProjectCountyID)
When 5 then convert(varchar(max),ProjectNumber)
When 6 then convert(varchar(max),ProjectStartDate)
When 7 then convert(varchar(max),Item#)
When 8 then convert(varchar(max),ItemDescription)
When 9 then convert(varchar(max),ItemQuantity)
When 10 then convert(varchar(max),ItemUnit)
When 11 then convert(varchar(max),UnitCost)
When 12 then convert(varchar(max),UnitPrice)
When 13 then convert(varchar(max),TotalCost)
When 14 then convert(varchar(max),TotalPrice)
When 15 then convert(varchar(max),DivisionID)
When 16 then convert(varchar(max),Divisionname)
When 17 then convert(varchar(max),CountyName)
When 18 then convert(varchar(max),QuoteID)
When 19 then convert(varchar(max),TotalFreight)
When 20 then convert(varchar(max),Markup)
When 21 then convert(varchar(max),Margin)
When 22 then convert(varchar(max),TotalBidPrice)
end as [Value]
from #temp as t
cross join (
Select 1 as seq ,'Project id' as name
union all Select 2,'Projectname'
union all Select 3,'ProjectControlNumber'
union all Select 4,'ProjectCountyID'
union all Select 5,'ProjectNumber'
union all Select 6,'ProjectStartDate' 
union all Select 7,'Item#'
union all Select 8,'ItemDescription'
union all Select 9,'ItemQuantity'
union all Select 10,'ItemUnit'
union all Select 11,'UnitCost'
union all Select 12,'UnitPrice'
union all Select 13,'TotalCost'       
union all Select 14,'TotalPrice'
union all Select 15,'DivisionID'
union all Select 16,'Divisionname'
union all Select 17,'CountyName'
union all Select 18,'QuoteID'
union all Select 19,'TotalFreight'
union all Select 20,'Markup'
union all Select 21,'Margin'
union all Select 22,'TotalBidPrice'
) as x
order by rn,seq

END

GO

Open in new window

Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

got following errors

Msg 102, Level 15, State 1, Procedure DBSP_Report_GetProjectDetailCost1, Line 171
Incorrect syntax near 'id'.
Msg 156, Level 15, State 1, Procedure DBSP_Report_GetProjectDetailCost1, Line 218
Incorrect syntax near the keyword 'as'.
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

can you help me to implement do while loop in the stored procedure?
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
like this ?
ALTER    PROCEDURE [dbo].[DBSP_Report_GetProjectDetailCost1]
(	
	--@projectID int =null,
	@ProjectNumber VARCHAR(50)=Null,
	@ProjectControlNumber VARCHAR(50)= Null,
	@ProjectLettingDateFrom DATETIME,
	@ProjectLettingDateTo DATETIME,
	@ProjectName varchar(50) = Null,
	@DivisionName VARCHAR(Max) = Null
)	
AS	
begin
DECLARE @SQL VARCHAR(8000)
  
  

  set @SQL= 
  'SELECT t1.*,((TotalPrice - TotalCost - TotalFreight)/TotalCost)*100 as Markup,
            ((TotalPrice - TotalCost - TotalFreight)/TotalPrice)*100 as Margin,
	    t2.TotalBidPrice
	    ,identity(int,1,1) as rn
      into #temp  
  FROM 
  (SELECT 
P.ProjectID,
 P.Projectname,
P.ProjectControlNumber,
P.ProjectCountyID,
P.ProjectNumber,
 convert(varchar(10),p.ProjectStartDate,110)as ProjectStartDate, 
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As UnitPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,       
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalPrice,
--QI.QuoteItemMarkupPercent AS MarkupPercent,
D. DivisionID,
D.Divisionname,
C.CountyName,
Q.QuoteID,
(Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3) AS TotalFreight

FROM TBL_Project P 

inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner   join TBL_Division D ON D.createdby = Q.quotecreatedby
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID
where P.projectID>=1'





if(@projectnumber is not null and @projectnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectnumber =  '''+ convert(varchar(5),@projectnumber ) +''''               
 End             
          
if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectcontrolnumber =  '''+ convert(varchar(5),@Projectcontrolnumber) + ''''               
 End            


if @projectname  <> ''
begin
    set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%''' 
end

If @ProjectLettingDateFrom Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLettingDateFrom As Varchar(30))+''''
	End

If @ProjectLettingDateTo Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLettingDateTo As Varchar(30))+''''
	End

if @DivisionName <> ''


 begin
 
 SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
 end
 
 

SET @SQL = @SQL + ') t1 '


SET @SQL = @SQL + '
cross join (
select SUM(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS TotalBidPrice
FROM TBL_Project P 

inner  join TBL_Quotes1 Q ON Q.ProjectID = P.ProjectID
inner  JOIN TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
inner join   TBL_Division D ON D.createdby = Q.quotecreatedby
inner  join TBL_County C ON P.ProjectCountyID = C.CountyID
where P.projectID>=1'





if(@projectnumber is not null and @projectnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectnumber =  '''+ convert(varchar(5),@projectnumber ) +''''               
 End             
          
if(@Projectcontrolnumber is not null and @Projectcontrolnumber <> '')                
 Begin                
   Set @SQL =  @SQL + ' and p.projectcontrolnumber =  '''+ convert(varchar(5),@Projectcontrolnumber) + ''''               
 End            


--if @projectname  <> ''
--begin
--    set @SQL= @Sql + ' And p.projectname LIKE ''%'+@projectname+'%''' 
--end


if @projectname  <> ''
begin
SET @SQL= @Sql + ' AND P.ProjectName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @ProjectName + ''','',''))'
 end
 



If @ProjectLettingDateFrom Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@ProjectLettingDateFrom As Varchar(30))+''''
	End

If @ProjectLettingDateTo Is Not Null
	Begin
		Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@ProjectLettingDateTo As Varchar(30))+''''
	End

if @DivisionName <> ''


 begin
 
 SET @SQL= @Sql + ' AND D.DivisionName in (SELECT Item FROM dbo.fneBid_SplitValue(''' + @DivisionName + ''','',''))'
 end
 
 
Declare @rows int,@i int

SET @SQL = @SQL + ') t2 '
   +' select @rows=@@rowcount,@i=1'

Declare @rows int,@i int


print @sql
exec (@sql)
 
/* 
select x.name as [Type],Case Seq 
When 1 then convert(varchar(max),Project id)
When 2 then convert(varchar(max),Projectname)
When 3 then convert(varchar(max),ProjectControlNumber)
When 4 then convert(varchar(max),ProjectCountyID)
When 5 then convert(varchar(max),ProjectNumber)
When 6 then convert(varchar(max),ProjectStartDate)
When 7 then convert(varchar(max),Item#)
When 8 then convert(varchar(max),ItemDescription)
When 9 then convert(varchar(max),ItemQuantity)
When 10 then convert(varchar(max),ItemUnit)
When 11 then convert(varchar(max),UnitCost)
When 12 then convert(varchar(max),UnitPrice)
When 13 then convert(varchar(max),TotalCost)
When 14 then convert(varchar(max),TotalPrice)
When 15 then convert(varchar(max),DivisionID)
When 16 then convert(varchar(max),Divisionname)
When 17 then convert(varchar(max),CountyName)
When 18 then convert(varchar(max),QuoteID)
When 19 then convert(varchar(max),TotalFreight)
When 20 then convert(varchar(max),Markup)
When 21 then convert(varchar(max),Margin)
When 22 then convert(varchar(max),TotalBidPrice)
end as [Value]
from #temp as t
cross join (
Select 1 as seq ,'Project id' as name
union all Select 2,'Projectname'
union all Select 3,'ProjectControlNumber'
union all Select 4,'ProjectCountyID'
union all Select 5,'ProjectNumber'
union all Select 6,'ProjectStartDate' 
union all Select 7,'Item#'
union all Select 8,'ItemDescription'
union all Select 9,'ItemQuantity'
union all Select 10,'ItemUnit'
union all Select 11,'UnitCost'
union all Select 12,'UnitPrice'
union all Select 13,'TotalCost'       
union all Select 14,'TotalPrice'
union all Select 15,'DivisionID'
union all Select 16,'Divisionname'
union all Select 17,'CountyName'
union all Select 18,'QuoteID'
union all Select 19,'TotalFreight'
union all Select 20,'Markup'
union all Select 21,'Margin'
union all Select 22,'TotalBidPrice'
) as x
order by rn,seq
*/


Declare @Project id varchar(max),
@Projectname varchar(max),
@ProjectControlNumber varchar(max),
@ProjectCountyID varchar(max),
@ProjectNumber varchar(max),
@ProjectStartDate varchar(max),
@Item# varchar(max),
@ItemDescription varchar(max),
@ItemQuantity varchar(max),
@ItemUnit varchar(max),
@UnitCost varchar(max),
@UnitPrice varchar(max),
@TotalCost varchar(max),
@TotalPrice varchar(max),
@DivisionID varchar(max),
@Divisionname varchar(max),
@CountyName varchar(max),
@QuoteID varchar(max),
@TotalFreight varchar(max),
@Markup varchar(max),
@Margin varchar(max),
@TotalBidPrice varchar(max)

do while @i <= @rows
begin
  select @Project id= convert(varchar(max),Project id),
@Projectname= convert(varchar(max),Projectname),
@ProjectControlNumber= convert(varchar(max),ProjectControlNumber),
@ProjectCountyID= convert(varchar(max),ProjectCountyID),
@ProjectNumber= convert(varchar(max),ProjectNumber),
@ProjectStartDate= convert(varchar(max),ProjectStartDate),
@Item#= convert(varchar(max),Item#),
@ItemDescription= convert(varchar(max),ItemDescription),
@ItemQuantity= convert(varchar(max),ItemQuantity),
@ItemUnit= convert(varchar(max),ItemUnit),
@UnitCost= convert(varchar(max),UnitCost),
@UnitPrice= convert(varchar(max),UnitPrice),
@TotalCost= convert(varchar(max),TotalCost),
@TotalPrice= convert(varchar(max),TotalPrice),
@DivisionID= convert(varchar(max),DivisionID),
@Divisionname= convert(varchar(max),Divisionname),
@CountyName= convert(varchar(max),CountyName),
@QuoteID= convert(varchar(max),QuoteID),
@TotalFreight= convert(varchar(max),TotalFreight),
@Markup= convert(varchar(max),Markup),
@Margin= convert(varchar(max),Margin),
@TotalBidPrice= convert(varchar(max),TotalBidPrice),
        @i=@i+1  -- increment counter for next loop
    from #temp
    where rn=@i
    
    /* 
       do what you want
       
   */
   
   
   End -- end of while loop    
    
END

GO

Open in new window

Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER


errors
Msg 134, Level 15, State 1, Procedure DBSP_Report_GetProjectDetailCost2, Line 170
The variable name '@rows' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Procedure DBSP_Report_GetProjectDetailCost2, Line 230
Incorrect syntax near 'varchar'.
Msg 137, Level 15, State 2, Procedure DBSP_Report_GetProjectDetailCost2, Line 255
Must declare the scalar variable "@Project".


Actually i need to develop the reports from this proc according to the requirement

and the format is like this

projectname :ebid     project number :40
county name:allen    control number :30

projectname :primus     project number :10
county name:dallas   control number :20

:
:

:
projectname :dcc    project number 50
county name:irving    control number 10


will this stored proc help if not how can i modify proc
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
but normally this level of output formatting would be done in another "dedicated" reporting package....

have you considered using MS word (as a mailmerge... style of report)...
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

actually i have to use ssrs 2008 r2 for report formatting
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

got following error

Msg 208, Level 16, State 0, Procedure DBSP_Report_GetProjectDetailCost3, Line 283
Invalid object name '#temp'.
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

Actually ineed all the following fields since they are part of the reports

Projectname
ProjectControlNumber
ProjectCountyID
ProjectNumber
ProjectStartDate
Item#
ItemDescription
ItemQuantity
ItemUnit
UnitCost
UnitPrice
TotalCost
TotalPrice
DivisionID
Divisionname
CountyName
QuoteID
TotalFreight
Markup
Margin
TotalBidPrice
      
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image
just add to the case when ... in the select statement at the end to format your output lines as desired....


 Select  case v.number
               When 1 then 'projectname:'+convert(varchar(max),projectname)
                          +' project number:'+convert(varchar(max),projectnumber)
               when 2 then 'county name:'+convert(varchar(max),projectcountyid)
                          +' control number:'+convert(varchar(max),projectcontrolnumber)
               when 3 then ''
               end as Report
        ......
          and v.number between 1 and 3 -- adjust the 3 as required...
Avatar of sqlcurious
sqlcurious
Flag of United States of America image

ASKER

hello expert
i used your previous solution

thanks