|
| [WD 20] MS SQL: call a stored procedure with OUTPUT |
| Iniciado por guest, 27,oct. 2015 19:42 - 11 respuestas |
| |
| | | |
|
| |
| Publicado el 27,octubre 2015 - 19:42 |
Hi all,
I have a very simple stored procedure like this one:
CREATE PROCEDURE MyProc ( -- Add the parameters for the stored procedure here @p1 int, @p2 int, @res int output ) AS BEGIN SET NOCOUNT ON; SET @res = @p1 + @p2 END GO
In SQL we can call it in this way:
Declare @Result INT; EXEC MyProc @p1 = 5, @p2 = 4, @Res = @Result OUTPUT;SELECT @Result
And the result is, of course, 9.
Now I'm trying the same with WD20, keep in mind that I have no analysis, just a connection to SQL (native access). The only way to get this running is using this:
dsQuery is Data Source sQuery is string = "Declare @@Result INT; " + ... "EXEC MyProc " + EDT_Param1 + ", " + EDT_Param2 + ", @@Result OUTPUT ;" + ... "SELECT @@Result " IF HExecuteSQLQuery(dsQuery, gctMyConnection, hQueryWithoutCorrection, sQuery) THEN HReadFirst(dsQuery) Info(HRetrieveRecord(dsQuery)) ELSE Error(HErrorInfo(hErrFullDetails)) Trace(sQuery) END
But I want to send the parameters of the stored procedure, I mean @p1 and @p2, like this:
sQuery is string = "Declare @@Result INT; " + ... "EXEC MyProc @p1 = " + EDT_Param1 + ", @p2 = " + EDT_Param2 + ", @res = @@Result OUTPUT ;" + ... "SELECT @@Result "
However this does not work and I'm getting an error. (http://screencast.com/t/R2kUfT77). Is there a way to get this working?
Thanks in advance.
Mauricio |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 10:15 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 10:40 |
Hi Paulo,
I've already tried that and it didn't work. I guess that case is when you have your stored procedure defined in your analysis. In my case, there is no analysis. Maybe I'm wrong but I couldn't find how to define the parameters in my data source, it gave me an error.
Thanks. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 10:51 |
No, I don't have any stored procedure in my analysis and it works.
I'm using it in WD18 andWB18 with DB-Library, i never used it with SQLnCli.
Use the the code of the example to test, your code is diferent. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 13:08 |
Try this:
CREATE FUNCTION add2int
(
@p1 int,
@p2 int
)
returns int
AS
BEGIN
return @p1 + @p2
END
GO
// just by using wdSql to run as below, you'll get 3
select dbo.add2int(1,2)
HTH
King |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 18:17 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 18:20 |
| @kingdr, thanks for your suggestion but that's not the point. I know that the sp can be replace by a function in this case but the real sp insert or update some tables (something that you can't do inside a function) and I wanted to show a simple example. The point is just how to call the sp without passing all the parameters in the right order, the real one has more than 10 and to read what you're sending becomes a bit complex. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 19:07 |
Maurico,
As i told you before i only have WD18, i can't open your project (WD20).
My code in WD18 & MSSQL 2005 to run your procedure is:
IF NOT HDescribeConnection("MY_CONN","myuser","mypass","myserver","mydb",hNativeAccessSQLServer,hOReadWrite) THEN
Error("Error in HDescribeConnection",ErrorInfo())
RETURN
END
IF NOT HOpenConnection("MY_CONN") THEN
Error("Error in HOpenConnection",ErrorInfo())
RETURN
END
MyProcedure is Data Source
MyProcedure.p1 = 10
MyProcedure.p2 = 20
IF HExecuteSQLQuery(MyProcedure, "MY_CONN", hQueryWithoutCorrection, "MyProc @p1, @p2 ,@res out") THEN
Trace("res=" + MyProcedure.res)
Trace("p1=" + MyProcedure.p1)
Trace("p2=" + MyProcedure.p2)
ELSE
Error("Error in procedure",HErrorInfo())
END |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2015 - 19:30 |
@Paulo, you're right. I was calling the sp like this:
"MyProc" (taken from example) and it has to be like this: "MyProc @p1, @p2, @res OUTPUT"
Docs confused me about that, this syntax is not for native SQL driver but it works for me so it's fine.
Thanks for your help and I'm sorry for the example, I forgot you're in WD18.
BR,
Mauricio |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,octubre 2015 - 01:53 |
Hi Mauricio,
I work for several years with WD + MS SQL and OLEDB connection.
Never I used "output" in store procedure.
The "output" can be a single SELECT.
Try this, even if you use PC SOFT native SQL Server Access.
CREATE PROCEDURE dbo.MyProc
@p1 int,
@p2 int
AS
SET NOCOUNT ON
DECLARE @res INT
SET @res = @p1 + @p2
SELECT
@res AS MySum
****************************
WLanguage code
****************************
dS is Data Source
n_p1, n_p2, n_Result are int
s_CmdSQL is string
n_p1 = 2
n_p2 = 3
s_CmdSQL = "EXEC dbo.MyProc "+n_p1 + "," + n_p2
IF HExecuteSQLQuery(dS,g_Cnn,hQueryWithoutCorrection,s_CmdSQL) = False THEN
Error(HErrorInfo(hErrFullDetails))
RETURN
END
HReadFirst(dS,hNoRefresh)
n_Result = dS.MySum
HFreeQuery(dS)
Info(n_Result)
Regards,
JJM |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,octubre 2015 - 03:26 |
m_conn is Connection
m_conn..Provider = hOledbSQLServer
m_conn..Server = "192.168.1.1"
m_conn..Database = "mydb"
m_conn..User = "sa"
m_conn..Password = "pass1"
// Connection to the database
IF HOpenConnection(m_conn) = False THEN
Error(HErrorInfo())
RETURN
END
m_qry is Data Source
M_sql is string = [
Declare @@Result INT;
EXEC MyProc @p1 = %1, @p2 = %2, @res = @@Result OUTPUT ;
SELECT @@Result as cnt ;
]
M_sql = StringBuild(M_sql, EDT_Param1..value, EDT_Param2..value)
IF NOT HExecuteSQLQuery(m_qry, m_conn, hQueryWithoutCorrection+hQueryWithoutHFCorrection, M_sql)
Error(HErrorInfo())
ELSE
HReadFirst(m_qry)
Info(m_qry.cnt )
END |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,octubre 2015 - 06:58 |
| @Jorge, my sp was just an example. There are already several sp that I cannot change because the current system is working. I just wanted to use in an easy way those sp. Paulo suggestion works fine, I'll try CCC2 now but I can see it's different to what we have in docs so maybe docs are not a good example in this case. |
| |
| |
| | | |
|
| | | | |
| | |
|