SQL query

[venkey]

sub: how to concartenate variables in MS SQL2000 select....form...where<here>
hi,
This is venkey, can u see the following query. Actuly , i want to know how to Concatenate the local variable [subsitute of where caluse. ]
but i triyed the bellow of the query but it is not execut.
Plz can u solve my problem.
-----------------------------------------
DECLARE @WHERE_DO VARCHAR(300),@WHERE_DO1 VARCHAR(300)
set @WHERE_DO=' where dbo.OPSSOhdr.OrdDate <=CONVERT(DATETIME,LTRIM(RTRIM('+'01 10 2006 '+')),107) AND (dbo.OPSSOhdr.Penalty=1 )'

print @WHERE_DO
select
dbo.OPSSOhdr.JobNo, dbo.OPSSOhdr.OrdDate, dbo.OPSSOhdr.CustId, dbo.OPSSOhdr.CustNm,dbo.OPSSOhdr.PrjDeptId, dbo.OPSSOhdr.PrjIcId,dbo.OPSSOhdr.ordvalue,
dbo.OPSSOhdr.lpono1, dbo.OPSSOhdr.lpodate1,dbo.OPSSOitem.CmpnId, dbo.OPSSOitem.CmpnDesc, dbo.OPSSOitem.totsoqty,
dbo.OPSSOitem.jobvalue,dbo.OPSSOitem.ItemSrlno,dbo .OPSDOMast.dono, dbo.OPSDOMast.dodate,
dbo.OPSDODet.Qty , dbo.OPSDODet.Rate, dbo.OPSDODet.[Value]
from
dbo.OPSSOitem inner join dbo.OPSSOhdr on dbo.OPSSOitem.Jobno = dbo.OPSSOhdr.JobNo inner join dbo.OPSDOMast on dbo.OPSSOhdr.JobNo = dbo.OPSDOMast.jobno inner join
dbo.OPSDODet on dbo.OPSDOMast.dono = dbo.OPSDODet.dono AND dbo.OPSSOitem.CmpnId = dbo.OPSDODet.CmpnId + @WHERE_DO
---------------------------------------

let me know ur replay
thanks& regards
venkey
venkey

[azimmer]

sub: how to concartenate variables in MS SQL2000 select....form...where<here>
hi,
This is venkey, can u see the following query. Actuly , i want to know how to Concatenate the local variable [subsitute of where caluse. ]
but i triyed the bellow of the query but it is not execut.
Plz can u solve my problem.
-----------------------------------------
DECLARE @WHERE_DO VARCHAR(300),@WHERE_DO1 VARCHAR(300)
set @WHERE_DO=' where dbo.OPSSOhdr.OrdDate <=CONVERT(DATETIME,LTRIM(RTRIM('+'01 10 2006 '+')),107) AND (dbo.OPSSOhdr.Penalty=1 )'

print @WHERE_DO
select
dbo.OPSSOhdr.JobNo, dbo.OPSSOhdr.OrdDate, dbo.OPSSOhdr.CustId, dbo.OPSSOhdr.CustNm,dbo.OPSSOhdr.PrjDeptId, dbo.OPSSOhdr.PrjIcId,dbo.OPSSOhdr.ordvalue,
dbo.OPSSOhdr.lpono1, dbo.OPSSOhdr.lpodate1,dbo.OPSSOitem.CmpnId, dbo.OPSSOitem.CmpnDesc, dbo.OPSSOitem.totsoqty,
dbo.OPSSOitem.jobvalue,dbo.OPSSOitem.ItemSrlno,dbo .OPSDOMast.dono, dbo.OPSDOMast.dodate,
dbo.OPSDODet.Qty , dbo.OPSDODet.Rate, dbo.OPSDODet.[Value]
from
dbo.OPSSOitem inner join dbo.OPSSOhdr on dbo.OPSSOitem.Jobno = dbo.OPSSOhdr.JobNo inner join dbo.OPSDOMast on dbo.OPSSOhdr.JobNo = dbo.OPSDOMast.jobno inner join
dbo.OPSDODet on dbo.OPSDOMast.dono = dbo.OPSDODet.dono AND dbo.OPSSOitem.CmpnId = dbo.OPSDODet.CmpnId + @WHERE_DO
---------------------------------------

let me know ur replay
thanks& regards
venkey
venkey

In short: place the whole statement into a string and execute it.
Longer:

DECLARE @WHERE_DO VARCHAR(300)
declare @sqlstatement varchar(1024)
set @WHERE_DO=' where dbo.OPSSOhdr.OrdDate <=CONVERT(DATETIME,LTRIM(RTRIM('+'01 10 2006 '+')),107) AND (dbo.OPSSOhdr.Penalty=1 )' 
 
print @WHERE_DO 
set @sqlstatement = 'select ' 
set @sqlstatement = @sqlstatement + ' dbo.OPSSOhdr.JobNo, dbo.OPSSOhdr.OrdDate, dbo.OPSSOhdr.CustId, dbo.OPSSOhdr.CustNm,dbo.OPSSOhdr.PrjDeptId, dbo.OPSSOhdr.PrjIcId,dbo.OPSSOhdr.ordvalue, 
dbo.OPSSOhdr.lpono1, dbo.OPSSOhdr.lpodate1,dbo.OPSSOitem.CmpnId, dbo.OPSSOitem.CmpnDesc, dbo.OPSSOitem.totsoqty, 
dbo.OPSSOitem.jobvalue,dbo.OPSSOitem.ItemSrlno,dbo.OPSDOMast.dono, dbo.OPSDOMast.dodate, 
dbo.OPSDODet.Qty , dbo.OPSDODet.Rate, dbo.OPSDODet.[Value] 
from 
dbo.OPSSOitem inner join dbo.OPSSOhdr on dbo.OPSSOitem.Jobno = dbo.OPSSOhdr.JobNo inner join dbo.OPSDOMast on dbo.OPSSOhdr.JobNo = dbo.OPSDOMast.jobno inner join 
dbo.OPSDODet on dbo.OPSDOMast.dono = dbo.OPSDODet.dono AND dbo.OPSSOitem.CmpnId = dbo.OPSDODet.CmpnId '
set @sqlstatement = @sqlstatement + @WHERE_DO 
 
exec(@sqlstatement)