stored procedure row source with parameter?

[Zarty]

I've been going around in circles with this. I want to find out if it iis possible to use a stored procedure with a parameter as a row source? I imagine this would be exactly the same using a query with 'variables' in the where clause.

Thanks,
Zarty

 

[datAdrenaline]

Is your front end an ADP? ... if NOT ... you can modify the SQL statement of a Passthrough to have the literal values of the arguments you wish to pass to the SP, or, you can open an recordset in VBA, then bind that recordset to a form.

 

[Zarty]

Yes, it's ADP. Is there a better way to do it with ADP?

Regards,
Zarty

 

[datAdrenaline]

In an ADP you would set your RecordSource property to the name of the stored procedure, then you can utilitize the "Input Parameters" form property.

For example:
A form with a record source of "sp_ProcessesSelected" with the header that looks something like this:

[SIZE=1]ALTER PROCEDURE pr_ProcessesSelected[/SIZE]
[SIZE=1]   ([/SIZE]
[SIZE=1]   @nvTerminalID nvarchar(20)[/SIZE]
[SIZE=1]   )[/SIZE]
[SIZE=1]AS[/SIZE]

So, as you can see, the sp expects ONE parameter identified as @nvTerminalID and it has the datatype of nvarchar(20) .... so ... since the sp is my recordsource, I will set the "Input Parameters" property (Me.InputParameters in VBA with code behind the form) to the following string:

@nvTerminalID nvarchar(20) = 'D0203419'

{note: if you have multiple parameters, separate the expressions with a comma}

Now when the forms recordsource is queried (ie: an execution of Me.Requery) the value of 'D0203419' is passed to the @nvTerminalID parameter that is expected by my sp.

If you want to use a form control value for your parameter, then the property would look something like this:

@nvTerminalID nvarchar(20) = [Forms]![SomeFormName]![SomeControl]

With this syntax, your value of the referenced control is passed to the indicated paramter.

...

Here is a sample code snipet the could be attached to a button (if you go that route) ....

Private Sub cmdResetMyParameter_Click()
    Me.InputParameters = "@nvTerminalID nvarchar(20)='D0203419'"
    Me.Requery
End Sub

Also, I would suggest that when you use a parameterized sp for your Recordsource, set a value (ie: set the property while in design view) for the Input Parameters property that makes sense when initially opening your form.

... Disclaimer ... its been a while since I've done this in an ADP and I am not near a machine I can verify at the moment ... so I may be off just a bit ...

Also ... as I finishing this up, I am pretty sure you can just enter the values of the parameters in the expected order ... so in the sample I provided, I think I can just enter 'D0203419' on the Input Parameters property ...

 

[Zarty]

Thank you, I'll try that out and report back.