stored procedure help with if clauses

Trying to make sp that will dinamically ad clauses to the where.

here is what I got (I keep getting syntax errors, but I cant find it )
go
CREATE PROCEDURE SearchResearch (

@ProjNum nvarchar (4),
@ProjLetter nvarchar (50),
 @ProjTitle nvarchar (255),
  @ProjStatus nvarchar (255),
  @ProjCode nvarchar (255)
)
AS
SET @ProjNum = @ProjNum + '%'
SET @ProjLetter = @ProjLetter + '%'
SET @ProjTitle = @ProjTitle + '%'
SET @ProjStatus = @ProjStatus + '%'
SET @ProjCode = @ProjCode + '%'

SELECT     ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects

where 0=0


IF (@projnum != ' ' )
begin
and ProjNum like '@ProjNum'
end
IF (@projletter != ' ' )
begin
and Projletter like '@Projletter'
end
IF (@projnum != ' ' )
begin
and Projtitle like '@Projtitle'
end
help me .

thanks in advance.
jriver12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SashPCommented:
Hi jriver12

IF is only used for branching execution not is SQL Queries you need the case statement

Also @ProjNum !=' ' will never be true for a varchar or nvarchar as SQL trims it.

CREATE PROCEDURE SearchResearch (

@ProjNum nvarchar (4),
@ProjLetter nvarchar (50),
 @ProjTitle nvarchar (255),
  @ProjStatus nvarchar (255),
  @ProjCode nvarchar (255)
)
AS

SELECT     ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects

where
       (ProjNum like @ProjNum + '%' and @ProjNum !='')
and    (ProjLetter like @ProjLetter + '%' and @ProjLetter !='')
and    (ProjTitle like @ProjTitle + '%' and @ProjTitle !='')
and    (ProjStatus like @ProjStatus + '%' and @ProjStatus !='')
and    (ProjCode like @ProjCode + '%' and @ProjCode !='')



Cheers Sash
0
lengreenCommented:
Hi

This should do the trick

cheers

Len


CREATE PROCEDURE SearchResearch (

@ProjNum nvarchar (4),
@ProjLetter nvarchar (50),
 @ProjTitle nvarchar (255),
  @ProjStatus nvarchar (255),
  @ProjCode nvarchar (255)
)
AS
SET @ProjNum = @ProjNum + '%'
SET @ProjLetter = @ProjLetter + '%'
SET @ProjTitle = @ProjTitle + '%'
SET @ProjStatus = @ProjStatus + '%'
SET @ProjCode = @ProjCode + '%'

Declare @SQl nvarchar(4000)

set @SQl=' SELECT     ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects where 0=0

'


IF @projnum <> '%'
begin
set @SQl=@SQl' and ProjNum like ''' + @ProjNum + ''''
end
IF @projletter <> '%'
begin
set @SQl=@SQl' and Projletter like ''' + @Projletter + ''''
end
IF @Projtitle <> '%'
begin
set @SQl=@SQl' and Projtitle like ''' + @Projtitle+ ''''
end

exec(@SQl)
0
ColosseoCommented:
Hi

you dont need the if statements for what you are trying to do you can just use

SELECT     ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects
WHERE 0=0 and ProjNum like @ProjNum and Projletter like @Projletter and Projtitle like @Projtitle

This will work because for example when @ProjNum is an empty string it will actually be %

so this part of the where clause will be ProjNum like % which returns all records

Hope that makes sense

Scott
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mjm42Commented:
Maybe you can try:

SELECT     ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects

WHERE ProjNum like '@ProjNum'
and Projletter like '@Projletter'
and Projtitle like '@Projtitle'


If one of the variables is null you will get all the results for that variable because you ad the %. If it has a value the query will process the like clause

0
jriver12Author Commented:
I guess I need to expand
this sp is going to be called by coldfusion.
the original code is used to do an adhoc query from a web form,
this gave the user and instant drill down to return records only for the fields that he filled in.

the additional and clauses where only triggered if something was populated.

the suggestion above would not return correct recordset because of the % returning everything within that field if nothing was passed on the form. (?)
Here is the code.

<cfquery name="test" datasource="#application.dsn#">
SELECT     ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects
where 0=0

<CFIF ProjNum NEQ "">
and ResearchProjects.ProjNum like '#ProjNum#'
</cfif>
      
<CFIF ProjLetter NEQ "">
and ResearchProjects.ProjLetter like '#ProjLetter#%'
</cfif>
      
<CFIF ProjTitle NEQ "">
and ResearchProjects.ProjTitle like '%#ProjTitle#%'
</cfif>

<CFIF ProjStartDate NEQ "">
and ResearchProjects.ProjStartDate like '#ProjStartDate#%'
</cfif>

<CFIF ProjEndDate NEQ "">
and ResearchProjects.ProjEndDate like '#ProjEndDate#%'
</cfif>

<CFIF ProjStatus NEQ "">
and ResearchProjects.ProjStatus like '#ProjStatus#%'
</cfif>

<CFIF ProjCode NEQ "">
and ResearchProjects.ProjCode like '#ProjCode#%'
</cfif>



</cfquery>

so I am trying to convert long code to be done by the sql db vs the websvr.

can we still help.
0
danrosenthalCommented:
Try using the ISNULL() function as follows:

CREATE PROCEDURE SearchResearch
@ProjNum nvarchar (4) = NULL,
@ProjLetter nvarchar (50) = NULL,
@ProjTitle nvarchar (255) = NULL,
@ProjStatus nvarchar (255) = NULL,
@ProjCode nvarchar (255) = NULL

AS

SELECT       ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects
where 0=0
AND (ProjNum = ISNULL(@ProjNum,ProjNum) OR ProjNum like @ProjNum + '%')
AND (Projletter = ISNULL(@Projletter,Projletter) OR Projletter like @Projletter + '%')
AND (Projtitle = ISNULL(@Projtitle,Projtitle) OR Projtitle like @Projtitle + '%')

0
Scott PletcherSenior DBACommented:
For efficiency reasons, you should use this style of checking the variables for a value:


SELECT     ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM         ResearchProjects
WHERE (@projnum = ' '  OR ProjNum LIKE @ProjNum + '%')
AND (@projletter = ' '  OR @projletter LIKE @Projletter + '%')
AND (@projnum = ' ' OR Projtitle LIKE @Projtitle + '%')
0
jriver12Author Commented:
still returning incorrect recordset.

I should be receiving about 100 rcds when only the projnum formfield is passed.
however all the codes above are still passign the undefined %.

this is still the same issue.

thanks tho'
0
danrosenthalCommented:
What do you mean by "all the codes above are still passing the undefined %" ?

What are you passing in to the Stored Proc?

Does the following work? (different from my last post):

CREATE PROCEDURE SearchResearch
@ProjNum nvarchar (4) = '',

AS

SELECT      ProjectID,ProjNum, ProjLetter, ProjTitle, ProjStatus, ProjCode
FROM        ResearchProjects
where 0=0
AND (@ProjNum = '' OR ProjNum like @ProjNum + '%')

Run this the following 2 ways and compare results:
1. EXEC SearchResearch ''

2. EXEC SearchResearch '1000'

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.