Dynamic query help SQL 2005

[george_Martinho]

I'm having a problem in getting a dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.

Thanks
Girogio

--------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Search_Profile]
@Country NVARCHAR(100) = null,
@County NVARCHAR(100) = null,
@Town NVARCHAR(100) = null,
@AType bit,
@PageIndex int,
@NumRows int,
@UsersCount int Output
AS
BEGIN

DECLARE @where_clause NVARCHAR(500);

IF @Country IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND aCountry = "' + @Country +
'"'
END

IF @County IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND aCounty = "' + @County + '"'
END

IF @Town IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND aTown = "' + @Town + '"'
END

IF @AType IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND Independent = "' +
Convert(NVARCHAR, @AType) + '"'
END
DECLARE @Query1 NVARCHAR(1000);
SET @Query1 = 'SELECT @UsersCount=(SELECT COUNT(*) FROM CustomProfile
WHERE aActive = 1 ' + @where_clause
exec(@Query1)

DECLARE @startRowIndex int;
SET @startRowIndex = (@PageIndex * @NumRows) +1;

DECLARE @Query2 NVARCHAR(1000);

SET @Query2 = 'WITH UsersProfiles as (
SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,
t.apubName, t.aCounty, t.aTown, u.UserName
FROM CustomProfile t, vw_aspnet_Users u
WHERE t.UserID = u.UserID AND aActive = 1 ' + @where_clause

Declare @Query3 NVARCHAR(1000)

SET @Query3 = '
SELECT UserId, apubName, aCounty, aTown, UserName
FROM ' + @Query2 +
' WHERE Row BETWEEN ' + Convert(NVARCHAR, @startRowIndex) + ' AND ' +
Convert(NVARCHAR, @startRowIndex+@NumRows-1)

exec(@Query3)

END

[Erland Sommarskog]

(ge*************@hotmail.com) writes:

I'm having a problem in getting a dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.

I certainly helps if you are more specific about what your problems
are. The one thing I caught at a glance is that you are using " as a
string delimiter. This is possible if QUOTED_IDENTIFIER is off, but
there is functionality that requires this setting to be on, so don't
do that.

Check out my article about dynamic search conditions on
http://www.sommarskog.se/dyn-search.html. There are some examples
that very similar to what you are doing.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DickChristoph]

Hi Girogio,

One problem I noticed is that the first time (actually everytime) you try to
set @Where_Clause, It will always set @Where_Clause to Null. Since
@Where_Clause is Null to begin with.

Try This

DECLARE @where_clause NVARCHAR(500);

Set @where_clause = '' -- <New Line

--.....Other Stuff as before

I didn't test this but I hope it helps.

-Dick Christoph
<ge*************@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I'm having a problem in getting a dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.

Thanks
Girogio

--------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Search_Profile]
@Country NVARCHAR(100) = null,
@County NVARCHAR(100) = null,
@Town NVARCHAR(100) = null,
@AType bit,
@PageIndex int,
@NumRows int,
@UsersCount int Output
AS
BEGIN

DECLARE @where_clause NVARCHAR(500);

IF @Country IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND aCountry = "' + @Country +
'"'
END

IF @County IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND aCounty = "' + @County + '"'
END

IF @Town IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND aTown = "' + @Town + '"'
END

IF @AType IS NOT NULL
BEGIN
SET @where_clause = @where_clause + ' AND Independent = "' +
Convert(NVARCHAR, @AType) + '"'
END
DECLARE @Query1 NVARCHAR(1000);
SET @Query1 = 'SELECT @UsersCount=(SELECT COUNT(*) FROM CustomProfile
WHERE aActive = 1 ' + @where_clause
exec(@Query1)

DECLARE @startRowIndex int;
SET @startRowIndex = (@PageIndex * @NumRows) +1;

DECLARE @Query2 NVARCHAR(1000);

SET @Query2 = 'WITH UsersProfiles as (
SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,
t.apubName, t.aCounty, t.aTown, u.UserName
FROM CustomProfile t, vw_aspnet_Users u
WHERE t.UserID = u.UserID AND aActive = 1 ' + @where_clause

Declare @Query3 NVARCHAR(1000)

SET @Query3 = '
SELECT UserId, apubName, aCounty, aTown, UserName
FROM ' + @Query2 +
' WHERE Row BETWEEN ' + Convert(NVARCHAR, @startRowIndex) + ' AND ' +
Convert(NVARCHAR, @startRowIndex+@NumRows-1)

exec(@Query3)

END