Stored procedure and variable
lazarus / 16-Databases

----------------------------------------------------------------------
Steen · 2015-06-19 17:32

I have some problems to retrieve the variables value from a strored
procedure.

Trying the following: (@Var1-3 input, @Var4 output)

  SQLQuery.Close;
  SQLQuery.SQL.Text := 'exec MyProcedure @Var1=1, @Var2=2, @Var3=3';
  SQLQuery.ExecSQL;

   S := SQLQuery.ParamByName('@Var4').AsInteger;

  With this i don't get anything? What have i done wrong?


Sql server MSSQL 2014, and lazarus 1.4.0

----------------------------------------------------------------------
exdatis · 2015-06-20 12:05

try:
sqlQuery.Sql.Text:= 'select * from procedure_name(:param1, :param2,
:par...)';
sqlQuery.Params[0].AsIneger(or something else):= your_value;
or sqlQuery.ParamByName('param1').as... etc
after open, you can use result

----------------------------------------------------------------------
Steen · 2015-06-21 12:34

When i do that i get a:

Invalid object name 'MyProcedure'.

The same results i also get in Microsoft SQL Server Management, where
i try the same line.

But if i do this in Microsoft SQL Server Management
Exec Myprocedure @Var1=0, @Var2=0, Var3=1  I get the results???

----------------------------------------------------------------------
exdatis · 2015-06-21 13:25

I do not understand. Have you stored procedure defined on M$SqlServer
with name MyProcedure? If you have this then you can use sqlQuery to
select what you want from storedProcedure.
See:
http://wiki.freepascal.org/SqlDBHowto#How_to_use_parameters_in_a_query
.3F
http://wiki.freepascal.org/Working_With_TSQLQuery
or
http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial#Lazarus_a
nd_MSSQL

----------------------------------------------------------------------
exdatis · 2015-06-21 13:30

http://lazarus.lazarus.freepascal.narkive.com/uaQVQa6D/sqldb-not-able-
to-retrieve-data-from-stored-procedures

----------------------------------------------------------------------
exdatis · 2015-06-21 13:34

Or at first:
http://forum.lazarus.freepascal.org/index.php/topic,26084.msg160356.ht
ml#msg160356

----------------------------------------------------------------------
Steen · 2015-06-21 13:58

Thank you for your kindness

I have made a test Stored procedure with the name "MyProcedure"

USE [DAT]
GO
/****** Object:  StoredProcedure [dbo].[MyProcedure]    Script Date:
21-06-2015 15:48:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      mig
-- Create date:
-- Description:   
-- =============================================
ALTER PROCEDURE [dbo].[MyProcedure]
   -- Add the parameters for the stored procedure here
   @p1 int = 2,
   @p2 int = 4
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT on;
   DECLARE @c int = 3
    DECLARE @d int = @p1 + @p2
    -- Insert statements for procedure here
   SELECT @c as ABC, @d
END

But when i use a 'select * from MyProcedure' and use .open i get :
Invalid object name 'MyProcedure'.

When i make a ' exec MyProcedure' and use .ExeSql i get nothing?
And i try   s := SQLQuery1.ParamByName('ABC').AsString; I get error
Also s := SQLQuery1.FieldByName('ABC').AsString; i get error

----------------------------------------------------------------------
jc99 · 2015-06-21 14:44

Could you try the fully qualified version:
Code: [Select]SELECT * from dbo.MyProcedure(1,2);
in the SQL-MMS - Query
What happens ?

----------------------------------------------------------------------
Steen · 2015-06-21 15:00

Also result in : Invalid object name 'MyProcedure'.

----------------------------------------------------------------------
jc99 · 2015-06-21 15:49

Oh yes, that's right. You cannot select on a Procedure.
Try
Code: [Select]EXEC dbo.MyProcedure(1, 2);
in MMS
if it works the Code should be:
Code: [Select]sqlQuery.Sql.Text:= 'EXEC dbo.MyProcedure(:param1,
:param2)';
sqlQuery.Params[0].AsIneger:= <your_value>;
[...]
sqlQuery.open;
The Result is in the dataset.

----------------------------------------------------------------------
Steen · 2015-06-21 15:54

IF i use:

SQLQuery.SQL.Text := 'exec MyProcedure'; 
SQLQuery.CLOSE;

 s := SQLQuery1.Fields[0].AsString;  ->Correct
 t := SQLQuery1.Fields[1].AsString;   -> Correct

Then i get output variables. BUT i miss my input then.

SQLQuery1.SQL.Text := 'exec MyProcedure(P1,P2) Values (1,2)'; or
SQLQuery1.SQL.Text := 'exec MyProcedure(1,2)';

Then i get "Incorrect syntax near  P1

----------------------------------------------------------------------
Steen · 2015-06-21 16:03

This works:

SQLQuery1.SQL.Text := 'exec MyProcedure @p1=1, @p2=2'
SQLQuery.CLOSE;

 s := SQLQuery1.Fields[0].AsString;  ->Correct
 t := SQLQuery1.Fields[1].AsString;   -> Correct

Thanks for all reply...... I am a happy man now.

----------------------------------------------------------------------
LacaK · 2015-06-22 12:32

Quote from: Steen on June 21, 2015, 06:03:38 pmThis works:

SQLQuery1.SQL.Text := 'exec MyProcedure @p1=1, @p2=2'
SQLQuery.CLOSE;

CLOSE ? not OPEN ?
How can CLOSE execute stored procedure and return correct result ?

Btw. read about output parameters if you use TMSSQLConnection:
http://wiki.freepascal.org/mssqlconn#Limitations_and_tips