Posted by george_Martinho on 02/13/06 15:50

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

[Back to original message]