September 18, 2009 at 8:31 am
How can we create a parameter in a function as an output parameter. Typically, each of us adds a parameter in a function, it will automatically be input parameters.
Previously I apologize if you ask a little weird, but for me it makes sense but do not know how to do it.
So far, I often use the database in MS SQL Server is limited to the use of stored procedures and I rarely use the functions and almost never use it except for a few last time.
If I create a stored procedure and want to add the output parameters, I live add OUTPUT or OUT, as an example
CREATE PROCEDURE DBO.TEST1
@ INPUT1 VARCHAR (MAX) OUT
AS
...
...
If I create a function to do something similar to a stored procedure, the results I get are always fail. Examples of my query is like this:
CREATE FUNCTION test2 (
@ INPUT VARCHAR (MAX) OUT
)
...
...
I beg for help from my friends here to help me find a solution about adding the output parameters in function
September 18, 2009 at 10:48 am
why you want an output parameter?
why dont just return a scalar value.
September 18, 2009 at 10:57 am
a function is used in select, and returns a resultset (scalar or table, depends on the function type)
thats why a function cant use output parameters
i think this article would help you to know the user defined functions limitations
http://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations
hope this would help
September 18, 2009 at 11:33 am
Fausto Echevarria (9/18/2009)
why dont just return a scalar value.
http://msdn.microsoft.com/es-es/library/ms186755.aspx%5B/quote%5D
Why would I want the output parameters,
because I want to return the data more than
one other than those functions.
September 18, 2009 at 11:52 am
You cant use output parameters in functions
if i understand correctly you want to return more than one value, i.e.
you could use table functions...so you can return a table.
create function TableFunction(@parameter1 datatype, ..., @parameterN datatype)
returns @ReturnTable table (field1 datatype,...,fieldN datatype)
as begin
insert @ReturnTable values(@parameter1,...@parameterN);
return;
end
does that accomplish with your requirements.
September 18, 2009 at 12:03 pm
Fausto Echevarria (9/18/2009)
why dont just return a scalar value.
http://msdn.microsoft.com/es-es/library/ms186755.aspx%5B/quote%5D
Thank you very much for the information, I now understand why this happened and why I am from yesterday always get an error message. So now I've decided to not use a lot of output parameters. And if I want a lot of output parameters, I have to use stored procedures.
Once again, I say many thanks to Fausto Echevarria.
September 18, 2009 at 12:08 pm
you are very welcome. 🙂
September 18, 2009 at 1:07 pm
Fausto Echevarria (9/18/2009)
You cant use output parameters in functionsif i understand correctly you want to return more than one value, i.e.
you could use table functions...so you can return a table.
create function TableFunction(@parameter1 datatype, ..., @parameterN datatype)
returns @ReturnTable table (field1 datatype,...,fieldN datatype)
as begin
insert @ReturnTable values(@parameter1,...@parameterN);
return;
end
does that accomplish with your requirements.
Exactly, I mean like that. Using such concepts, database MS SQL Server engine can access and work properly, but when I access using Delphi programming language using TADOStoredProc, the return values can not be read by TADOStoredProc.
September 18, 2009 at 1:27 pm
i've never used Delphi before, so im not quite sure what the problem is... but i read a little, and i found something called TADOQuery... maybe you could execute a user defined function with TADOQuery
September 18, 2009 at 2:00 pm
Fausto Echevarria (9/18/2009)
i've never used Delphi before, so im not quite sure what the problem is... but i read a little, and i found something called TADOQuery... maybe you could execute a user defined function with TADOQuery
I think this problem occurs because not seeing the type of data and TADOStoredProc XXXX in TADOQuery. So, if TADOQuery can work well, of course in TADOStoredProc can work better, because here is more specific and structured.
But, before I would like to thank you because you are giving more attention to this problem.
September 18, 2009 at 2:46 pm
no problem :), i guess we all are here to help each other.
well im not sure if its like .NET but, when you execute a function it has to be used in a select
for example
we create this table function
create function t_sql_tvfPoints(@val1 float, @val2 float)
returns @points table (x float, y float)
as begin
insert @points values(@val1,@val2);
return;
end
Execute the function
select * from t_sql_tvfPoints(1,2)
the resultset is
x y
---------------------- ----------------------
1 2
so if you use it with TADOQuery then you gotta do something like this
Query1.SQL.Text:='select * from t_sql_tvfPoints(1,2)';
Query1.Open;
if you want to use it with TADOstoredproc you have to create a stored procedure with that function, i.e.
create procedure spt_sql_tvfPoints(@val1 float, @val2 float)
as
begin
select * from t_sql_tvfPoints(@val1,@val2)
end
spt_sql_tvfPoints 1,2
the resultset
x y
---------------------- ----------------------
1 2
so you can use it with TADOStoredProc
September 18, 2009 at 3:20 pm
Fausto Echevarria (9/18/2009)
no problem :), i guess we all are here to help each other.well im not sure if its like .NET but, when you execute a function it has to be used in a select
for example
we create this table function
create function t_sql_tvfPoints(@val1 float, @val2 float)
returns @points table (x float, y float)
as begin
insert @points values(@val1,@val2);
return;
end
Execute the function
select * from t_sql_tvfPoints(1,2)
the resultset is
x y
---------------------- ----------------------
1 2
so if you use it with TADOQuery then you gotta do something like this
Query1.SQL.Text:='select * from t_sql_tvfPoints(1,2)';
Query1.Open;
if you want to use it with TADOstoredproc you have to create a stored procedure with that function, i.e.
create procedure spt_sql_tvfPoints(@val1 float, @val2 float)
as
begin
select * from t_sql_tvfPoints(@val1,@val2)
end
spt_sql_tvfPoints 1,2
the resultset
x y
---------------------- ----------------------
1 2
so you can use it with TADOStoredProc
wow, this is amazing, I've managed to do so by using illustrations that you provide. And it turned out, what's done can not be done by TADOStoredProc be done properly by TADOQuery.
I do really thank you for your help and taking the time to solve this problem.
King regads,
Eko Indriyawan
September 18, 2009 at 3:41 pm
you're welcome 🙂 im gald i could help
see you next time.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply