Ongoing problems building an SP

# Thread: Ongoing problems building an SP

1. Senior Member
Join Date
Dec 1969
Posts
604

## Ongoing problems building an SP

I&#039;m having problems with putting a Where clause in an SP<BR><BR>It runs fine in ASP like this...<BR><BR>WhereClause = "WHERE "<BR>If strTerm &#060;&#062; "" Then<BR> WhereClause = WhereClause & "CharIndex(&#039;"&strTerm & "&#039;,Jobsought)&#062;0 OR " & _<BR> "CharIndex(&#039;" & strTerm & "&#039;,CV)&#062;0" <BR>End If<BR><BR> If strWorkPermit &#060;&#062;"" Then <BR> WhereClause = WhereClause & " AND Workpermit = &#039;"&strWorkPermit&"&#039;"<BR> End if<BR><BR> If strSalary &#060;&#062;"" Then <BR> WhereClause = WhereClause & " AND SalarySought &#060;= &#039;"&intSalary&"&#039;"<BR> End if<BR><BR> If strFtOrPt &#060;&#062;"" Then <BR> WhereClause = WhereClause & " AND FtOrPt = &#039;"&intFtOrPt&"&#039;"<BR> End if<BR><BR> If strCat &#060;&#062;"" Then <BR> WhereClause = WhereClause & " AND Category = &#039;"&strCat&"&#039;"<BR> End if<BR><BR>WhereClause = WhereClause & " AND Status NOT LIKE &#039;%ot looking%&#039; ORDER BY Entrydate DESC"<BR><BR>strSQL = "SELECT * FROM Candidetails " & WhereClause<BR><BR><BR>I want to put it into an SP, but I&#039;m having trouble...<BR><BR>I&#039;ve inserted it into the SP, as below... Is it in the right place? And the syntax seems to be different for an SP - any corrections welcome!<BR><BR><BR>CREATE PROCEDURE mp_PagedCandSearch<BR> (<BR> @Page int,<BR> @RecsPerPage int,<BR> @strTerm varchar(250),<BR> @WhereClause varchar(250),<BR> @strWorkPermit varchar(50),<BR> @intSalary int,<BR> @intFtOrPt int,<BR> @strCat varchar(50)<BR> )<BR>AS<BR><BR>-- We don&#039;t want to return the # of rows inserted<BR>-- into our temporary table, so turn NOCOUNT ON<BR>SET NOCOUNT ON<BR><BR><BR>@WhereClause = "WHERE "<BR>If @strTerm &#060;&#062; "" Then<BR> @WhereClause = @WhereClause & "CharIndex("&@strTerm & ",Jobsought)&#062;0 OR " & _<BR> "CharIndex(&#039;" & @strTerm & "&#039;,CV)&#062;0" <BR>End If<BR> If @strWorkPermit &#060;&#062;"" Then <BR> @WhereClause = @WhereClause & " AND Workpermit = &#039;"&@strWorkPermit&"&#039;"<BR> End if<BR><BR> If @strSalary &#060;&#062;"" Then <BR> @WhereClause = @WhereClause & " AND SalarySought &#060;= &#039;&@intSalary&&#039;"<BR> End if<BR><BR> If @strFtOrPt &#060;&#062;"" Then <BR> @WhereClause = @WhereClause & " AND FtOrPt = &#039;"&@intFtOrPt&"&#039;"<BR> End if<BR><BR> If @strCat &#060;&#062;"" Then <BR> @WhereClause = @WhereClause & " AND Category = &#039;"&@strCat&"&#039;"<BR> End if<BR><BR>@WhereClause = @WhereClause & " AND Status NOT LIKE &#039;%ot looking%&#039; ORDER BY Entrydate DESC"<BR><BR> <BR><BR>--Create a temporary table<BR>CREATE TABLE #TempItems<BR>(<BR> ID int IDENTITY,<BR> CandidateID int,<BR> Jobsought text,<BR> WorkPermit varchar(50),<BR> FtOrPt varchar(50),<BR> SalarySought int,<BR> Category varchar(50),<BR> Status varchar(50),<BR> Entrydate smalldatetime,<BR> CV text<BR> <BR>)<BR><BR>-- Insert the rows from tblItems into the temp. table<BR>INSERT INTO #TempItems (CandidateID, Jobsought, WorkPermit, FtOrPt, SalarySought, Category, Status, CV, Entrydate) <BR>SELECT CandidateID, Jobsought, WorkPermit, FtOrPt, SalarySought, Category, Status, CV, Entrydate FROM Candidetails<BR>+ &#039; &#039; +@WhereClause<BR><BR><BR>-- Find out the first and last record we want<BR>DECLARE @FirstRec int, @LastRec int<BR>SELECT @FirstRec = (@Page - 1) * @RecsPerPage<BR>SELECT @LastRec = (@Page * @RecsPerPage + 1)<BR><BR>-- Now, return the set of paged records, plus, an indication of we<BR>-- have more records or not!<BR>SELECT *,<BR> MoreRecords = <BR> (<BR> SELECT COUNT(*) <BR> FROM #TempItems TI<BR> WHERE TI.ID &#062;= @LastRec<BR><BR> ) <BR>FROM #TempItems<BR>WHERE ID &#062; @FirstRec AND ID &#060; @LastRec <BR><BR>SELECT Count(#TempItems.CandidateID) AS CountOfCandidateID<BR>FROM #TempItems<BR><BR>-- Turn NOCOUNT back OFF<BR>SET NOCOUNT OFF<BR>GO<BR>

2. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## RE: Ongoing problems building an SP

use + instead of &<BR><BR>use &#039; instead of " (as a general rule)<BR><BR>&#062;SELECT * FROM Candidetails " & WhereClause<BR>should be <BR>"SELECT * FROM Candidetails " + @WhereClause<BR><BR>but before that pring the @WhereClause and see if it look ok<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
604

## I've tried that...

I now have the code below in the SP, but I&#039;m getting a syntax error in the first line of this, and five occurances of an error near THEN...<BR><BR>Any idea what the errors are?<BR>Thanks!!!<BR><BR><BR>@WhereClause = &#039;WHERE&#039;<BR>If @strTerm &#060;&#062;&#039;&#039; Then<BR> @WhereClause = @WhereClause + &#039;CharIndex("+@strTerm + ",Jobsought)&#062;0 OR &#039; +_<BR> &#039;CharIndex(" + @strTerm + ",CV)&#062;0&#039;<BR>End If<BR> If @strWorkPermit &#060;&#062;&#039;&#039; Then <BR> @WhereClause = @WhereClause + &#039; AND Workpermit = &#039;+@strWorkPermit<BR> End if<BR><BR> If @intSalary &#060;&#062;&#039;&#039; Then <BR> @WhereClause = @WhereClause + &#039; AND SalarySought &#060;= &#039;+@intSalary<BR> End if<BR><BR> If @intFtOrPt &#060;&#062;"" Then <BR> @WhereClause = @WhereClause + &#039; AND FtOrPt = &#039;+@intFtOrPt<BR> End if<BR><BR> If @strCat &#060;&#062;"" Then <BR> @WhereClause = @WhereClause + &#039; AND Category = &#039;+@strCat<BR> End if<BR><BR>@WhereClause = @WhereClause + &#039; AND Status NOT LIKE "%ot looking%" ORDER BY Entrydate DESC&#039;<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: I've tried that...

remove<BR><BR> then<BR><BR>and <BR><BR>end if<BR><BR>from the if statements. You need to use dynamic sql as well, i.e. build a string containing the statement and then using the exec statement.<BR><BR>declare @s varchar(400)<BR>set @s = &#039;insert into ...&#039; + @whereClause<BR>exec(@s)

5. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## Also

&#062;&#039;CharIndex("+@strTerm + ",Jobsought)&#062;0 OR <BR>what is that<BR><BR>more like<BR><BR>&#039;CharIndex(&#039; + @strTerm + &#039;,Jobsought)&#062;0 &#039;.....<BR><BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
604

## Thanks, but this gives other errors...

... I get 6 errors listed around my @Whereclauses<BR><BR>... and have I got the @s bit right further down?<BR>Thanks!<BR><BR><BR>...<BR>@WhereClause = &#039;WHERE&#039;<BR>If @strTerm &#060;&#062;&#039;&#039; <BR> @WhereClause = @WhereClause + &#039;CharIndex("+@strTerm + ",Jobsought)&#062;0 OR &#039; +_<BR> &#039;CharIndex(" + @strTerm + ",CV)&#062;0&#039;<BR><BR> If @strWorkPermit &#060;&#062;&#039;&#039; <BR> @WhereClause = @WhereClause + &#039; AND Workpermit = &#039;+@strWorkPermit<BR><BR> If @intSalary &#060;&#062;&#039;&#039; <BR> @WhereClause = @WhereClause + &#039; AND SalarySought &#060;= &#039;+@intSalary<BR> <BR> If @intFtOrPt &#060;&#062;"" <BR> @WhereClause = @WhereClause + &#039; AND FtOrPt = &#039;+@intFtOrPt<BR> <BR> If @strCat &#060;&#062;"" <BR> @WhereClause = @WhereClause + &#039; AND Category = &#039;+@strCat<BR> <BR><BR>@WhereClause = @WhereClause + &#039; AND Status NOT LIKE "%ot looking%" ORDER BY Entrydate DESC&#039;<BR><BR> <BR><BR>--Create a temporary table<BR>CREATE TABLE #TempItems<BR>(<BR> ID int IDENTITY,<BR> CandidateID int,<BR> Jobsought text,<BR> WorkPermit varchar(50),<BR> FtOrPt varchar(50),<BR> SalarySought int,<BR> Category varchar(50),<BR> Status varchar(50),<BR> Entrydate smalldatetime,<BR> CV text<BR> <BR>)<BR><BR>-- Insert the rows from tblItems into the temp. table<BR>set @s =&#039;INSERT INTO #TempItems (CandidateID, Jobsought, WorkPermit, FtOrPt, SalarySought, Category, Status, CV, Entrydate) <BR>SELECT CandidateID, Jobsought, WorkPermit, FtOrPt, SalarySought, Category, Status, CV, Entrydate FROM Candidetails<BR>&#039; +@WhereClause<BR><BR>exec(@s)

7. Senior Member
Join Date
Dec 1969
Posts
604

## You mean I should nest single quotes

within other single quotes??

8. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## SEE what you are doing there

you are building a string<BR><BR><BR>so <BR><BR>&#039;text &#039; + variable + &#039;some more text&#039;<BR><BR><BR>

9. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## oh come now

PRINT your whereclause and show us what that looks like<BR><BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
604

## Er... how to PRINT..?

Could you tell me how to do that?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•