SQL Server Stored Procedure

HCL Domino Domino Forum
1

How can we call a SQL Server Stored procedure from Notes, pass parameters and get an output, using Lotus Script agent? Any code or any pointers will help.

Thanks very much.

2

Subject: SQL Server Stored Procedure

Seach on my posts on this or 4/5 forum. I use LSXLC.

3

Subject: SQL Server Stored Procedure

read the documentation on basic ODBC usage in LotusScript. You need a good graps of the Connection, Query and ResultSet objects.

Include the ODBC library with Uselsx “*LSXODBC”

When defining your query to call a stored proc, do something like this:

Qry.SQL = “{CALL sp_example('” + strAgentCode + “')}”

(With LotusScript you can build parameterized queries, but I’ve found manual

construction of the query to be faster and more flexible)

When retrieving the resultset, you may find an array of variants necessary if

multiple types are being retrieved. avResult (below) is a 2-d variant array.

           'Create ResultSet Object

		If Res Is Nothing Then          

			Set Res = New ODBCResultSet       

		End If

		

           'Assign Query to ResultSet 

		Set Res.Query = Qry

		

           'Execute Query          

		bLastRetCode = Res.Execute

		

           'Exit if Execute failed

		If bLastRetCode = False Then                    

			strErrorMessage = Res.GetExtendedErrorMessage

		Else                                          

			Res.LastRow

			nRows = Res.NumRows          

			

			If nRows > 0 Then

				Res.FirstRow          

				

                    'do not return more results than can be handled

				If nRows > Ubound(avResult) Then

					nRows = Ubound(avResult)

				End If        

				

				Dim i As Integer

				For i = 0 To nRows - 1

avResult(i,COMMTYPE) = Res.GetValue “commissionType”)

avResult(i,LIFELASTYR) = Res.GetValue(“lifelastyr”)

etc…

This just a snippet: I’ve left out the else and end if and so on.