Solved

need to implement cursor in stored proc

Posted on 2011-03-02
12
851 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:sqlcurious
  • 7
  • 5
12 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35021620
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

0
 

Author Comment

by:sqlcurious
ID: 35021700
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'.
0
 

Author Comment

by:sqlcurious
ID: 35021715
can you help me to implement do while loop in the stored procedure?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35021872
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

0
 

Author Comment

by:sqlcurious
ID: 35022010

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
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35022353
ok ...

then you don't need the while loop either,....

just add to the case when ... in the select statement at the end to format your output lines as desired....



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'

print @sql
exec (@sql)
 
/* 
select x.name as [Type],Case Seq 
When 1 then convert(varchar(max),Projectid)
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 @Projectid varchar(max),
@XProjectname varchar(max),
@XProjectControlNumber varchar(max),
@ProjectCountyID varchar(max),
@XProjectNumber 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),
@XDivisionname varchar(max),
@CountyName varchar(max),
@QuoteID varchar(max),
@TotalFreight varchar(max),
@Markup varchar(max),
@Margin varchar(max),
@TotalBidPrice varchar(max)

while @i <= @rows
begin
  select @Projectid= convert(varchar(max),Projectid),
@XProjectname= convert(varchar(max),Projectname),
@XProjectControlNumber= convert(varchar(max),ProjectControlNumber),
@ProjectCountyID= convert(varchar(max),ProjectCountyID),
@XProjectNumber= 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),
@XDivisionname= 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    
*/
  
  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
    from #temp
    cross join master.dbo.spt_values as v
    where v.type='p'
      and v.number between 1 and 3
    Order by rn,v.number
    
END

GO

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35022371
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)...
0
 

Author Comment

by:sqlcurious
ID: 35022505
actually i have to use ssrs 2008 r2 for report formatting
0
 

Author Comment

by:sqlcurious
ID: 35022527
got following error

Msg 208, Level 16, State 0, Procedure DBSP_Report_GetProjectDetailCost3, Line 283
Invalid object name '#temp'.
0
 

Author Comment

by:sqlcurious
ID: 35022579
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
      
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35022741
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...
0
 

Author Comment

by:sqlcurious
ID: 35037857
hello expert
i used your previous solution

thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now