April 1, 2002 at 6:22 pm
Hi, I have a question,
Inside my procedure, I have to call a function.
but only when I code in this way:
databaseowner.functionname ( @passing_para )
then, my procedure can be compiled.
Is there any way to figure out and to code the databaseowner dynamically or to get rid of the
databaseowner at all when I call the function?
I can't hard code the databaseowner name when the code port to different machine server.
Thanks.
April 1, 2002 at 6:44 pm
Don't think so. I'd recommend either 'dbo' for the owner or explicitly have the object owned by a sql login that would never change.
Andy
April 1, 2002 at 6:54 pm
If no way to get rid of the databaseownername
when call a function, then I guess I have to change my function call to a procedure call. But
my function returns a table type, can procedure
return a table type? I tried but the code can't be compiled. If procedure can return a table type, what should be the correct syntax for calling the procedure?
Thanks.
April 2, 2002 at 7:05 am
First it is the Function Owner (which may be the same as the DB owner) and yes you have to specify it. If this is a problem then post your code and we can see if we can help. Also from BOL
quote:
table variables can be used in functions, stored procedures, and batches.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 2, 2002 at 5:24 pm
I have no problem to declare a table variable inside my procedure. But if I want my procedure to return a table variable, then, I always get the compiling error:
Server: Msg 156, Level 15, State 1, Procedure splitString, Line 3
Incorrect syntax near the keyword 'table'.
I am trying to split an input string ( '1|2|3|4|5|6' and insert those '|' separated values into a table variable and return the table variable
Here is my code:
create procedure splitString (
@string_in varchar,
@mv_return_table table (t_id int, item int ) OUT )
as
begin
declare @mv_temp_table table (t_id int, item int )
declare @mv_token varchar (10),
@remaining_list varchar (1000),
@pos int,
@mv_i int,
@remaining_len int
set @mv_i = 1
set @remaining_list = @string_in
set @pos = 0
set @remaining_len = len ( @remaining_list )
while ( @remaining_len > 0 AND
charindex ( '|', @remaining_list ) > 0 )
begin
set @pos = charindex ( '|', @remaining_list )
set @mv_token = substring ( @remaining_list, 1, @pos -1 )
set @remaining_list = substring (@remaining_list,
@pos + 1, @remaining_len )
set @remaining_len = len ( @remaining_list )
insert into @mv_temp_table ( t_id, item )
values (@mv_i, convert ( numeric, @mv_token ))
set @mv_i = @mv_i + 1
end /* end of while */
if len ( @remaining_list ) > 0
begin
insert into @mv_temp_table ( t_id, item )
values ( @mv_i, convert ( numeric, @remaining_list ) )
end
return 0
end
go
I always got the compiling error when I declare it as a procedure.
But if I change the procedure to a function:
CREATEFUNCTION splitString (
@string_in VARCHAR )
RETURNS @temp_table table (t_id int, item int )
/* the body part remaing the same as above */
then it gets compiled.
What is my problem?
Thank you very much.
Zishan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply