Using Like with and operator in stored procedure

Question

I have a problem with working at stored procedure and stimulsoft when using Like operator and and operator. Please help me. When I use this code, it returns a blank page in stimul, but if I change one of the conditions to or, the code returns all rows in database table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[reportedu2]
    @d1 NVARCHAR(50),
    @d2 NVARCHAR(50),
    @name NVARCHAR(50),
    @unit NVARCHAR(50),
    @semat NVARCHAR(50),
    @maghta NVARCHAR(50),
    @uni NVARCHAR(50),
    @field NVARCHAR(50)
AS
BEGIN
    SELECT * 
    FROM Tbledu
    WHERE (unit LIKE '%' + ISNULL(@unit, unit) + '%')
      AND (name LIKE '%' + ISNULL(@name, name) + '%')
      AND (dateend BETWEEN @d1 AND @d2)
      AND (semat LIKE '%' + ISNULL(@semat, semat) + '%')
      AND (maghta LIKE '%' + ISNULL(@maghta, maghta) + '%')
      AND (uni LIKE '%' + ISNULL(@uni, uni) + '%')
      AND (field LIKE '%' + ISNULL(@field, field) + '%')
END

c# does not have stored procedures. The code you've posted looks like T-SQL. Please [edit] your post to properly indent the code and provide proper tags. — Zohar Peled, Jul 07 '19 at 17:01
Please also explain what an expression like `unit Like '%'+IsNull(@unit,unit)+'%'` is expected to do. I admit, I'm no SQL expert, but that looks like gibberish to me. You seem to be trying to accommodate null parameters to your SP, which I would expect would be correctly handled with something more like `@unit IS NULL OR unit = @unit`. — Peter Duniho, Jul 07 '19 at 17:12
Possible duplicate of [sql query if parameter is null select all](https://stackoverflow.com/questions/13474207/sql-query-if-parameter-is-null-select-all) — Peter Duniho, Jul 07 '19 at 17:12
my problem is some of my field may be able empty means in my report form user enter data to some field and some part of data — Amir, Jul 07 '19 at 17:23
Also: `@d1` and `@d2` are **obviously** dates - so **WHY** are you passing them as `NVARCHAR(50)` instead of the **most appropriate** datatype - either `DATE` (if you don't need any time portion), or `DATETIME2(n)` if the time is relevant — marc_s, Jul 07 '19 at 18:48
https://stackoverflow.com/users/13302/marc-s this is persian date i save it as a NVARCHAR(50) — Amir, Jul 07 '19 at 20:13

score 0 · Answer 1 · answered Jul 07 '19 at 17:54

0

A good way to debug this is to try each condition by itself and make sure it returns the rows you expect.

If you are getting back all of the rows, the most likely thing is that one of the variables is = '', instead of the expected null, so the like condition is , for example ...

(uni Like '%%')

which of course will return everything.

answered Jul 07 '19 at 17:54

greg

1,673
1
17
30

Correction: `uni Like '%%'` will return all rows where `uni` is not null, because `null like '%%'` will return unknown which evaluates as false. – Zohar Peled Jul 09 '19 at 14:13

Using Like with and operator in stored procedure

1 Answers1