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),@W HERE_DO1 VARCHAR(300)
set @WHERE_DO=' where dbo.OPSSOhdr.Or dDate <=CONVERT(DATET IME,LTRIM(RTRIM ('+'01 10 2006 '+')),107) AND (dbo.OPSSOhdr.P enalty=1 )'

print @WHERE_DO
select
dbo.OPSSOhdr.Jo bNo, dbo.OPSSOhdr.Or dDate, dbo.OPSSOhdr.Cu stId, dbo.OPSSOhdr.Cu stNm,dbo.OPSSOh dr.PrjDeptId, dbo.OPSSOhdr.Pr jIcId,dbo.OPSSO hdr.ordvalue,
dbo.OPSSOhdr.lp ono1, dbo.OPSSOhdr.lp odate1,dbo.OPSS Oitem.CmpnId, dbo.OPSSOitem.C mpnDesc, dbo.OPSSOitem.t otsoqty,
dbo.OPSSOitem.j obvalue,dbo.OPS SOitem.ItemSrln o,dbo.OPSDOMast .dono, dbo.OPSDOMast.d odate,
dbo.OPSDODet.Qt y , dbo.OPSDODet.Ra te, dbo.OPSDODet.[Value]
from
dbo.OPSSOitem inner join dbo.OPSSOhdr on dbo.OPSSOitem.J obno = dbo.OPSSOhdr.Jo bNo inner join dbo.OPSDOMast on dbo.OPSSOhdr.Jo bNo = dbo.OPSDOMast.j obno inner join
dbo.OPSDODet on dbo.OPSDOMast.d ono = dbo.OPSDODet.do no AND dbo.OPSSOitem.C mpnId = dbo.OPSDODet.Cm pnId + @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),@W HERE_DO1 VARCHAR(300)
set @WHERE_DO=' where dbo.OPSSOhdr.Or dDate <=CONVERT(DATET IME,LTRIM(RTRIM ('+'01 10 2006 '+')),107) AND (dbo.OPSSOhdr.P enalty=1 )'

print @WHERE_DO
select
dbo.OPSSOhdr.Jo bNo, dbo.OPSSOhdr.Or dDate, dbo.OPSSOhdr.Cu stId, dbo.OPSSOhdr.Cu stNm,dbo.OPSSOh dr.PrjDeptId, dbo.OPSSOhdr.Pr jIcId,dbo.OPSSO hdr.ordvalue,
dbo.OPSSOhdr.lp ono1, dbo.OPSSOhdr.lp odate1,dbo.OPSS Oitem.CmpnId, dbo.OPSSOitem.C mpnDesc, dbo.OPSSOitem.t otsoqty,
dbo.OPSSOitem.j obvalue,dbo.OPS SOitem.ItemSrln o,dbo.OPSDOMast .dono, dbo.OPSDOMast.d odate,
dbo.OPSDODet.Qt y , dbo.OPSDODet.Ra te, dbo.OPSDODet.[Value]
from
dbo.OPSSOitem inner join dbo.OPSSOhdr on dbo.OPSSOitem.J obno = dbo.OPSSOhdr.Jo bNo inner join dbo.OPSDOMast on dbo.OPSSOhdr.Jo bNo = dbo.OPSDOMast.j obno inner join
dbo.OPSDODet on dbo.OPSDOMast.d ono = dbo.OPSDODet.do no AND dbo.OPSSOitem.C mpnId = dbo.OPSDODet.Cm pnId + @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)