Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
vks_gautam
Starting Member
1 Post |
Posted - 2013-05-01 : 08:49:52
|
I just simply wan that .i want to search data with different criteria.one is from date and other is from itemid and so on.in last of procedure i used if else statement and im passing my variable "option" to select my search type.if i search by"date" it simple means i am not sending "itemid" value from my application to procedure .i want some advise about this how to get what i want.Option 1 Search by dateOption 2 Seatch by itemidand so onestored procedure PROCEDURE [dbo].[PROC_GET_SOH_REPORTOptions]( @ItemName VARCHAR(200) = '%', @To Date, @From Date, @ItemIdd int, @Option Int )ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; CREATE TABLE #tempSOH ( ItemID INT, ItemCode VARCHAR(50), ItemName VARCHAR(200), ItemLocation VARCHAR(50), created_date date, SOH NUMERIC(18,2) ) INSERT INTO #tempSOH ( ItemID, ItemCode, ItemName, ItemLocation,created_date ) SELECT Item_Master.Item_ID, Item_Master.Item_Code, Item_Master.Item_Name, Item_Location_Master.Rack_Name,Item_Master.Created_Date FROM Item_Master INNER JOIN Item_Location_Master ON Item_Master.Rack_ID = Item_Location_Master.Rack_ID WHERE Item_Master.Item_Name LIKE +'%'+ @ItemName +'%' UPDATE #tempSOH SET SOH = ISNULL((SELECT SUM( Stock_Quantity) FROM Stock_Header WHERE Stock_Type NOT IN ('Stock Out') AND Item_ID = #tempSOH.ItemID),0) UPDATE #tempSOH SET SOH = SOH - ISNULL((SELECT SUM( Stock_Quantity) FROM Stock_Header WHERE Stock_Type = 'Stock Out' AND Item_ID = #tempSOH.ItemID),0) if (@option)='1' SELECT ItemID, ItemName, ItemLocation, SOH,created_date FROM #tempSOH where created_date>=@From and created_date<=@to ORDER BY ItemName else if(@Option ='2') SELECT ItemID, ItemName, ItemLocation, SOH,created_date FROM #tempSOH where ItemID>=@ItemIdd ORDER BY ItemName DROP TABLE #tempSOH Vivek |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-01 : 08:53:51
|
The terminology that they use for what you are trying to do is "catch-all queries" or "dynamic search conditions". There are some really good articles that you should take a look at. One is from Sommarskog - it is very detailed and descriptive: http://www.sommarskog.se/dyn-search.html Another one, short and sweet is by Gail Shaw: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ |
|
|
|
|
|
|
|