Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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  
 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.07 seconds. Powered By: Snitz Forums 2000