Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

stored procedure help with if clauses

Posted on 2004-11-17
9
Medium Priority
?
193 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:jriver12
9 Comments
 
LVL 8

Expert Comment

by:SashP
ID: 12608501
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
 
LVL 10

Expert Comment

by:lengreen
ID: 12608522
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
 
LVL 15

Expert Comment

by:Colosseo
ID: 12608536
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Expert Comment

by:mjm42
ID: 12608541
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
 

Author Comment

by:jriver12
ID: 12608759
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
 
LVL 15

Expert Comment

by:danrosenthal
ID: 12608998
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 12609123
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
 

Author Comment

by:jriver12
ID: 12609148
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
 
LVL 15

Accepted Solution

by:
danrosenthal earned 1000 total points
ID: 12609243
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question