Is this query possible?

[ssv45324]

My goal is to change the where clause depending on a variable.

For example if @value > 0 my where should be

sampletable.column1 = 1 and sampletable.column2 = @value2

If @value = 0 my where should be

sampletable.column1 = 1 and sampletable.column2 > 0


I am looking for something like

declare @value as integer
set @value = 1

select * from sampletable where sampletable.column1 = 1 and
sampletable.column2 = case
when @value>1 then @value
else > 0
end

 

[vongrunt]

Maybe this:

select * 
from sampletable 
where column1 = 1 and
	((@value>0 and column2 = @value2) or (@value=0 and column2 > 0))

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[belovedcej]

Check out select case statements - I haven't used them yet, but they look like they might do what you want.

Another possibility:

have string variables that build your query and then execute it at the end - here's something I did that you might be able to tweak for your query:

ALTER PROCEDURE dbo.CSF_Search_Claims_SP

	@Claim_Number varchar (50),
	@Last_Name varchar (50)
	
		

As

Set @Claim_Number = IsNull(@Claim_Number, ' ')
Set @Last_Name = IsNull(@Last_Name, ' ')

DECLARE @Query varchar (500)

	If Not(@Claim_Number=' ')	
	   Begin

	     	SET @Query = 
	     	'SELECT *
	     	FROM  dbo.CSF_People_Per_Claim_V
	     	WHERE  dbo.CSF_People_Per_Claim_V.Claim_Number  = @Claim_Number'

	   End

	Else
	   Begin
	
	      	SET @Query = 
		'SELECT  *
		FROM bo.CSF_People_Per_Claim_V
		WHERE dbo.CSF_People_Per_Claim_V.Last_Name Like ''%'+  @Last_Name+  '%'''


	   End


EXEC (@Query)