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