May 1, 2013 at 7:02 am
i want to search data with different criteria.
one is from "date" and other is from itemid and so on.
in last part of procedure i used if else statement and im passing my variable "option" to select my search criteria.
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 date
Option 2 Seatch by itemid
and so one
PROCEDURE [dbo].[PROC_GET_SOH_REPORTOptions]
(
@ItemNameVARCHAR(200) = '%',
@To Date,
@From Date,
@ItemIdd int,
@Option Int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #tempSOH
(
ItemIDINT,
ItemCodeVARCHAR(50),
ItemNameVARCHAR(200),
ItemLocationVARCHAR(50),
created_date date,
SOHNUMERIC(18,2)
)
INSERT INTO #tempSOH
(
ItemID, ItemCode, ItemName, ItemLocation,created_date
)
SELECTItem_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
[/Code]
May 1, 2013 at 7:22 am
One or both of these should help
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply