0

Hi can you help me please?, That way I can return the result of a query string in a variable inside a function with "execute"?.

for example

create function my _funcion(in @ num_celular char (20), in @ str_comando char (120))
returns char (120)
- on exception resume
as
begin

set @ exec_qry = Select 1 from dummy
execute (@ exec_qry)

return (@ the return value of the query "execute")

end

go

thanks

demongolem
  • 9,474
  • 36
  • 90
  • 105
user2932523
  • 1
  • 1
  • 2

2 Answers2

1

Try this way:

create function my_funcion(@num_celular char (20), @str_comando char (120))
returns char(120)
as
begin
DECLARE @Result char(120)

execute('select @Result = ''Aaa'' ' )

RETURN @Result

end

go

Result of below query 

select my_funcion('AA','BB')

is 

Aaa
Robert
  • 25,425
  • 8
  • 67
  • 81
0

Ok, thanks for answering my question, but look at the issue is much more complex because we are running a Dynamic Sql, formed in while loop to the query is then stored in one string variables and running it, the problem are the function promptly made ​​as follows and is attached below.

create function ms_trafico.fn_mov_cambia_tag( in @num_celular char(20), in @str_comando char(120) )
returns char(120)
--on exception resume
as
begin
  set nocount on
  set temporary option string_rtruncation='OFF'

  --aplican para isql
  --set option isql_print_result_set=ALL
  --set option isql_show_multiple_result_sets=On

  declare @comando varchar(100)
  declare @exec_qry varchar(500)
  declare @cadena varchar(60)
  declare @tag char(15)
  declare @columna char(20)
  declare @pos int
  declare @strCadFinal char(150)
  declare @strFono char(20)

  set @comando = trim(@str_comando)
  set @strFono = trim(@num_celular)
  set @exec_qry='select XXX '
  set @pos = 1
  print "comando " + @comando

  while(@pos <= locate(@comando,'<',-1))
  begin

    set @tag = substr(@comando,locate(@comando,'<',@pos),CHARINDEX('>', SUBSTR(@comando,locate(@comando,'<',@pos),length(@comando))))
    set @pos = locate(@comando,'<',@pos) + 1
    select atributo_campo into @columna from TB_MOV_MAPEO_COMANDOS where parametro = @tag
    set @cadena="replace(XXX,'"||trim(@tag)||"',"||trim(@columna)||")"
    set @exec_qry=replace(@exec_qry,'XXX',@cadena)
  end

  set @comando="'"||trim(@comando)||"'"
  set @exec_qry=replace(@exec_qry,'XXX',@comando)
  set @exec_qry=@exec_qry||' as comando INTO @strCadFinal FROM VM_ALL_SERVICES_MOVIL where num_celular=@strFono'

  execute (@exec_qry)

  return (@strCadFinal) "The result of my query with execute method"!!!!!!!!!!

end
go

Thanks for you attention.

user2932523
  • 1
  • 1
  • 2