get a value from an MS SQL Server stored procedure

Databases
1

Hello friends
Xojo
Very grateful for your attention and your answers to solve the following:

I need to get a value from an MS SQL SERVER stored procedure and I am performing a test.
Code in sql server

USE [INVENTARIO]
GO
/****** Object:  StoredProcedure [dbo].[sp_productosinsout]    Script Date: 28/11/2019 10:07:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_productosinsout](@codprod char(10),
@nomprod varchar(50),
@valor char(10) output
)
As
begin
 INSERT INTO Productos (codprod,nomprod) VALUES(@codprod,@nomprod)
 SET @valor='0000000020'
 RETURN @valor
End

Code in Xojo

Dim sql,valor As String
valor=""
sql = "inventario.dbo.sp_productosinsout '001', 'LIBRO','"+valor+"'"
Dim rs As recordset=app.pDb.SQLSelect(sql)
If rs <> Nil then
  MsgBox(" El valor es: ")+rs.Field("valor").StringValue
Else
  MsgBox "registro nulo "
End If

The data sent is recorded perfectly, in the database but does not return any value

Your answers will be greatly appreciated.
Cordially,

Raul Juarez Pulache

2

I would rewrite both your Stored Procedure and the Xojo code to be more like the following:

ALTER procedure [dbo].[sp_productosinsout]( @codprod char(10), @nomprod varchar(50) ) As BEGIN DECLARE @valor char(10) INSERT INTO Productos (codprod,nomprod) VALUES(@codprod,@nomprod) SET @valor = '0000000020' SELECT @valor As valor END

Dim sql As String sql = "inventario.dbo.sp_productosinsout '001', 'LIBRO'" Dim rs As recordset=app.pDb.SQLSelect(sql) If rs <> Nil then MsgBox(" El valor es: ")+rs.Field("valor").StringValue Else MsgBox "registro nulo " End If

Disclaimer: Although I work in SQL Server for a living, I haven’t used it yet with Xojo. So I’m not sure how a T-SQL output parameter should be handled when called in Xojo, because normally an output parameter also requires the OUTPUT keyword suffix when the Stored Procedure is executed.

But hopefully the above works for you.

3

A RETURN in a sql stored procedure is not returned as a set of rows so a sqlselect will not retrieve it

The variation Scott suggests should work and return a single row

4

Infinite Thank you
Scott Cadillac
Norman Palardy

For your great help.
Indeed, by including the modifications recommended by Scott and with the inclusion of the following line of code:
SET NOCOUNT ON

It all worked great
Then I leave the following code for someone who needs it, as a guide:

Code in sql server

USE [INVENTARIO]
GO
/****** Object:  StoredProcedure [dbo].[sp_productosinsout]    Script Date: 28/11/2019 01:30:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_productosinsout](@codprod char(10),
@nomprod varchar(50)
)
As
begin
 SET NOCOUNT ON; 	
 
 INSERT INTO Productos (codprod,nomprod) VALUES(@codprod,@nomprod)

 SELECT codprod AS cod,nomprod As nom FROM productos WHERE codprod='004'

End

Code in Xojo

Dim sql As String

sql = "inventario.dbo.sp_productosinsout '004', 'COMPUTADORA'"
Dim rs As recordset=app.pDb.SQLSelect(sql)
If rs <> Nil then
   MsgBox(" El nombre es: ")+rs.Field("nom").StringValue
Else
  MsgBox "registro nulo "
End If

blessings to all

Raul Juarez Pulache

5

[quote=465272:@Raul Juarez Pulache]Indeed, by including the modifications recommended by Scott and with the inclusion of the following line of code:
SET NOCOUNT ON[/quote]
Ah, indeed. How did I miss that? Good catch Raul. And you’re welcome :slight_smile:

Whenever I create a new Stored Procedure, that is usually the first line I write because most applications are not necessarily concerned with the number of rows affected by the internal activity of the procedure - just the end result, if there is one.

6

Infinite Thank you
Scott Cadillac

I really appreciate your great help
Best regards

Raul Juarez Pulache

7

Perhaps this…

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES(‘bob’)

8

Yes for sure, an OUTPUT clause on INSERT would work in a simple example like this. But for myself, I typically only use this syntax for a stand-alone T-SQL statement because of some quirky limitations imposed by OUTPUT on more complex queries.

Although, with that said, I like to use it with OUTPUT INTO a table variable sometimes for other logic in the same Stored Procedure. I use a lot of Stored Procedures in my work. It saves having to write too much web application code, for better overall performance.