Help me writing this query

Question

CREATE PROCEDURE [dbo].[sp_SelectRecipientsList4Test] --'6DBF9A01-C88F-414D-8DD9-696749258CEF','Emirates.Description','0','5'
--'6DBF9A01-C88F-414D-8DD9-696749258CEF',
--'121f8b91-a441-4fbf-8a4f-563f53fcc103'
(
@p_CreatedBy UNIQUEIDENTIFIER,
@p_SortExpression NVARCHAR(100),
@p_StartIndex INT,
@p_MaxRows INT
)
AS
SET NOCOUNT ON;
IF LEN(@p_SortExpression) = 0 
SET @p_SortExpression = 'Users.Name Asc'

DECLARE @sql NVARCHAR(4000)
SET @sql='

DECLARE @p_CreatedBy UNIQUEIDENTIFIER

SELECT
Name,
POBox,
EmirateName,
TelephoneNo,
RecipientID,
CreatedBy,
CreatedDate,
ID

FROM
(

SELECT     Users.Name, Users.POBox, Emirates.Description As EmirateName, 

UserDetails.TelephoneNo, AddressBook.RecipientID,AddressBook.CreatedBy, AddressBook.CreatedDate, 

AddressBook.ID,
ROW_NUMBER() OVER(ORDER BY '+ @p_SortExpression +') AS Indexing

FROM         AddressBook INNER JOIN
                      Users ON AddressBook.RecipientID = Users.ID INNER JOIN
                      UserDetails ON Users.ID = UserDetails.UserID INNER JOIN
                      Emirates ON Users.EmiratesID = Emirates.ID
----WHERE       (AddressBook.CreatedBy = @p_CreatedBy)
) AS NewDataTable

WHERE Indexing > '+ CONVERT(NVARCHAR(10), @p_StartIndex) + 
' AND Indexing<=(' + CONVERT (NVARCHAR(10),@p_StartIndex ) + ' + '
+ CONVERT(NVARCHAR(10),@p_MaxRows)+') '

EXEC sp_executesql @sql

This query is not giving any error but also not giving any result please help

Answer 1

Have you tried breaking down the statement, to check if intermediate results are as expected? That's what you do to debug a complex statement...

For example, there's a nested SELECT in there. If you commit that SELECT on its own, does it print the expected values?

Edit: There's a saying about teaching a man to fish. 'ck' and 'n8wrl' have given you fish to eat today, now please practice fishing to feed you tomorrow...

Answer 2

You are querying:

'WHERE Indexing > '+ CONVERT(NVARCHAR(10), @p_StartIndex) + 
' AND Indexing<=(' + CONVERT (NVARCHAR(10),@p_StartIndex ) + ' + '

and then adding max rows as a string, you can do this much more easily like so:

'WHERE Indexing > '+ CONVERT(NVARCHAR(10), @p_StartIndex) + 
'  AND Indexing <='+ CONVERT(NVARCHAR(10),@p_StartIndex + @p_MaxRows)

---

EDIT

The problem with your inner WHERE is that you are passing in the parameter, you need to do

'WHERE       (AddressBook.CreatedBy = ''' + CAST(@p_CreatedBy AS CHAR(36)) + ''')'

Answer 3

Are you sure all your joins should be inner joins?

Answer 4

Well, a quick glance of this:

WHERE Indexing > '+ CONVERT(NVARCHAR(10), @p_StartIndex) + ' AND Indexing<=(' + CONVERT (NVARCHAR(10),@p_StartIndex ) +...

looks like you're looking for an impossible condition, not unlike this:

WHERE Indexing > 5 AND Indexing <= 5

So that might be why you're getting no rows, but this proc is ripe for SQL injection attacks too. Building SQL on the fly based on possibly-unvalidated parameters is very dangerous.

Answer 5

Change sp_executesql to PRINT and see what gets generated (the poor man's debugger)

Besides what all the other people told you,

give me one good reason why you are using sp_executesql over exec? You are not using parameterized statements, you also are not protected from sql injections because you just execute the whole string

This will just bloat the procedure cache everytime this is run and some values change, you will get a new plan every time

Please take a look at Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly and Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec