April 22, 2011 at 10:59 pm
Declare @cCompanyNo nvarchar (5),
@cStockRoomCode nvarchar(10),
@cFromItemCode nvarchar(32),
@cToItemCode nvarchar(32),
@cFromSpec1 nvarchar(100),
@cToSpec1 nvarchar(100),
@cFromSpec2 nvarchar(100),
@cToSpec2 nvarchar(100),
@cFromSpec3 nvarchar(100),
@cToSpec3 nvarchar(100),
@cBarcode nvarchar(40),
@cFromStyle nvarchar(20),
@cToStyle nvarchar(20),
@cClass1 nvarchar(max),
@cClass2 nvarchar(max),
@cClass3 nvarchar(max),
@cClass4 nvarchar(max),
@cClass5 nvarchar(max),
@cClass6 nvarchar(max),
@cClass7 nvarchar(max),
@cClass8 nvarchar(max),
@cClass9 nvarchar(max),
@cClass10 nvarchar(max)
select @cCompanyNo = 'PR' , @cStockRoomCode = 'G08', @cFromItemCode = '',
@cToItemCode = '',@cFromSpec1 = '', @cToSpec1 = '',
@cFromSpec2 = '', @cToSpec2 = '', @cFromSpec3 ='' ,
@cToSpec3 = '', @cBarcode = '',@cFromStyle = '',
@cToStyle = '',@cClass1 = '',@cClass2 = '',
@cClass3 = '',@cClass4 = '',@cClass5 ='',
@cClass6 = '',@cClass7 = '',@cClass8 = '',
@cClass9 = '',@cClass10 = ''
Select im.cCompanyNo,
'StockCountNumber' as nStockCountNumber,
IM.cItemCode,
im.cBarCode,
srb.cStockroomCode,'' as cLotBinSerialNo,
0.00 as nRecordQTY,
0.00 as nActualQTY,
0.00 as nRecordValue,
0.00 as nActualValue,
0.00 as nPercError,
'' as cStatus,
im.cStockUnit as cUOMCode,
GETDATE() as dStockCountDate,
'' as cUserID,
'' as cPCID,
isNull((Select convert(nvarchar,c.nCurrentYear) + '0'+ convert(nvarchar,c.nCurrentPeriod) from CalendarFile c where c.cCompanyNo = im.cCompanyNo),'') as nYearPeriod,
'' as cCompleted,
getdate() as dDateLastChanged,
'' as COST,
newid() as rowguid
From ItemMaster IM
inner join StockRoomBalance SRB
on
(
im.cCompanyNo = srb.ccompanyNo
and im.citemCode = srb.citemcode
)
where im.cCompanyNo = @cCompanyNo
and srb.cStockRoomCode = @cStockRoomCode
and IM.cItemCode like case when @cFromItemCode = '' then '%' else @cFromItemCode end
and IM.cItemCode like case when @cToItemCode = '' then '%' else @cToItemCode end
and IM.cItemCode like case when @cBarcode = '' then '%' else ( select top 1 convert(nvarchar,cItemCode) from vw_BarCodes where cCompanyNo = @cCompanyNo and BarCode = @cBarcode) end
and im.vSpec1 >= case when @cFromSpec1 = '' then '%' else @cFromSpec1 end
and im.vSpec1 <= case when @cToSpec1 = '' then true else @cToSpec1 end
and im.vSpec2 >= case when @cFromSpec2 = '' then '%' else @cFromSpec1 end
and im.vSpec2 <= case when @cToSpec2 = '' then '%' else @cToSpec1 end
and im.vSpec3 >= case when @cFromSpec3 = '' then '%' else @cFromSpec1 end
and im.vSpec3 <= case when @cToSpec3 = '' then '%' else @cToSpec1 end
and im.cStyle <= case when @cFromStyle = '' then '%' else @cFromStyle end
and im.cStyle >= case when @cToStyle = '' then '%' else @cToStyle end
and IM.cClass1 in ( case when @cClass1 = '' then '%' else (select * from dbo.getTableFromString(@cClass1)) end )
and IM.cClass2 in ( case when @cClass2 = '' then '%' else (select * from dbo.getTableFromString(@cClass2)) end )
and IM.cClass3 in ( case when @cClass3 = '' then '%' else (select * from dbo.getTableFromString(@cClass3)) end )
and IM.cClass4 in ( case when @cClass4 = '' then '%' else (select * from dbo.getTableFromString(@cClass4)) end )
and IM.cClass5 in ( case when @cClass5 = '' then '%' else (select * from dbo.getTableFromString(@cClass5)) end )
and IM.cClass6 in ( case when @cClass6 = '' then '%' else (select * from dbo.getTableFromString(@cClass6)) end )
and IM.cClass7 in ( case when @cClass7 = '' then '%' else (select * from dbo.getTableFromString(@cClass7)) end )
and IM.cClass8 in ( case when @cClass8 = '' then '%' else (select * from dbo.getTableFromString(@cClass8)) end )
and IM.cClass9 in ( case when @cClass9 = '' then '%' else (select * from dbo.getTableFromString(@cClass9)) end )
and IM.cClass10 in ( case when @cClass10 = '' then '%' else (select * from dbo.getTableFromString(@cClass10)) end )
i want to select all the records if the given parameter is '' or if its blank. my intension is to genarate the records without using dynamic sql command. please help me
Thanks in advance
April 23, 2011 at 4:37 am
First of all it seems like it's a "catch-all-query"[/url] type.
I'm confident this query will (almost) always end up using a table scan...
Furthermore, what do consider being the difference between parameter is '' or if its blank?
The usage of nvarchar(max) is another issue: is it really required or can you reduce it to nvarchar(4000) or even varchar(8000)? Speaking of unicode: why do you need to convert c.nCurrentYear into a unicode format?
What's the code of the function dbo.getTableFromString()? Is it already an iTVF?
I would also consider normalizing the table (cClass and vSpec in separate rows, not columns). This would also make the query much easier.
April 23, 2011 at 7:09 am
Hi Lutz,
first of all I’m really happy to see a reply to my post. This is the second post which I drafted and for the first post I rally didn’t get any reps 🙁
Millions of thanks.
yes it’s kind of catch all type quarry.
I’m not too sure whether it will do a table scans because those tables are extracted from a live system. And those tables filled with several indexes as well but i assume those indexes are also not properly planned and created.
I know those table's are not normalized properly but now it’s too late to do so. CZ if I do so then my developers might have to do lot of cord work hence i have to live with it
what i want is when those parameters not containing a values i need to select all the recorder’s which is matching the other conditions
Such as @cCompanyNo nvarchar (5),
@cStockRoomCode nvarchar(10),
@cFromItemCode nvarchar(32),
@cCompanyNo = 'XY'
@cStockRoomCode = 'CB'
suppose if @cFromItemCode does not contain any value or if its set to null i want to select all the
records which is matching
company xy and stockroom cb
"The usage of nvarchar(max) is another issue: is it really required or can you reduce it to nvarchar(4000) or even varchar(8000)? "
yes i can reduce it up to varchar(2000) no issue's
"Speaking of Unicode: why do you need to convert c.nCurrentYear into a Unicode format?"
in this case i think i have to do so because in the system it saved nCurrentYear as integer value ex 2011 and nCurrentPeriod allso the same ex 2, my expected output is a string ex '201102' so far i don’t have much experience in sql so this is the only way i know to get my expected output.
dbo.getTableFromString() this is a small function which will return a table when we input comma separated string. even though in sql 2008 supporting list values i cannot go for it because i have another company which is on sql 2005 and im not comfortable with keeping 2 source codes for two companies.
Thanks again.
April 25, 2011 at 6:06 am
Why is dynamic SQL not an option here?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 25, 2011 at 2:56 pm
Lutz has a link to it in his post, but links are not easily visible in this forum so in case you missed it you can read this article: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
It is THE article to read to get up to speed on the definition of "catch-all queries", the T-SQL methods you can use to solve the problem, and some solid insight on which method might be best for you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply