T-SQL Search Procedure

[DeanWilliams]

Hi,

I have a search form and a stored procedure that returns the resultant recordset. It works by using tags on each control on the form to store the name of the corresponding parameter in the stored procedure.

So basically whatever values the user types in get parsed into the sql statement.

However now the users would like another 14 numeric fields to be searched for but instead of looking for the actual values I need to find where these numeric values are not null.

So I have checkboxes on the form. I cannot now think of a way of checking each of the ticked fields for null values without having to have 14 IF statements in my procedure with the Select statement repeated and altered accordingly.

I am sure there must be an easy way using either IsNull, NullIf or Case statements or something like that.

Please can anyone help.

Thanks,
Dean

 

[hmckillop]

If you are creating dynamic sql based on a statement like this
declare @sqlStatment varchar(1000)
IF @param1 is NOT NULL
SET @sqlStatement = "Select * From myTable where field1 = ' + @param1

You could just do

SELECT * FROM MyTable
WHERE
(Field1 IS NULL OR Field1 = @param1)

This could be repeated for each field
i.e.

SELECT * FROM MyTable
WHERE
(Field1 IS NULL OR Field1 = @param1)
AND
(Field2 IS NULL OR Field2 = @param2)
etc.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[DeanWilliams]

Hi,

Thanks for your response. I was sort of getting there. Have a dynamic sql statement now. However my client app has an ADO command object that I set to a recordset.

I.e: Set rst = cmd.Execute

This does not work now. It says the recordset is not open. It worked before I included the Exec (@SQL) line in the stored procedure.

 

[hmckillop]

Did you open a new recordset i.e.
Dim rst as New Adodb.Recordset
?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[DeanWilliams]

Yeah, I did that.

It was all working fine. It works without Exec statement in T-SQL.

Is there some way that the EXEC statement conflicts with the ado cmd.execute method?

 

[DeanWilliams]

Do I need to use a RETURN statement or something. Maybe the stored procdure isn't returning a recordset.

I am new to SQL Server, as you can probably tell.

 

[hmckillop]

have you tried executing the same statement in SQL Query Analyser and you actually get results?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[DeanWilliams]

Hi,

Thanks I hadn't done that. Getting there now.

Worked out why it wasn't working, and can now get it to work in Query Analyser, but not in my app.

Same error: but I have narrowed it down.

It is to do with the paramaters I have in my stored procedure that are of the bit data-type.

For some reason I cannot pass the ADO adBoolean command parameters to my bit paramaters in the procedure. The same procedure works if I remove these bit parameters.

Is there a way around it? Or should I just change those parameters data-types from bit to something else in T-SQL?

 

[DeanWilliams]

Finally sorted it out!!

Changing the data types had no effect.

Can't believe what it was - all I needed to do was include the SET NOCOUNT ON option.

3 days I have been trying to debug that and it was just 3 words!

Thanks all who replied.