Calling a dataset Stored Procedure that has parameters

BIZ TMS XData
1

Hello Support,

I my learning to retrieve data using the XData component is used the direct SQL Command i.e. Select * From Mytable :@sampleID, @PrntOpt this works. However, I have stored procedures already calling the data in the database. So, I tried to send the SQL MyStoredProc :@SampleId, :@PrntOpt etc. But when I try and open the data (in design time) I get an error saying it can’t find :@SampleID parameter. I then tried the SQL Text ‘Call MyStoredProc (:@SampleID, :@PrntOpt)’ and get the same error.

Is there another way to call a stored procedure with the correct parameters. The Stored procedure does return a dataset.

Regards

Tom Dalton

2

Hello Support,

After trying I got the XData to run the stored procedure. SQL: MyStoredProc :Param, :Parm2 and then call open or refresh from code.

Regards

3

Sorry, but I got really confused with you first request an also your second post. Do I understand correctly that you have solved your problem?

4

Hi Wagner,

Thanks for your reply. Yes, I did resolve my issue. I had a XDataset component that I wanted to use a stored procedure (as the SQL) to access the data and not the full SQL text. The store procedure is a single string e.g. Samples_By_Client with three parameters expected (typically @Client, @LocationCode and @Option). This instead of the full SQL SELECT Field1, Field2, etc. FROM SamplesTable WHERE ClientID = @Client And LocationID = @Location (don’t need the opt here).

When I called the Stored Proc with the SQL set to: Samples_By_Client @Client, @Location, @Opt in the component. Setting it to active would give an error that said that @Client could not be found.

With a bit experimenting I found that the correct way it set the SQL in the component is the following:

Samples_By_Client :Client, :Location, :Opt This fetches the data that I require.

Regards

1 Like
5

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.