Solved

need to implement cursor in stored proc

Posted on 2011-03-02
12
857 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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
 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

14 Experts available now in Live!

Get 1:1 Help Now