Having trouble with dynamic SQL SP 2

[Amesville]

Hi Folks

coming back to a problem I was having a couple of months ago, I wasn't able to solve it then and I was working around it, now I'm back to it.

I'm trying to write an SP that allows me to put in one or more of many different search criteria for changing what comes back in the dataset returned. I have put together the following query based on what I've read from several internet sites and such:

PROCEDURE [dbo].[spSPFillGridPlus] 
	-- Add the parameters for the stored procedure here
	@Par1 varchar(50) = NULL,
	@Par2 varchar(50) = NULL,
	@Par3 varchar(50) = NULL,
	@Par4 varchar(50) = NULL,
	@Par5 varchar(50) = NULL,
	@Par6 varchar(50) = NULL,
	@Par7 varchar(50) = NULL,
	@Par8 varchar(50) = NULL
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT CLIC, IndName, IndCity, IndState, IndRR, ShipRecv, Siding, Commodity, Notes FROM Industry 
    WHERE Active = 'X' 
    AND ((@Par1 IS NULL) OR Commodity = @Par1)
    AND ((@Par2 IS NULL) OR ShipRecv IN (@Par2, 'B'))
    AND ((@Par3 IS NULL) OR CLIC LIKE @Par3)
    AND ((@Par4 IS NULL) OR CLIC NOT LIKE @Par4)
    AND ((@Par5 IS NULL) OR IndName LIKE @Par5)
    AND ((@Par6 IS NULL) OR IndCity LIKE @Par6)
    AND ((@Par7 IS NULL) OR IndState LIKE @Par7)
    AND ((@Par8 IS NULL) OR IndRR LIKE @Par8)
    ORDER BY IndName;

END

As you can probably tell because I'm asking about this, it isn't working. I get no results back no matter what I put in for criteria. Is there anything I'm doing wrong that I can fix? I'm afraid I really don't understand dynamic SQL yet...

Thanks

Craig

 

[gmmastros]

It's possible that the stored procedure is actually getting an empty string instead of NULL for the parameters. If this is the case, then you can probably resolve this issue like this:

PROCEDURE [dbo].[spSPFillGridPlus] 
    -- Add the parameters for the stored procedure here
    @Par1 varchar(50) = NULL,
    @Par2 varchar(50) = NULL,
    @Par3 varchar(50) = NULL,
    @Par4 varchar(50) = NULL,
    @Par5 varchar(50) = NULL,
    @Par6 varchar(50) = NULL,
    @Par7 varchar(50) = NULL,
    @Par8 varchar(50) = NULL
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT CLIC, IndName, IndCity, IndState, IndRR, ShipRecv, Siding, Commodity, Notes FROM Industry 
    WHERE Active = 'X' 
    AND (([!]NullIf([/!]@Par1[!], ')[/!] IS NULL) OR Commodity = @Par1)
    AND (([!]NullIf([/!]@Par2[!], ')[/!] IS NULL) OR ShipRecv IN (@Par2, 'B'))
    AND (([!]NullIf([/!]@Par3[!], ')[/!] IS NULL) OR CLIC LIKE @Par3)
    AND (([!]NullIf([/!]@Par4[!], ')[/!] IS NULL) OR CLIC NOT LIKE @Par4)
    AND (([!]NullIf([/!]@Par5[!], ')[/!] IS NULL) OR IndName LIKE @Par5)
    AND (([!]NullIf([/!]@Par6[!], ')[/!] IS NULL) OR IndCity LIKE @Par6)
    AND (([!]NullIf([/!]@Par7[!], ')[/!] IS NULL) OR IndState LIKE @Par7)
    AND (([!]NullIf([/!]@Par8[!], ')[/!] IS NULL) OR IndRR LIKE @Par8)
    ORDER BY IndName;

END

The NullIf code allows you to pass NULL or an empty string in for your parameters. So... NULL or empty string will cause that part of the where clause to be ignored.

Make sense?

P.S. There may be performance problems with this type of query if the table is large. If this is the case, let me know and I can help advise you on performance improvements.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RTag]

You need to add % to the variables in order to have LIKE working:
If you are trying to filter CLIC that starts with @Par3:
CLIC LIKE @Par3 +'%'
if you are trying to filter CLIC that contains @Par3:
CLIC LIKE '%' + @Par3 +'%'


The same for @Par4 to @Par8

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[Amesville]

Thanks guys, between the two of you you solved my problem! I can get back to working on my program now. Kudos!