Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql Server Stored Procedure varaible declaration Expand / Collapse
Author
Message
Posted Wednesday, May 01, 2013 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 7:44 AM
Points: 4, Visits: 3
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]
(
@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

Post #1448401
Posted Wednesday, May 01, 2013 7:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1448409
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse