Pass wildcard character (%) as parameter to stored procedures

[Siddharth Parekh]

Hey,

Can anyone tell me how to pass a wildcard character based on user input to a stored procedure?

I can make it work by adding it to the parameter in the stored procedure itself. For example Description LIKE @desc + '%'.
But i want it to leave it to the user to enter the wildcard if he wants to. Is it possible to do so? Am using ADP's.

Thanks in advance,
Siddharth.

 

[Malcolm Cook]

Siddharth,

No differently than passing any other string to a stored procedure.

How are you doing that now?

What are you expecting to do with the results of the procedure?
If use it as the recordource of a form, then read up on the form
property "Input Parameters"?
If as the recordsource to the sourceobject of a subform, then you might
a) use the subform Link Child Fields and Linke Master Fields to get
the value of a contron on the parent form passed to the SQL stored proc.
b) rely upon some ADP 'magic' which will contrive to pass the value
of a parent form field to the stored proc if it has the same name as the
formal parameter (I've seen this advertise as a feature for how ADP's do
comboboxes, but not seen it mentioned as working for subforms - but it does
at least in AC2003).

Good luck

 

[J. Clay]

In regards to option b) below...You can use imput parameters in the sub form
and reference the parent form controls, or for that matter any open form's
controls.

Jim

 

[Malcolm Cook]

Jim,

Agreed.

Many ways to skin that cat.

Cheers,

Malcolm

 

[Siddharth Parekh]

Malcom,

Thanks for your response.

Basically the value that is entered in a text box is used as an input parameter for a subform.

For example, If i enter 'SID' as the input parameter value and requery my subform, then it will return records and display in the subform, but if i enter the inputparameter value as 'SID%', then instead of returning all the records that start with SID, it returns an empty recordset.

Any idea what i am doing wrong?

Siddharth.
Siddharth,

No differently than passing any other string to a stored procedure.

How are you doing that now?

What are you expecting to do with the results of the procedure?
If use it as the recordource of a form, then read up on the form
property "Input Parameters"?
If as the recordsource to the sourceobject of a subform, then you might
a) use the subform Link Child Fields and Linke Master Fields to get
the value of a contron on the parent form passed to the SQL stored proc.
b) rely upon some ADP 'magic' which will contrive to pass the value
of a parent form field to the stored proc if it has the same name as the
formal parameter (I've seen this advertise as a feature for how ADP's do
comboboxes, but not seen it mentioned as working for subforms - but it does
at least in AC2003).

Good luck

 

[Malcolm Cook]

what is the recordsource of the subform?

Malcom,

Thanks for your response.

Basically the value that is entered in a text box is used as an input parameter for a subform.

For example, If i enter 'SID' as the input parameter value and requery my

subform, then it will return records and display in the subform, but if i
enter the inputparameter value as 'SID%', then instead of returning all the
records that start with SID, it returns an empty recordset.

 

[Siddharth Parekh]

A stored procedure to which i pass parameters based on the textbox
what is the recordsource of the subform?

Malcom,

Thanks for your response.

Basically the value that is entered in a text box is used as an input parameter for a subform.

For example, If i enter 'SID' as the input parameter value and requery my

subform, then it will return records and display in the subform, but if i
enter the inputparameter value as 'SID%', then instead of returning all the
records that start with SID, it returns an empty recordset.

 

[Malcolm Cook]

I meant, what is the definition of the stored procedure?

 

[Siddharth Parekh]

The stored procedure which is the record source of tjhe subform is as follows:

SELECT DISTINCT Code, Description1, Description2
FROM dbo.qryCode
WHERE (@desc IS NULL) AND (@manuf IS NULL) OR
(@desc IS NULL) AND (manufacturer = @manuf) OR
(@manuf IS NULL) AND (Description2 = @desc) OR
(manufacturer = @manuf) AND (Description2 = @desc)
ORDER BY Description1

The parameters are supplied to it by using the Inpur parameters property in the subform.
Everything works fine till someone enters the search criteria along with a %. Instead of returing all records it does not return any records.
I meant, what is the definition of the stored procedure?

 

[Malcolm Cook]

Siddharth,

What you provided is an SQL select statement, not a stored procedure.

In any case, if you want to use a wildcard operator you'll need to use the
SQL LIKE operator instead of using only equality (=) as you have in your
SQL.

Look it up in books on-line.

Good luck,

Malcolm

The stored procedure which is the record source of tjhe subform is as follows:

SELECT DISTINCT Code, Description1, Description2
FROM dbo.qryCode
WHERE (@desc IS NULL) AND (@manuf IS NULL) OR
(@desc IS NULL) AND (manufacturer = @manuf) OR
(@manuf IS NULL) AND (Description2 = @desc) OR
(manufacturer = @manuf) AND (Description2 = @desc)
ORDER BY Description1

The parameters are supplied to it by using the Inpur parameters property in the subform.
Everything works fine till someone enters the search criteria along with a

%. Instead of returing all records it does not return any records.

 

[Siddharth Parekh]

Hey Malcomn,

Thanks a lot...replacing '=' by LIKE solved the problem.

Siddharth.