how to use < or > in sp condition dynamically

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 2005 Forums

Transact-SQL (2005)

how to use < or > in sp condition dynamically

Author

Topic

lleemon
Starting Member

24 Posts

Posted - 2011-06-03 : 18:04:37

I have a stored procedure that I am using to pass several variables from a form to search a few tables to get data. I would like allow the user to select the operator plus enter a value to further filter results.

For example, allow them to search a field for values greater then 10:
Size > 10

I would want to pass in the '>' and the value '10' into the following sp.

Here is my sp:



ALTER PROCEDURE [dbname].[sp_Search]
	@vDESC varchar(150),
	@vAVLQTY varchar(30) = '',
	@vOVRSZ varchar(3) = ''
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @AvDESC_V2 varchar(152)
	
	SET @vDESC_V2 = '%' + @vDESC + '%'

	SELECT 
	[id]
      ,[itemno], [desc]
      ,[entry_dt]
      ,[modified_dt]
	FROM maindatatable
	WHERE desc like @vDESC_V2
	AND ((@vAVLQTY = '') OR (AVLQTY = @vAVLQTY))
	AND ((@vOVRSZ = '') OR (OVRSZ = @vOVRSZ))
END

Is there a good way of doing this without creating dynamic sql?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 18:19:04

You can add additional conditions in the where clause, for example:


   AND
   (
      ( @OPCODE = '>' AND ColValue > @CutoffValue )
      OR
      ( @OPCODE = '<' AND ColValue < @CutoffValue )
   )

lleemon
Starting Member

24 Posts

Posted - 2011-06-03 : 22:42:50

sunitabeck - thank you very much. Exactly what I was looking for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources