RDA Pull with Stored Proc parameters

[Eliyahu Goldin]

Put your parameters in the same string with your stored procedure name,
i.e., form a string with your stored procedure name and parameters and pass
it to Pull in the second parameter.

Eliyahu

"Steve Doughty" <replyto...@nowhere.com> wrote in message
news:7f8001c16d5b$fa0a8710$35ef2ecf@TKMSFTNGXA11...
> Hi all,
> Can you send parameters with a PULL using a stored
> procedure? There doesn't seem to be a parameters
> collection.
>
> TIA,
>
> Steve
>
>

[Jeff Harris]

Its my understanding that in order to use stored
procedures with the .Pull method, the stored procedures
need to be very simple select statements with only
references to the necessary table. Is this true?

What's a good way to use stored procedures with the .Pull
method if you want to use different combinations of
parameters?

>.
>

[Steve Doughty]

Thanks Eliyahu, works a treat!
Jeff, there's no reason that the stored proc can't be
complex - all that CE is going to know about is the
recordset that is returned. To call the stored proc with
a parameter all I did was concatenate the parameters to
the stored proc name

"sp_GetEmployeeList 26"

I assume that I can just keep adding parameters if
necessary...

Cheers,

Steve

>.
>

[Jeff Harris]

I did some testing last night and found 1 minor concern.
First of all, it looks like the stored procedure can be
more than just a simple select. I usually create a temp
table to hold the primary key values of records that I
want to return. At the very end, I join the temp table to
the table with the records in it (ex. Employees) and use
the temp table as a filter. I DO NOT specify any fields
from the temp table as part of the SELECT clause.

This all worked fine.

I did run into a problem when my return query returned
fields from more than one table in the SELECT clause.

Ex1. (this works)
SELECT Employees.EmployeeID, Employees.Name
FROM Employees INNER JOIN #Temp ON Employees.EmployeeID
= #Temp.EmployeeID

Ex2. (this does not work)
SELECT Employees.EmployeeID, Employees.Name,
Departments.Number
FROM Employees INNER JOIN #Temp ON Employees.EmployeeID
= #Temp.EmployeeID
INNER JOIN Departments ON Employees.DepartmentID =
Departments.DepartmentID

I'm not absolutely sure that this was caused by 2 tables
being referneced in the SELECT clause, but I'm pretty sure
that caused my trouble. I guess it makes sense if you
think about it.

>.
>

[kkum...@gmail.com]

Use trackingOptionOFF when combining columns from multiple tables. Keep in mind that this does not PUSH changes to this table on the server afterwards. Can be used only for PULLs. Check the MSDN for more http://msdn.microsoft.com/en-us/library/k2tw71a0%28VS.90%29.aspx