CODEWITHSUNDEEP
sql-serverstored-procedures
Malcolm McCaffery
Malcolm McCaffery

Reputation: 2576

Stored Procedure Not Working With LIKE and % Passed As Parameter

I am trying to re-create a stored procedure for testing a specific application scenario, but I don't have enough access to view the original stored procedure. I can see the application runs a query like:

exec dbo.SP_GET_WORKSTATIONS @WORKSTATION_FID=N'%',@VALID_IND=N'%',@WORKSTATION_DOMAIN=N'%',@WORKSTATION_PID=N'%'

Based on the input data type results of the original stored procedure, and results it returned I built a stored procedure like this:

CREATE PROCEDURE [dbo].[SP_GET_WORKSTATIONS]
@WORKSTATION_FID varchar(32),
@VALID_IND char(1),
@WORKSTATION_DOMAIN char(20),
@WORKSTATION_PID varchar(10)
AS
SELECT 
    WORKSTATION_FID as Code,
    VALID_IND as ValidInd,
    WORKSTATION_DOMAIN As Name,
    WORKSTATION_PID as PId 
FROM
    [dbo].[L_WORKSTATIONS] 
WHERE
    WORKSTATION_FID LIKE @WORKSTATION_FID AND
    VALID_IND LIKE @VALID_IND AND
    WORKSTATION_DOMAIN LIKE @WORKSTATION_DOMAIN AND
    WORKSTATION_PID LIKE @WORKSTATION_PID;
GO

However this doesn't return any data, however if I run

SELECT 
    WORKSTATION_FID as Code,
    VALID_IND as ValidInd,
    WORKSTATION_DOMAIN As Name,
    WORKSTATION_PID as PId 
FROM
    [dbo].[L_WORKSTATIONS] 
WHERE
    WORKSTATION_FID LIKE '%' AND
    VALID_IND LIKE '%' AND
    WORKSTATION_DOMAIN LIKE '%' AND
    WORKSTATION_PID LIKE '%'

I get results. If I change the LIKE in stored procedure to = and specify exact parameters, the stored procedure works.

Upvotes: 1

Views: 421

Answers (2)

HLGEM
HLGEM

Reputation: 96552

Be aware that there may be the need to pass in different parameters that like % and depending on the datatypes your proc might not work. More than likely the original was created using dynamic SQL so it could handle a list of values and different datatypes.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453028

Your @WORKSTATION_DOMAIN parameter should be varchar not char.

Trailing space is significant in the like pattern and % followed by 19 spaces (padded out to 20 characters) will try and match content ending with 19 spaces.

Trailing space is ignored in equality comparisons.

Upvotes: 4

Related Questions