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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy