Dynamic query help SQL 2005

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(george_Martinho@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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
<george_Martinho@.hotmail.com> wrote in message
news:1139838631.991677.194800@.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