Re: Parameters or no parameters - CASE statements

From: Alan (ambradnum_at_hotmail.com)
Date: 05/09/04 

Date: 9 May 2004 07:21:36 -0700

Hi Chris

If this query is to be used in a stored procedure then
this example should help: 

---
create procedure spExample(@name varchar(50))
declare @myname varchar(51)
if @name is null
  select @myname='%'
 else
  select @myname=@name
select *
from mytable
where name=@myname
go
---
Hope this helps
Alan
"Not Me" <Not.Me@faker.fake.fa.kee> wrote in message news:<c7d9pk$ujl$1@ucsnew1.ncl.ac.uk>...
> Hi,
> 
> I would like to build a query that would take a number of parameters which
> will be used for search clauses. What I would like tho is that if the
> parameters are not given then no effect is had on the results. For example
> if given the parameter @name I would like 'where name = @name' and if @name
> was blank then i'd like either nothing, or just name = '%'.
> 
> I've tried various ways of putting case statements in but keep getting
> syntax errors.. where exactly are you allowed to use cases?
> 
> Cheers,
> Chris

Relevant Pages

  • Re: return a value using MDX
    ... I created a stored procedure to this query. ... it is looking for @sql as well. ... DECLARE @sql varchar ...
    (microsoft.public.sqlserver.olap)
  • Re: return a value using MDX
    ... I created a stored procedure to this query. ... it is looking for @sql as well. ... DECLARE @sql varchar ...
    (microsoft.public.sqlserver.olap)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... > Noiseword varcharNot Null ... > the data from the noise file to the noise_words table. ... >> A clause of the query contained only ignored words. ... >> into query analyzer before starting the stored procedure. ...
    (microsoft.public.sqlserver.fulltext)
  • RE: ODBC query in VB code Need HELP
    ... I am trying to get a stored procedure to run on info I send it I do not need ... I played around and got the code below to work using a pass-through query, ... Dim strpass As String ...
    (microsoft.public.access.formscoding)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
Loading