TAGS :Viewed: 11 - Published at: a few seconds ago

[ SqlCommand IF statement ]

is it possible to write SQL Transact query which will be different for different input?

I mean I have a query:

SELECT  Entries.Date AS Date, 
        Users.UserName AS UserName,
        Entries.Enter AS Enter,
        Entries.Leave AS Leave
FROM Entries 
INNER JOIN Users 
ON (Entries.UserID = Users.Id)
WHERE UserName LIKE @UserName

And I would like to remove last line if @UserName is not provided.

Answer 1


You can write this SQL as:

SELECT e.Date AS Date, u.UserName AS UserName, e.Enter AS Enter, e.Leave AS Leave
FROM Entries e INNER JOIN
     Users u
     ON e.UserID = u.Id
WHERE u.UserName LIKE @UserName or @UserName is NULL;

(The aliases just make the query easier to write and to read.)

However, there are reasons why this might not be a great idea. Having an or condition can make it harder for SQL Server to use an index. With one variable, it might be all right. With multiple variables, this could have a big impact on performance.

If that is a concern, then write the query in the application as dynamic SQL. Start with a where clause like:

declare @where varchar(8000) = '1=1'

And then build it:

if @UserName is not NULL
begin
    set @where = @where + ' and UserName = @UserName';
end;

and continue this for each clause.

Answer 2


Change the last part like:

  SELECT  Entries.Date AS Date, 
            Users.UserName AS UserName,
            Entries.Enter AS Enter,
            Entries.Leave AS Leave
    FROM Entries 
    INNER JOIN Users 
    ON (Entries.UserID = Users.Id)
    WHERE (@UserName IS NULL OR (UserName LIKE @UserName))

Since you're using LIKE here, will you be passing in the wild-card? If not, you will need...

WHERE (@UserName IS NULL OR (UserName LIKE @UserName + '%'))

You could add the % sign at the beginning of the param, but this will invalidate any index.

Answer 3


This is the style I generally use in these situations.

SELECT  Entries.Date AS Date, 
        Users.UserName AS UserName,
        Entries.Enter AS Enter,
        Entries.Leave AS Leave
FROM Entries 
INNER JOIN Users 
ON (Entries.UserID = Users.Id)
WHERE (@UserName IS NOT NULL AND UserName LIKE '%' + @UserName + '%') 
    OR (@UserName IS NULL AND UserName LIKE '%')