|
|
Posted by DickChristoph on 02/14/06 23:12
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.googlegroups.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
>
[Back to original message]
|