Pass wildcard character (%) as parameter to stored procedures

  • Thread starter Thread starter Siddharth Parekh
  • Start date Start date
S

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.
 
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
 
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
 
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
 
what is the recordsource of the subform?

Siddharth Parekh said:
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
Click to expand...
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.
 
A stored procedure to which i pass parameters based on the textbox
what is the recordsource of the subform?

Siddharth Parekh said:
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
Click to expand...
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.
 
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?
 
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

Siddharth Parekh said:
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
Click to expand...
%. Instead of returing all records it does not return any records.
 
Back
Top