Dynamic where clause 2

[Kai77]

Hi,

I wrote the following stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Alter PROCEDURE [dbo].[UW_VACANCIES_VACANCYMODULE_SEARCH]
@SQL nvarchar(4000),
@PageIndex INT,
@NumRows INT,
@VacancyCount INT OUTPUT
AS

BEGIN

SELECT @VacancyCount = (SELECT COUNT(*) FROM VW_VACANCYMODULE_ACTIVE_VACANCIES WHERE @SQL)

Declare @StartRowIndex INT;
Set @StartRowIndex = (@PageIndex * @NumRows) + 1;

WITH VacancyEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY DATE_VALID_FROM DESC)AS Row,
ID,
SHIFT_HOURS,
DATE_VALID_FROM,
TITLE,
JOB_DESCRIPTION,
BENEFITS_DESCRIPTION,
SALARY_PAYAMOUNT_MIN,
SALARY_PAYAMOUNT_MAX,
EXPERIENCE_YEARS,
REQUIRED_SKILLS_DESCRIPTION,
SCHEDULE_TYPE,
CLASSIFICATION_TYPE,
BRH_ID,
FNC_ID,
UME_ID
FROM VW_VACANCYMODULE_ACTIVE_VACANCIES
Where @SQL
)

SELECT
ID,
SHIFT_HOURS,
DATE_VALID_FROM,
TITLE,
JOB_DESCRIPTION,
BENEFITS_DESCRIPTION,
SALARY_PAYAMOUNT_MIN,
SALARY_PAYAMOUNT_MAX,
EXPERIENCE_YEARS,
REQUIRED_SKILLS_DESCRIPTION,
SCHEDULE_TYPE,
CLASSIFICATION_TYPE,
BRH_ID,
FNC_ID,
UME_ID
FROM VacancyEntries
WHERE Row between @StartRowIndex and @StartRowIndex+@NumRows-1

END


I would like to pass the where clause as a parameter (e.g.: ID=2 AND Name Like '%Jo%')

But I am getting an error where I on the line:

SELECT @VacancyCount = (SELECT COUNT(*) FROM VW_VACANCYMODULE_ACTIVE_VACANCIES WHERE @SQL)

It doesnt seem to accept @SQL, its giving the following error message:
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Is it in anyway possible to resolve this problem?

 

[hmckillop]

Dynamic SQL needs to be executed in one statement.
e.g.

CREATE PROC usp_TESTPROC
(
@pi_MyPassedWHEREStatement VARCHAR(1000)
)
AS
BEGIN
DECLARE @SSQL VARCHAR(2000)

SET @sSQL = 'SELECT * FROM MyTable WHERE ' & @pi_MyPassedWHEREStatement
exec @sSQL

The problem with what you are tring to do is assign a variable to the result, in this case the count. You cant do this due to the exec of dynamic SQL is not in the same process.

Two solutions:
Dont have such a dynamic parameter i.e.
If you need to specify a name and/or an ID field, try using

SELECT * FROM MYTable WHERE
(Field1 = @pi_Param1 or @pi_Param1 is NULL) AND
(Field2 like '%' & @pi_Param2 & '%' OR @pi_Param2 is NULL)

This allows you to specify either parameter or both.

Option 2:
Hold the result of dynamic query into a global table variable.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[bborissov]

One small correction. To concatenate string you must use [+] sign in T-SQL not [&].

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mbabcock]

Thanks for posting this...this helps with the thread I had awhile back about temp tables....with this dynamic substitution in the WHERE clause, I shouldn't have to bother with the temp tables now!

--Michael

 

[Kai77]

check out this posting:

http://www.experts-exchange.com/Databases/Q_21937504.html#17215276

still need to check out the solutions provided by hmckillop