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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sql Server Stored Procedure varaible declaration

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 date
Option 2 Seatch by itemid
and so one



stored procedure
PROCEDURE [dbo].[PROC_GET_SOH_REPORTOptions]
(
@ItemName VARCHAR(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
(
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/
Go to Top of Page
   

- Advertisement -