How to return filtered rows using SQL Cursors-CodePudding

I have been looking at examples of cursors in stored procedures and all of them seem to be either printing to the console or manipulating data in the main loop.

My situation is as follows:

Create a stored procedure that returns filtered rows from a single table.
Create a cursor for a simple SELECT statement without a WHERE clause.
Iterate through the fetch cycle.
Return only rows that meet certain criteria.

Pseudocode (C#, EntityFrameworkCore):

var name = "xyz";
var email = "";
var users = new List<User>();

foreach (var user in context.Users.AsQueryable())
{
    var added = false;

    if (!added)
    {
        if ((!string.IsNullOrWhitespace(name)) && (user.Name.Contains(name)))
        {
            users.Add(user);
        }
    }

    if (!added)
    {
        if ((!string.IsNullOrWhitespace(email)) && (user.Name.Contains(email)))
        {
            users.Add(user);
        }
    }
}

These conditions can be complex enough to not be able to fit in a WHERE clause. Hence looking at cursors.

Just not sure how to think about this. A cursor is an iterator but how do you accumulate filtered rows to return?

Any advice would be appreciated.

Here is the stored procedure I would like to use cursors in:

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @UseName BIT
    DECLARE @UseEmail BIT

    IF ((@Name IS NULL) OR (LEN(@Name) = 0)) SET @UseName = 0 ELSE SET @UseName = 1
    IF ((@Email IS NULL) OR (LEN(@Email) = 0)) SET @UseEmail = 0 ELSE SET @UseEmail = 1

    IF (@Condition = 0)
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 1) OR ([Name] LIKE '%'   @Name   '%'))
            AND
            ((@UseEmail = 1) OR ([Email] LIKE '%'   @Email   '%'))
    ELSE
        SELECT [Id], [Name], [Email]
        FROM [User]
        WHERE
            ((@UseName = 1) OR ([Name] LIKE '%'   @Name   '%'))
            OR
            ((@UseEmail = 1) OR ([Email] LIKE '%'   @Email   '%'))

    RETURN (@@ROWCOUNT)

END

CodePudding user response：

This type of query is called a Kitchen Sink query.

This can have issues due to parameter sniffing. So the best way to do this is not to use a cursor, but to dynamically build the conditions, and execute it using sp_executesql.

Note how the actual values are not concatenated in, but are also passed through as parameters to the dynamic side.

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @sql nvarchar(max) = N'
SELECT [Id], [Name], [Email]
FROM [User]
WHERE 1=1 AND (
    ';

    DECLARE @conditions nvarchar(max);
    IF NULLIF(@Name, '') IS NOT NULL
        SET @conditions = '([Name] LIKE ''%''   @Name   ''%'')'   '
  ';

    IF NULLIF(@Email, '') IS NOT NULL
        SET @conditions = CONCAT(
                @conditions   CASE WHEN @Condition = 0 THEN 'AND ' ELSE 'OR ' END   '
    ',
                '([Email] LIKE ''%''   @Email   ''%'')
');

    SET @sql  = @conditions   '
);

    RETURN (@@ROWCOUNT);
';
    PRINT @sql; --for testing

    DECLARE @rc int;
    EXEC @rc = sp_executesql
        @sql,
        N'@Name NVARCHAR(100), @Email NVARCHAR(100)',
        @Name = @Name,
        @Email = @Email;

    RETURN @rc;

END

CodePudding user response：

I agree with the others that a cursor sounds like a really bad fit for your use case. But to answer your question, you would first create a temp table, then the cursor could insert any matching rows to the temp table as it looks through the data. At the end, select all rows from the temp table.