SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sql Server Stored Procedure varaible declaration
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vks_gautam
Starting Member

India
1 Posts

Posted - 05/01/2013 :  08:49:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 05/01/2013 :  08:53:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000