Stored Procedure form mssqlserver

Databases
1

Hi

You could tell me if with xojo can be called a stored procedure that returns a single value or returns rows of data

Thank you very much and good day

Greetings from Costa Rica

2

Sure. Just do:

dim SQL as string=“Select * from MyFancyFunction()”
Dim Rs as Recordset=MyConnection.SQLSelect(SQL)
if MyConnection.error=false then
…

3

thank you very much Maximilian, thanks to your help and probe a function that returns rows of data:

CREATE FUNCTION dbo.ExampleTop (
@top integer
)
RETURNS TABLE
AS RETURN (SELECT top (@top) * FROM MyTable)

and then xojo

db.Host = “dsg07”
db.DatabaseName = “siawin0”
db.UserName = …
db.Password = …
If db.connect Then
rs = db.SQLSelect ( “SELECT * FROM ExampleTop (2)”)

Worked perfect, now you can well help as making a sqlserver function that returns a string value and not a table

Thank you and good night

PD: sorry for my English

4

Something like:

Create FUNCTION dbo.ExampleTop
(
@ID BIGINT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @RESULT_STRING nvarchar(max);
// som computation
RETURN @RESULT_STRING
END

But you’d need to take a deep dive into The MSSQL-Server documentation anyway. I can’t claim any expertise in the area of Transact-SQL.

5

did you try

select @result_string

6

If you do connect with MBS SQL Plugin we can call stored procedures without SELECT statement.

7

Many thanks, Christian thanks , i will check mbs plugin

insurance is not the best way but also helps me

I put it if helps others

CREATE Function dbo.Example(@ID Int)
RETURNS
@temp TABLE (sMessage Varchar(10))
AS
BEGIN
//your code

INSERT INTO @temp VALUES (‘My Message’)
RETURN

8

Russ Lunn Many thanks , I tried it but it did not work