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

Dynamic query help

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)
ENDThere are lots of problems with your dynamic SQL,
@.where_clause is always NULL (NULL + string gives NULL)
@.UsersCount will never get set as it is in the wrong scope
Your CTE should be at the start of the statement, not the middle
You should be able to avoid dynamic SQL by doing
something like this.
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
SET NOCOUNT ON
SELECT @.UsersCount=COUNT(*) FROM CustomProfile
WHERE aActive = 1
AND (@.Country IS NULL OR aCountry=@.Country)
AND (@.County IS NULL OR aCounty=@.County)
AND (@.Town IS NULL OR aTown=@.Town)
AND (@.AType IS NULL OR Independent=@.AType)
DECLARE @.startRowIndex int;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
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
AND (@.Country IS NULL OR aCountry=@.Country)
AND (@.County IS NULL OR aCounty=@.County)
AND (@.Town IS NULL OR aTown=@.Town)
AND (@.AType IS NULL OR Independent=@.AType)
)
SELECT UserId, apubName, aCounty, aTown, UserName
FROM UsersProfiles
WHERE Row BETWEEN @.startRowIndex AND @.startRowIndex+@.NumRows-1
END