How to make this query accurate in SQL server 2005

Use GROUP BY in which query you can ROW_NUMBER(), second thing its safe from safe from sql injection. you can only use one verchar parameter in row_number "p_SortExpression" which is safe.

SET @sql ='
SELECT  ID, DateTime, Subject, CreatedBy, ToReceipientID, Status
FROM
(
 
SELECT  ID, DateTime, Subject, CreatedBy, ToReceipientID, Status,
ROW_NUMBER() OVER(ORDER BY '+ @p_SortExpression +') AS Indexing
FROM
(
SELECT  ID,DateTime, Subject, CreatedBy, ToReceipientID, SenderStatus AS Status
FROM ComposeMail 
 
WHERE (CreatedBy =@p) AND (SenderStatus = 7 OR SenderStatus = 8 )
UNION
SELECT ID,DateTime, Subject, CreatedBy, ToReceipientID, ReceiverStatus As Status
FROM ComposeMail
 
WHERE (ToReceipientID = @p1) AND (ReceiverStatus = 7 OR ReceiverStatus = 8)
)
AS NewDataTable
GROUP BY  ID, DateTime, Subject, CreatedBy, ToReceipientID, Status
 
) AS IndexTable
 
WHERE Indexing > @p2 AND Indexing<= (@p2+@p3)' 

Select allOpen in new window

Welll actually i should have eloborate my question little bit further, there are two querries, one is getting records according to "CreatedBy" and the second is getting records according to "ToReceipeintID". my problem is in this case,
1) when createdby and ToReceipientID are same but may have different statuses, so im getting two records in this case for the same instance with differnent status.. i want to eleminate this condition, if it is coming up with status 7 and the other is coming up with status 8 then i only want the record with  status 7...
Actually this query is complicated and i cant think of any other way to do it.

Would be using a group by I think...
 and it is OK from injection perspective...

ALTER PROCEDURE [dbo].[sp_GetTrashListWithSorting] --'6dbf9a01-c88f-414d-8dd9-696749258cef', '6dbf9a01-c88f-414d-8dd9-696749258cef','DateTime ASC','0','30'
(
@p_CreatedBy UNIQUEIDENTIFIER,
@p_ToReceipientID UNIQUEIDENTIFIER,
@p_SortExpression NVARCHAR(100),
@p_StartIndex INT,
@p_MaxRows INT
)
 
As
SET NOCOUNT ON;
IF LEN(isnull(@p_SortExpression,'')) < 1
SET @p_SortExpression ='DateTime DESC'
 
DECLARE @Sql NVARCHAR(4000)
SET @sql ='
SELECT  ID, DateTime, Subject, CreatedBy, ToReceipientID, Status
FROM
(
 SELECT  ID, DateTime, Subject, CreatedBy, ToReceipientID, min(Status) as Status,
 ROW_NUMBER() OVER(ORDER BY '+ @p_SortExpression +') AS Indexing
 FROM
 (
  SELECT  ID,DateTime, Subject, CreatedBy, ToReceipientID, SenderStatus AS Status
  FROM ComposeMail 
  WHERE (CreatedBy =@p) AND (SenderStatus = 7 OR SenderStatus = 8 )
 
  UNION ALL
 
  SELECT ID,DateTime, Subject, CreatedBy, ToReceipientID, ReceiverStatus As Status
  FROM ComposeMail
  WHERE (ToReceipientID = @p1) AND (ReceiverStatus = 7 OR ReceiverStatus = 8)
 ) AS NewDataTable
 GROUP BY ID, DateTime, Subject, CreatedBy, ToReceipientID
 
) AS IndexTable
 
WHERE Indexing > @p2 AND Indexing<= (@p2+@p3)' 
 
DECLARE @paramDefination NVARCHAR(500)
SET @paramDefination =N'@p UNIQUEIDENTIFIER ,@p1 UNIQUEIDENTIFIER, @p2 INT, @p3 INT'
 
--print @sql
--print @paramdefination
 
EXEC sp_executesql @sql, @paramDefination,
					@p = @p_CreatedBy,
					@p1 = @p_ToReceipientID,
					@p2 = @p_StartIndex ,
					@p3 = @p_MaxRows

Select allOpen in new window

it solved my problem,

Sorry, but I think that answer is wrong... Simply because it is not reporting ReceiverStatus... And the reason for the Union is to get Senders and Receivers both reporting under the same columns. So, you possibly need a case statement, but that too is a bit difficult...

Consider :

create table #composemail (id int, toreceipientID uniqueidentifier, createdby uniqueidentifier, [datetime] datetime, subject varchar(100), Senderstatus int, Receiverstatus int)
insert #composemail values (3, '6dbf9a01-c88f-414d-8dd9-696749258cef','6dbf9a01-c88f-414d-8dd9-696749258cef', getdate(),'test','6','8')

declare @p_CreatedBy UNIQUEIDENTIFIER,
        @p_ToReceipientID UNIQUEIDENTIFIER

set @p_CreatedBy = '6dbf9a01-c88f-414d-8dd9-696749258cef'
set @p_ToReceipientID = '6dbf9a01-c88f-414d-8dd9-696749258cef'

SELECT DISTINCT
       ID,DateTime, Subject, CreatedBy, ToReceipientID, SenderStatus AS Status
  FROM #ComposeMail  
 WHERE ((CreatedBy = @p_createdby) AND (SenderStatus = 7 OR SenderStatus = 8 ))
    OR ((ToReceipientID = @p_toreceipientid) AND (ReceiverStatus = 7 OR ReceiverStatus = 8))


Then you will be getting (incorrectly) status 6 being reported and it was not the reason why the row was selected - the status should be reporting 8.

c'mon guys, I am not trying to cause any problems, the above scenario might not be possible, but if it is, then the answer is wrong and you are going to mislead anyone else coming along afterwards...

Can we please chat about this ?