how do I call this procedure?
-
i tried to convert a function into a procedure but i can't find a way to make the call work
ALTER PROCEDURE procJoinColuna
@coluna varchar( 2000 ),
@valor varchar( 2000 ),
@delimiter char( 1 ),
@saida varchar( 2000 ) OUTPUT
AS
SET @saida = ''
DECLARE @cr_rolename varchar( 2000 )
DECLARE @cr_sql varchar( 2000 )
...the code...
SET @saida = SUBSTRING( @saida, 2, LEN( @saida ) )
RETURN( @saida )
--RETURN
GO
exec dbo.procJoinColuna 'rolename','14;0;-1;-2;',';' OUTPUT SELECT @saida << this doesn't work
exec dbo.procJoinColuna 'rolename','14;0;-1;-2;',';' << this doesn't work either Any idea? O.o []s
-
ALTER PROCEDURE procJoinColuna
@coluna varchar( 2000 ),
@valor varchar( 2000 ),
@delimiter char( 1 ),
@saida varchar( 2000 ) OUTPUT
AS
SET @saida = ''
DECLARE @cr_rolename varchar( 2000 )
DECLARE @cr_sql varchar( 2000 )
...the code...
SET @saida = SUBSTRING( @saida, 2, LEN( @saida ) )
GO
exec dbo.procJoinColuna 'rolename','14;0;-1;-2;',';', @saida OUTPUT
select @saida
-
aah now seems to call the proc. but it errors. i don't know why.
ALTER PROCEDURE procJoinColuna
@coluna varchar( 2000 ),
@valor varchar( 2000 ),
@delimiter char( 1 ),
@saida varchar( 2000 ) OUTPUT
AS
BEGIN
SET @saida = ''
DECLARE @cr_rolename varchar( 2000 )
DECLARE @cr_sql varchar( 2000 )
SET @cr_sql = 'DECLARE cursor_join CURSOR FOR SELECT DISTINCT( '+ @coluna +' ) FROM Split2Table( '+ @valor +', '+ @delimiter +' )'
EXECUTE( @cr_sql )
OPEN cursor_join
FETCH NEXT FROM cursor_join INTO @cr_rolename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @saida = @saida + '-' + @cr_rolename
FETCH NEXT FROM cursor_join INTO @cr_rolename
END
CLOSE cursor_join
DEALLOCATE cursor_join
SET @saida = SUBSTRING( @saida, 2, LEN( @saida ) )
END
declare @x varchar(2000)
exec dbo.procJoinColuna 'rolename','14;0;-1;-2;',';', @x OUTPUT select @x
---------------------
Server: Msg 170, Level 15, State 1, Line 1
Linha 1: sintax error near ';'.
Server: Msg 16916, Level 16, State 1, Procedure procJoinColuna, Line 15
there's no cursor named 'cursor_join'.
-
The scope of the cursor starts and ends in the exec() statement, you must declre it global if you want to use it after the exec... or include the while loop in the exec.
-
...and you must tripple the quotation marks between the concatenation so that the exec statement is valid.
-
triple '''?
how come? I dont use any inside the query. see above:
DECLARE cursor_join CURSOR FOR SELECT DISTINCT(@coluna) FROM Split2Table(@valor,@delimiter)
As i understand there's no need for a ''' here
I still don't get your point. please could you show me what you mean?
ty
[]s
-
You're concatenating a string for the server to execute. This string must be a valid statement or else it fails.
try print [concatenation here] and see if you can execute that statement.
-
ah i did some tests and now it works
SET @cr_sql = 'DECLARE cursor_join CURSOR FOR SELECT DISTINCT( '+ @coluna +' ) FROM Split2Table( '''+ @valor +''', '''+ @delimiter +''' )'
So even if the argument is a variable, i still need to let them inside the ''s
thank you very much! Now I can make the dyamic query to work among the cursor, with a proc with arguments that return a value, working just like a function
From my previous languages I learned that procs never returns values and functions always do. Why with tsql it is diferent? procs and functions are the same then?
[]s
-
They can both return something...
I don't have a list of difference but there are a lot of small differences between those 2.
-
/cry
now i found another issue.
as a function I could call like this:
select myfunction(args),fields from table
now with the procedure call like this:
declare @x varchar(2000)
exec dbo.procJoinColuna 'rolename','14;0;-1;-2;',';', @x OUTPUT select @x
this code just outputs the result. I needed to put them inside another outside query like
select @x, y from table
any way to save the result into a variable or something that i could call from a query like I could do with functions?
[]s
-
Not with a sp, you'd have to use a function for that, but dynamic sql is forbiden in functions (in the current conditions).
Now you know why you should stay away from that design when you have the chance
.Looks like you'll have to use some sort of loop to fetch the results in a temp table and then join that to your main select to present the data.
-
oh boy thats sad
((If I can make a proc work like a function but can't use it results anywhere it what's the point
i need to run it on demand, like select myjoin( delimitedfield, delimiter )
inside of the main query.
Would it be possible to call a proc from a function then? The value passed must be dynamic somehow.
I don't know what to do!
If I use functions I can't concat the field into the cursor's query because it can't run exec in a function.
If I use procedures, I can't call its result from the main query because I would have to call the proc first to generate a temp table, then call the main query, which is more code for client application.
All that I needed was concat the cursor query inside the function! that would be the perfect way! call it from the main query and voi la. all there completely transparent.
((like: 'declare cursor as select ' + @fieldname + 'from table'
why why this is impossible??
((( whyyyyyyy?? ((((( -
oh boy thats sad ((
Yup
If I can make a proc work like a function but can't use it results anywhere it what's the point
The point of the function is to be able to do most of the stuff a SP does and to be able to use it in a select, not the other way around.
i need to run it on demand, like select myjoin( delimitedfield, delimiter )
inside of the main query.
Would it be possible to call a proc from a function then? The value passed must be dynamic somehow.
Can't call an SP from a function.
I don't know what to do!
If I use functions I can't concat the field into the cursor's query because it can't run exec in a function.
If I use procedures, I can't call its result from the main query because I would have to call the proc first to generate a temp table, then call the main query, which is more code for client application.
All that I needed was concat the cursor query inside the function! that would be the perfect way! call it from the main query and voi la. all there completely transparent. ((
like: 'declare cursor as select ' + @fieldname + 'from table'
why why this is impossible?? ((( whyyyyyyy?? (((((
Already answered all that, use a sp to populate the #temp table with the all results required, then you can select from the temp table. That's the simplest way left unless you can change the design.
-
i dont know how to do this.
the main query will bring many rows, and for each row I will have to get the delimited ids converted to their descriptions, i see no way to do that. /cry
i can't stop my query in midle, to exec a proc for each record. it is too complicated. i needed that encapsulated, transparent to the query, do u understand?
i feel like tied hands. I heard very bad about tsql in the pass, now I'm forced to code with it, and i'm already feeling it is even worse than people said
-
I feel your pain.
TSQL is great, the design is awfull, that's where you're having problems.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply