6 Replies - 6871 Views - Last Post: 06 May 2013 - 04:30 PM Rate Topic: -----

#1 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Sql Server Stored Procedure varaible declaration

Posted 01 May 2013 - 01:37 AM

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
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)
				
		
			
		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		
				



Is This A Good Question/Topic? 0
  • +

Replies To: Sql Server Stored Procedure varaible declaration

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9570
  • View blog
  • Posts: 36,242
  • Joined: 12-June 08

Re: Sql Server Stored Procedure varaible declaration

Posted 01 May 2013 - 06:54 AM

That means, in your procedure, you need to determine which variable (coming in) has a value, and build your 'where' statement accordingly.
Was This Post Helpful? 0
  • +
  • -

#3 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Sql Server Stored Procedure varaible declaration

Posted 01 May 2013 - 07:03 AM

well on my form i have button with search textbox..and few radionbuttons.. everyone radio button have different criteria for query ..like rb1=date ,rb2=item_name and so on..this is what i found in internet dynamic sql.its working fine..just need little bit help to understand one thing.
PROCEDURE [dbo].[SearchHistory_Dynamic1] (@item_id int = NULL, @item_name varchar(200) = NULL)
AS
DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
SET @sSQL = 'select item_name,item_id from item_master '
 
IF @item_id is not null
SET @Where = @Where + 'AND item_id = @_item_id '
IF @item_name is not null
SET @Where = @Where + 'AND item_name = @_item_name '

 
IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)
 
EXEC sp_executesql @sSQL,
N'@_item_id int, @_item_name varchar(200)',
@_item_id = @item_id, @_item_name= @item_name



what does thins means "RIGHT(@Where, LEN(@Where)-3)"
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9570
  • View blog
  • Posts: 36,242
  • Joined: 12-June 08

Re: Sql Server Stored Procedure varaible declaration

Posted 01 May 2013 - 07:05 AM

MSSQL: RIGHT


RIGHT(@Where, LEN(@Where)-3)

RIGHT takes in a string, and returns the right most characters for some determined length. In this case it takes the right most characters of your variable '@WHERE' for the number of characters that is the length of @WHERE minus three.

So if @WHERE was 'ABC123', the number of characters is 6-3, or 3. It would return the three right most characters of 'ABC123', or '123'.
Was This Post Helpful? 1
  • +
  • -

#5 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 294
  • View blog
  • Posts: 1,532
  • Joined: 07-April 08

Re: Sql Server Stored Procedure varaible declaration

Posted 02 May 2013 - 02:16 PM

Ok, so why not do this. Set a default value on both of your parameters that are optional of null, then check in the where clause like so:

WHERE (item_name = @_item_name OR @_item_name IS NULL)
      AND (item_id = @_item_id OR @_item_id IS NULL)



This gives you the same result that you get from the dynamic SQL, looks cleaner, is easier to maintain, and can be optimized
Was This Post Helpful? 1
  • +
  • -

#6 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Sql Server Stored Procedure varaible declaration

Posted 03 May 2013 - 10:08 AM

View Postrgfirefly24, on 02 May 2013 - 02:16 PM, said:

Ok, so why not do this. Set a default value on both of your parameters that are optional of null, then check in the where clause like so:

WHERE (item_name = @_item_name OR @_item_name IS NULL)
      AND (item_id = @_item_id OR @_item_id IS NULL)



This gives you the same result that you get from the dynamic SQL, looks cleaner, is easier to maintain, and can be optimized

Due to this my friend.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


Was This Post Helpful? 0
  • +
  • -

#7 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Sql Server Stored Procedure varaible declaration

Posted 06 May 2013 - 04:30 PM

So in order to avoid a case statement, which some guy on the internet says is bad, you're willing to:
  • Create a stored procedure whose execution plan can't be globally cached
  • Create a temporary table and insert values into it, thereby invoking unnecessary memory-usage overhead, and also losing all index benefits
  • Run an UPDATE across that entire temporary table
  • Use a NOT IN clause, and use it against a single value to boot
  • Declare @Option as an int, then compare it against a string
  • Utilise an if..else block to conditionally return rows on that table
  • ...and/or turn to using dynamically generated SQL, which is notoriously hard to debug, and also suffers from point #1

I feel you are approaching this in entirely the wrong manner. The simplest solution is often the best when it comes to SQL. Sure, sometimes you need to use tricks, but for the most part, the shortest and easiest to understand SQL code is the code that works best.

If I were faced with your problem, I would write the stored procedure so:
CREATE PROCEDURE [dbo].[PROC_GET_SOH_REPORTOptions]
(
	@ItemName nvarchar(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;
	
	DECLARE @pItemName AS nvarchar(200) = '%' + @ItemName + '%';
	DECLARE @pTo AS date = @To;
	DECLARE @pFrom AS date = @From;
	DECLARE @pItemIdd AS int = @ItemIdd;
	DECLARE @pOption AS int = @Option;
	
	SELECT
		Item_Master.Item_ID AS ItemID,
		Item_Master.Item_Code AS ItemCode,
		Item_Master.Item_Name AS ItemName,
		Item_Location_Master.Rack_Name AS ItemLocation,
		Item_Master.Created_Date AS created_date,
		ISNULL(Stock.Quantity, 0) AS SOH
	FROM Item_Master
	LEFT OUTER JOIN (
		SELECT SUM(Stock_Quantity) AS Quantity
		FROM Stock_Header
		WHERE Stock_Type <> 'Stock Out'
		AND Item_ID = Item_Master.ItemID
	) AS Stock
	INNER JOIN Item_Location_Master
		ON Item_Master.Rack_ID = Item_Location_Master.Rack_ID	
	WHERE Item_Master.Item_Name LIKE @ItemName 
	AND (
		(
			@Option = 1
			AND created_date BETWEEN @From AND @to
		) OR (
			@Option = 2
			AND ItemID >= @ItemIdd
		)
	)
	ORDER BY Item_Master.Item_Name
END
GO


The first thing you should note about this code is that it is easy to read. Indenting aside, it is easy to see what is going on - you don't have an operation that includes insert, updates, and if..else blocks. All the logic and intention is captured in the one query, with the business logic all being held in the predicate block (WHERE ...).

Secondly, there is a trick I have used here in the initial block of the stored procedure. I have declared variables within the stored procedure and assigned them the values of the incoming variables. This is to circumvent an issue with MSSQL stored procedures and variable usage - if you use the parameter variable by itself, the stored procedure runs extremely slow, whereas if you redeclare the variable inside the stored procedure, it speeds up immensely (up to 90% faster). This is an MSSQL issue, and a trick to get around it - you aren't expected to know these things - I learnt after 2 years of being a DB professional.

Thirdly, I have put the @Option logic in the WHERE clauses, rather than conditionally return rows. I have found this to be an excellent method, as it ensures that the query is character-for-character equivalent every time the stored procedure runs. Because of this, the execution plan gets cached globally, which means different clients / pool connections can run the same stored procedure and not have to regenerate the execution plan each time. Complex execution plans can account for 50-75% of a total query's execution time.

A couple of things I should note here - the query is likely to run slowly due to the LIKE '%...%' clause. Doing a LIKE against a string beginning with a wild card subverts any index on that field. In other words - you may as well not index that field, as the index will never get utilised. As far as I know, even a fulltext index won't help here, as a fulltext index indexes on words, not on partial strings.

If you are going to index up your tables to improve performance of this query, I would suggest the following indexes (you can create these in SSMS, or write queries to implement them):
Index on Item_Master - Item_ID,Rack_ID,Item_Name,Created_Date (include Item_Code)
Index on Stock_Header - Item_ID,Stock_Type (include Stock_Quantity)
Index on Item_Location_Master - Rack_ID (include Rack_Name)

One last point - is there a reason why you are using Item_ID >= @ItemIdd? It seems odd that you would search for an Item_ID >=, rather than =.

This post has been edited by e_i_pi: 06 May 2013 - 04:32 PM

Was This Post Helpful? 2
  • +
  • -

Page 1 of 1