troubleshooting Question

How to make this query accurate in SQL server 2005

Avatar of yaserahmed
yaserahmed asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
7 Comments1 Solution224 ViewsLast Modified:
i have the a query which is union of two querries, the resulting query is bringing duplicate records, i dont want duplicate records, i tried putting DISTINCT but getting the same result, can anybody help me rectifying this query.
i also want to know whether this query is safe from sql injection...i'll be pasting my query below
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(@p_SortExpression)=0 
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, 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
) AS IndexTable
 
WHERE Indexing > @p2 AND Indexing<= (@p2+@p3)' 
 
 
DECLARE @paramDefination NVARCHAR(500)
SET @paramDefination =N'@p UNIQUEIDENTIFIER ,@p1 UNIQUEIDENTIFIER, @p2 INT, @p3 INT'
 
EXEC sp_executesql @sql, @paramDefination,
					@p = @p_CreatedBy,
					@p1 = @p_ToReceipientID,
					@p2 = @p_StartIndex ,
					@p3 = @p_MaxRows
ASKER CERTIFIED SOLUTION
JR2003

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros