MSSQL stored procedure data return

Marc_Brierley · February 4, 2022, 2:51am

Hey all.
This probably should be straightforward but how do I call a MSSQL stored procedure and return the data to my application to put into a queue?

Marc

Mauricio · February 4, 2022, 10:57am

Hi Marc,
you need a stupid temp table for that and call your stored procedure with Prop:SQL or Prop:SQLRowSet. Then you loop your stupid temp table.

totalhip · February 4, 2022, 3:05pm

A temp table is one way. Depending upon the situation, one can also add a table to the DCT or build a layer that allows adding to a passed queue.

Marc_Brierley · February 4, 2022, 6:00pm

Many thanks Mauricio. I’ll have a play and see what I can figure out. If I can’t sort it I may ask if anyone has some sample code.

Marc Brierley
Trimble Forestry
+64212855568

Rick_UpperPark · February 4, 2022, 6:52pm

Look in the help index for Calling a Stored Procedure. There are examples.

Marc_Brierley · February 5, 2022, 10:13am

Hi Douglas I was thinking along those lines myself. I have added a table to the dictionary. Next do I need a routine to set the propsql for that table?

Marc Brierley
Trimble Forestry
+64212855568

totalhip · February 5, 2022, 3:15pm

If the stored procedure is returning a record set, a simple browse, possibly w/ PROP:Sql, can be used for display in a list.

Marc_Brierley · February 5, 2022, 9:05pm

Morning Rick.
I did do that. The examples for returning output parameters were pretty clear
but I couldn’t work out how to actually pull back data.
I’ll have another look see.

totalhip · February 5, 2022, 9:36pm

Marc,
Might you post the select statement for your stored procedure?

NickP · February 6, 2022, 10:54am

I’m wondering if this is an example of the X-Y Problem? “I have problem X, and I’m having trouble with getting my solution Y to work. What am I doing wrong?”

In this case, I’m wondering why you want to fetch ONLY a date from an MS SQL database? And via a stored procedure? If the database is your app’s data store, then my immediate thought is that there are probably simpler ways of achieving the desired outcome, particularly if you plan on calling the procedure for more than one row in a set of records.

Might it be that a View (NOT a Clarion view, but a stored query in the database), containing ALL the data that you want from the database, could do the job? In a way that uses more “out of the box” Clarion, requires less programming, offers better maintainability and re-usability?

If so, you might find the attached, which I delivered to the last ever meeting of the UK Clarion User Group, useful.SQL Views.zip (1.4 MB)

Marc_Brierley · February 6, 2022, 11:37am

Hi Nick. If I typed Date instead of Data then I apologise. I am using a stored procedure to query a C# module which is posting to a webservice. I need the returned data which could be multiple rows of about eight columns displayed in a window in my Clarion application. So what I need to figure out is how to return the data from the stored procedure, which has a select statement at the end, into Clarion.
I hit that makes more sense.

Marc Brierley
Trimble Forestry
+64212855568

NickP · February 6, 2022, 1:04pm

Ah! Yes, “data” rather than “date” does make a bit of a difference!

I suspect that it’s simpler than you think (fear!).

May I suggest?
Break it down into two parts:

Construct a T-SQL query in SQL Server Management Studio, or similar, that does what you want, then
Clarionise the query, using the “dummy table”+Prop:SQL technique, with a queue to receive the result set.

I’m putting it briefly, not to be awkward, but so as not to waste effort on both our parts by going into exquisite on something that you’re already expert in.

N
Nick Pattinson

Rick_UpperPark · February 6, 2022, 3:01pm

Here’s a simple example using a “dummytable”

  Clear(DummyTable)
  DummyTable{Prop:Sql} = 'Call dbo.GetTotalItems('&GBL:JobUnique|
                                                  &','&pSuperiorUnique|
                                                  &','&pGroupUnique|
                                                  &','&Choose(pGroupUnique=0,0,1)|
                                                  &','&LimitingItem&')'
  Assert(~ErrorCode(),FileError())
  Next(DummyTable)

In this case the stored procedure only return one row. If it returns multiple rows then the Next(DummyTable) would be in a loop.
This is the dummy table definition. If you have a table that exactly matches what the store procedure is returning, you can use that too.

DummyTable           FILE,DRIVER('MSSQL'),OWNER(GBL:Owner),NAME('dbo.DummyTable'),PRE(DUM),BINDABLE,THREAD !                    
UniqueKey                KEY(DUM:DUMUnique),NOCASE,PRIMARY !Unique Key          
Record                   RECORD,PRE()
Dummy01                     CSTRING(257)                   !                    
Dummy02                     CSTRING(257)                   !                    
Dummy03                     CSTRING(257)                   !                    
Dummy04                     CSTRING(257)                   !                    
Dummy05                     CSTRING(257)                   !                    
Dummy06                     CSTRING(257)                   !                    
Dummy07                     CSTRING(257)                   !                    
Dummy08                     CSTRING(257)                   !                    
Dummy09                     CSTRING(257)                   !                    
Dummy10                     CSTRING(257)                   !                    
Dummy11                     CSTRING(257)                   !                    
Dummy12                     CSTRING(257)                   !                    
Dummy13                     CSTRING(257)                   !                    
Dummy14                     CSTRING(257)                   !                    
Dummy15                     CSTRING(257)                   !                    
Dummy16                     CSTRING(257)                   !                    
Dummy17                     CSTRING(257)                   !                    
Dummy18                     CSTRING(257)                   !                    
Dummy19                     CSTRING(257)                   !                    
Dummy20                     CSTRING(257)                   !                    
Dummy21                     CSTRING(257)                   !                    
Dummy22                     CSTRING(257)                   !                    
Dummy23                     CSTRING(257)                   !                    
Dummy24                     CSTRING(257)                   !                    
Dummy25                     CSTRING(257)                   !                    
Dummy26                     CSTRING(257)                   !                    
Dummy27                     CSTRING(257)                   !                    
Dummy28                     CSTRING(257)                   !                    
DUMUnique                   LONG                           !Unique Key Field    
                         END
                     END

NickP · February 7, 2022, 1:10pm

Exactly!

I’d just add that strings need to be surround with single quotes, and this is achieved by concatenating <39> + string + <39>.
eg In the following, TP:ContactName is a CString (nvarchar):
LOC:SQL = ‘Call procSetUpTests (’ & TP:IDTestPiece & ‘, <39>’ & TP:ContactName & ‘<39>)’

N

Marc_Brierley · February 21, 2022, 12:43am

Thanks for the help guys.

Graeme · June 27, 2022, 1:57pm

Hi Rick, thanks for your great example as it has helped me get my first stored proc called from my app in testing.

Any chance you could give me some pointers on passing a date to the stored proc. I am looking at passing a clarion date (RTIPSSTARTDATE). I have seen some discussions about using FORMAT(RTIPSSTARTDATE,@d10-) but for the life of me can’t get it working (still a relative newbie to MSSQL).

Thanks Graeme

I call stored proc via - sqlFile{Prop:Sql} = ‘Call dbo.DM2022Statistics’

In the stored proc I have-

–parameters for the stored procedure here

@RTIPSSTARTDATE DATE

–Part of the where statement

where DATE_NOTF > @RTIPSSTARTDATE

Jane · June 27, 2022, 9:03pm

I’m lazy and use @d12, Graeme.

Here’s a prop:sql statement for a proc (LOC:SQL is a cstring in this case). The date is sent as a string and SQL will do an implicit conversion to the parameter(s) for the stored procedure.

LOC:SQL = 'CALL pressGaneyAppProviderFilter(<39>' & FORMAT(pStartDate,@d12) &'<39>,<39>' & FORMAT(pEndDate,@d12) &'<39>,<39>' &pSurveyType& '<39>)'

Rick_UpperPark · June 27, 2022, 10:57pm

Hi Graeme,

I use @D10-.
Here is a header of a sp that takes a date time.

CREATE PROCEDURE [dbo].[shGetDailyActivityTotals]
@WKDUnique INT,
@WorkDate  DATETIME,
@PDAUnique INT,
@BIMUnique INT
AS
BEGIN

This is a code example calling it.

DummyTable{PROP:SQL} = 'Call dbo.shGetDailyActivityTotals(' & WKD:WKDUnique & ',<39>' & format(WKD:WorkDate, @D10-) & '<39>,' & self.ItemsQ.PDAUnique & ',' & self.ItemsQ.ItemUnique & ')'

I’ve since moved to adding a SQLDate format method to a utility class. Then you don’t have to do the <39> or remember the format.
The above becomes this:

DummyTable{PROP:SQL} = 'Call dbo.shGetDailyActivityTotals(' & WKD:WKDUnique & ',' & gc:util.SQLDate(WKD:WorkDate) & ',' & self.ItemsQ.PDAUnique & ',' & self.ItemsQ.ItemUnique & ')'

Graeme · June 28, 2022, 12:00am

Thanks so much Jane, I have it working perfectly.