[RESOLVED] MS SQL - Function -> Transpose data into table

Hi

I have a table structure returning field names and values...

I have a need to transpose this data via a function in MS SQL, but having very limited experience and knowledge of tsql, I wanted to check whether it is possible before looking into how...

What I have atm

Code:

---

CREATE FUNCTION [dbo].[GetTabelaParametro] ( @nomeparam varchar(200), @idparametrovlr int )
        RETURNS @tabela TABLE ( 
            nome varchar(200),
            valor varchar(200)
        )
   
    AS
    BEGIN
     
        INSERT INTO @tabela ( nome, valor )
           
        SELECT
                pvi.Nome,
                pvi.valor
       
        FROM
                parametrosnew pn
INNER JOIN parametrovlrsnew pvn ON pn.idparametro = pvn.idparametro
INNER JOIN parametrovlrsitens pvi ON pvn.IdParametroVlr = pvi.IdParametroVlr
    WHERE
                pn.nomeparam = @nomeparam
        AND pvn.IdParametroVlr = @idparametrovlr
       
        RETURN
     
    END

---

Which just does the extract.

Question:
With the 'returns' can I just declare it to be a table and not specify columns?

I'm not sure what you're asking for exactly...

The direct answer is: yes... you can define the return as table, and then build a temp table or a table var in the function and then RETURN {the table} ... but I'm still not 100% sure what the end result should be. I should note that you CANNOT do any indeterministic actions in a function - no dynamic SQL, or alterations to tables or any structure that could cause a change to the state of any object.

If you can give an example of what you're getting and what you want, I might be able to figure something out.

-tg

Ok..
Hmm.. I'm starting with the structure of the table I want to output. This is held in a table like the following

Code:

---

id, idparam,thename,thevalue
1,1,Test,1
2,1,Test 2,'Something'

---

I'd like the output to be

Code:

---

Test,Test 2
1,'Something'

---

But there may be one or more rows to move to columns

I have another table with the structure to use

Code:

---

id, idparam, thename, type, obligatory
1,1,Test,Numeric,N
2,1,Test 2,String,S

---

And I am currently trying some other tsql to get this to generate the table... Then perhaps I can loop the data and insert it into the temp table to return..

Code:

---

DECLARE @tabela TABLE( id int );
DECLARE @thename varchar(200);
DECLARE @thetipo varchar(200);
DECLARE @theid int;
DECLARE @coltipo varchar(200);

DECLARE @nomeparam varchar(200);
SET @nomeparam = 'Teste';
SET @theid = 1;

DECLARE thecursor CURSOR FOR
                SELECT
                        pe.nome,
                        pe.tipodado
                FROM
                        parametrosnew pn
                INNER JOIN parametroestrutura pe ON pn.idparametro = pe.idparametro
                WHERE
                        pn.nomeparam = @nomeparam;
           
OPEN thecursor;
FETCH NEXT FROM thecursor INTO @thename,@thetipo;
WHILE @@FETCH_STATUS = 0
        BEGIN
               
        SELECT @coltipo = CASE @thetipo
                WHEN 'Numeric' THEN 'int'
            WHEN 'String'  THEN 'varchar(255)'
            WHEN 'Text'      THEN 'varchar(8000)'
            WHEN 'DECIMAL'    THEN 'decimal'
            WHEN 'DECIMAL (2)' THEN 'decimal(16,2)'
        END;

            SELECT @thename,@thetipo,@coltipo
       
--                ALTER @tabela ADD @thename @coltipo
            FETCH NEXT FROM thecursor INTO @thename,@thetipo;
    END;
CLOSE thecursor;
DEALLOCATE thecursor;

---

** I note that the cursor (thecursor) cannot have an @ sign... not sure why

I think what you need is a PIVOT... but it isn't that simple. Pivots work best when the col names are known. It's possible to pivot on dynamic columns, but you also still have to know how many ... which can be a bit tricky.
That said, anything you're doing, isn't going to be viable in a function. An SProc, sure, but not a function.

theCursor doesn't need the @ because it isn't a variable. It's a pointer to the table. It essentially becomes an object itself.

-tg

Hi tg,

I cannot use a pivot because a) need the names of the columns and b) the values might be strings or numbers... and I dont think the pivot will work with those.

Thank you for the info on the @ :) makes sense

Quote:

---

Originally Posted by Ecniv

Hi tg,

I cannot use a pivot because a) need the names of the columns and b) the values might be strings or numbers... and I dont think the pivot will work with those.

Thank you for the info on the @ :) makes sense

---

if you bracket the names, it can [LikeThis] ... or [100Bottles] or [1]
Just google "t-sql dynamic pivot" ... I'd post an example myself, but I'm in the middle of training.

-tg

Hi tg,

Thanks, but I'm pretty sure the dynamic pivot won't work with the data I have.
I don't have value values, I have strings, dates or values (numbers).

I found one example that may work...
Dynamic Pivot but again relies on having values.


On the other code I am closer... but cannot get the variable to be a table without knowing its columns.

Code:

---

DECLARE @thename varchar(200);
DECLARE @thetipo varchar(200);
DECLARE @theid int;
DECLARE @coltipo varchar(200);
DECLARE @sql varchar(8000);

DECLARE @nomeparam varchar(200);
SET @nomeparam = 'Teste';
SET @theid = 1;

DECLARE thecursor CURSOR FOR
                SELECT
                        pe.nome,
                        pe.tipodado
                FROM
                        parametrosnew pn
                INNER JOIN parametroestrutura pe ON pn.idparametro = pe.idparametro
                WHERE
                        pn.nomeparam = @nomeparam;
           
OPEN thecursor;
FETCH NEXT FROM thecursor INTO @thename,@thetipo;
WHILE @@FETCH_STATUS = 0
        BEGIN
               
        SELECT @coltipo = CASE @thetipo
                WHEN 'Numeric' THEN 'int'
            WHEN 'String'  THEN 'varchar(255)'
            WHEN 'Text'      THEN 'varchar(8000)'
            WHEN 'DECIMAL'    THEN 'decimal'
            WHEN 'DECIMAL (2)' THEN 'decimal(16,2)'
            WHEN 'Data'        THEN 'datetime'
        END;

--                SET @sql = COALESCE( @sql + ',' , '' ) + @thename + ' '+ @coltipo;
                SET @sql = COALESCE( @sql + ',' , '' ) + ''''' AS '+ @thename;

            SELECT @thename,@thetipo,@coltipo;
       
            FETCH NEXT FROM thecursor INTO @thename,@thetipo;
    END;
CLOSE thecursor;
DEALLOCATE thecursor;

--SET @sql = 'DECLARE TABLE @tabela ('+@sql+');'
SET @sql = 'SELECT '+@sql

SELECT @sql;

---

The above generates a simple sql to be executed. But I cannot seem to get the result into a variable (as you need to have a type and table needs the columns).

The commented bit gets me the columns and data types, but I couldn't get them to be returned as a table even using exec (it didnt put it in a variable).

I think I'm missing something...

Can you use a temp table? Does it HAVE to be a variable? If you use a #Temp table, you can alter it to your heart's content.

-tg

I've not used tsql much.

How do I create that ?

If I create a temp table, is it easy to get rid of it afterwards? or does it linger?

creating one is the same as creating any table:
Create table #TableName ... followed by the collumns... getting rid of it is as simple as a Drop Table #TableName ...
By making it a # table, it will be created in the tempDb and only exists for the length of the connection... by default they should go away when done and you close things, but I've always dropped mine just to be explicit about it. If you open it, close it. If you create it, drop it... If you make a mess, clean it up.

-tg

Right.

ok. So just

Code:

---

CREATE TABLE #tmp_data ( id int );

---

I can alter it afterwards using variables? To add the columns?

With some dynamic SQL? Yup. I've done it a few times. If I can find the case where I did that, I'll snag it and post a variation.

-tg

Hi

Thanks.

I used your idea to do the temporary table. Which works when I run it.

But when I put it in a function it complains about accessing temporary tables?

Current Sql...

Code:

---

--DROP FUNCTION GetTabelaParametro
CREATE FUNCTION [dbo].[GetTabelaParametro]
        ( @nomeparam varchar(200), @idpv int)
                RETURNS TABLE
   
    AS
BEGIN
DECLARE @thename varchar(200);
DECLARE @thetipo varchar(200);
DECLARE @theid int;
DECLARE @thevalor varchar(8000);
DECLARE @coltipo varchar(200);
DECLARE @sql varchar(8000);
DECLARE @currentid int;
DECLARE @aspa varchar(1);

-- isso dois ir passar
--DECLARE @nomeparam varchar(200);
--DECLARE @idpv int;

-- Debugging / teste
SET @nomeparam = 'Teste';
SET @idpv = 1;


CREATE TABLE #tmp_dados ( id int );

DECLARE thecursor CURSOR FOR
                SELECT
                                pe.nome,
                                pe.tipodado
                  FROM
                                  parametrosnew pn
    INNER JOIN        parametroestrutura pe ON pn.idparametro = pe.idparametro
                  WHERE
                                pn.nomeparam = @nomeparam;
           
-- pegar colunas pra adiciona de tabela temporario           
OPEN thecursor;
FETCH NEXT FROM thecursor INTO @thename,@thetipo;
WHILE @@FETCH_STATUS = 0
        BEGIN
               
        SELECT @coltipo = CASE @thetipo
                WHEN 'Numeric' THEN 'int'
            WHEN 'String'  THEN 'varchar(255)'
            WHEN 'Text'      THEN 'varchar(4000)'
            WHEN 'DECIMAL'    THEN 'decimal'
            WHEN 'DECIMAL (2)' THEN 'decimal(16,2)'
            WHEN 'Data'        THEN 'datetime'
        END;

                SET @sql = COALESCE( @sql + ',' , '' ) +'['+ @thename + '] '+ @coltipo;

-- mostrar dados pegado
--            SELECT @thename,@thetipo,@coltipo;
       
            FETCH NEXT FROM thecursor INTO @thename,@thetipo;
    END;
CLOSE thecursor;
DEALLOCATE thecursor;

-- adiciona colunas
SET @sql = 'ALTER TABLE #tmp_dados ADD '+@sql;
EXEC( @sql );

DECLARE thecursor CURSOR FOR
                SELECT
                            pi.IdParametroVlr,
                                        pi.nome,
                                        pi.Valor,
                    pe.tipodado
                   
                        FROM
                                        parametrosnew pn
      INNER JOIN        parametrovlrsnew pvn ON pn.idparametro = pvn.idparametro
          INNER JOIN        parametrovlrsitens pi ON pvn.IdParametroVlr = pi.IdParametroVlr
      INNER JOIN    parametroestrutura pe ON pn.idparametro = pe.idparametro
                                                                      AND pi.nome = pe.nome
     
                  WHERE
                                            pn.nomeparam = @nomeparam
            AND        (COALESCE( @idpv,0)=0 OR pvn.IdParametroVlr=@idpv );

OPEN thecursor;
FETCH NEXT FROM thecursor INTO @theid,@thename,@thevalor,@thetipo;
SET @currentid = -1;
SET @sql = '';
WHILE @@FETCH_STATUS = 0
        BEGIN

                IF @currentid!=@theid
                        BEGIN
                    if LEN(@sql)>0
                        BEGIN
                                        SET @sql = 'UPDATE #tmp_dados SET '+@sql+' WHERE id='+@theid+';';
                                EXEC( @sql );
                        END
                        SET @sql='INSERT INTO #tmp_dados ( id ) VALUES ( '+@theid+' );';
                EXEC( @sql );
                                SET @currentid = @theid;
                SET @sql="";
                        END
       
        SELECT @aspa = CASE @thetipo
            WHEN 'String' THEN ''''
            WHEN 'Text'  THEN ''''
            WHEN 'Data'  THEN ''''
            ELSE ''
        END;
       
        SET @sql = COALESCE(@sql+',','')+'['@thename+']='+@aspa+@thevalor+@aspa;

-- mostrar dados pegado
--        SELECT @theid, @thename, @thevalor;

            FETCH NEXT FROM thecursor INTO @theid,@thename,@thevalor;
    END;
CLOSE thecursor;
DEALLOCATE thecursor;

-- ultimo atualizado
if LEN(@sql)>0
        BEGIN
                SET @sql = 'UPDATE #tmp_dados SET '+@sql+' WHERE id='+@theid+';';
                EXEC( @sql );
        END

RETURN
        SELECT * FROM #tmp_dados
END

---

Would it be better to make it a stored procedure instead?

Temp tables can't be used in functions, and neither can dynamic SQL, so yes, it needs to be done in an sproc. Functions have to be deterministic... they can look up data, they can do calculations, they can manipulate variables and local data, but they cannot change anything on the server. They cannot update, they can't delete, they cannot create or drop objects.

-tg

Ah ok. Thanks. I'll change it to a proc.

Getting closer (seems I still have some errors in the sql... but almost works now :)

Will post once its working...

Thanks again

Ok...

One last thing.

I've moved it to a stored procedure to manipulate the temp table
Whilst testing on execution works, but when trying to add to the stored procedure it complains.

The question:
A stored procedure is just that, a procedue; and therefore it connot return data.
A function is read only (ie can grab data and return it but cannot mess with the tables etc...
If I run a stored procedure from a function will it be able to return the temp table created in the stored procedure?

You can add an output parameter if needed but I just do a select and that will return the data

a stored proc can most certainly return data...
considerr this:

Code:

---

create procedure usp_GetSampleData
as
begin
  select Fld1, fld2, fld3 from aTable
end

---

That returns data... now watch this

Code:

---


declare @SomeTable table (F1 nvarchar(max), F2 nvarchar(max), F3 nvarchar(max))
insert into @SomeTable exec usp_GetSampleData
select * from @SomeTable

-- also works (I think):
exec usp_GetSomeData
into #tmpTable -- this should create a new temp table you can use based on the data returned by the sproc

---

Quote:

---

If I run a stored procedure from a function will it be able to return the temp table created in the stored procedure?

---

You won't be able to call the sproc... because the sproc contains non-deterministic actions (temp table, dynamic sql)...

I have a feeling a function just isn't going to cut it for you in this case.

-tg

Almost huh... it at least compiles and is added to ms sql. However I cannot seem to call it... :/

Code:

---

--DROP PROCEDURE GetTabelaParametro
CREATE PROCEDURE [dbo].[GetTabelaParametro]
        @nomeparam varchar(200),
    @idpv int
   
    AS
DECLARE @thename varchar(200);
DECLARE @thetipo varchar(200);
DECLARE @theid int;
DECLARE @thevalor varchar(8000);
DECLARE @coltipo varchar(200);
DECLARE @sql varchar(8000);
DECLARE @currentid int;
DECLARE @aspa varchar(1);

-- isso dois ir passar
--DECLARE @nomeparam varchar(200);
--DECLARE @idpv int;

-- Debugging / teste
--SET @nomeparam = 'Teste';
--SET @idpv = 1;


CREATE TABLE #tmp_dados ( id int );

DECLARE thecursor CURSOR FOR
                SELECT
                                pe.nome,
                                pe.tipodado
                  FROM
                                  parametrosnew pn
    INNER JOIN        parametroestrutura pe ON pn.idparametro = pe.idparametro
                  WHERE
                                pn.nomeparam = @nomeparam;
           
-- pegar colunas pra adiciona de tabela temporario           
OPEN thecursor;
FETCH NEXT FROM thecursor INTO @thename,@thetipo;
WHILE @@FETCH_STATUS = 0
        BEGIN
               
        SELECT @coltipo = CASE @thetipo
                WHEN 'Numeric' THEN 'int'
            WHEN 'String'  THEN 'varchar(255)'
            WHEN 'Text'      THEN 'varchar(4000)'
            WHEN 'DECIMAL'    THEN 'decimal'
            WHEN 'DECIMAL (2)' THEN 'decimal(16,2)'
            WHEN 'Data'        THEN 'datetime'
        END;

                SET @sql = COALESCE( @sql + ',' , '' ) +'['+ @thename + '] '+ @coltipo;

-- mostrar dados pegado
--            SELECT @thename,@thetipo,@coltipo;
       
            FETCH NEXT FROM thecursor INTO @thename,@thetipo;
    END;
CLOSE thecursor;
DEALLOCATE thecursor;


-- adiciona colunas
SET @sql = 'ALTER TABLE #tmp_dados ADD '+@sql;
EXEC( @sql );
/*
-- mudar o dados pra tabela temporario
DECLARE thecursor CURSOR FOR
                SELECT
                            pi.IdParametroVlr,
                                        pi.nome,
                                        pi.Valor,
                    pe.tipodado
                   
                        FROM
                                        parametrosnew pn
      INNER JOIN        parametrovlrsnew pvn ON pn.idparametro = pvn.idparametro
          INNER JOIN        parametrovlrsitens pi ON pvn.IdParametroVlr = pi.IdParametroVlr
      INNER JOIN    parametroestrutura pe ON pn.idparametro = pe.idparametro
                                                                      AND pi.nome = pe.nome
     
                  WHERE
                                            pn.nomeparam = @nomeparam
            AND        (COALESCE( @idpv,0)=0 OR pvn.IdParametroVlr=@idpv );

OPEN thecursor;
FETCH NEXT FROM thecursor INTO @theid,@thename,@thevalor,@thetipo;
SET @currentid = -1;
SET @sql = '';
WHILE @@FETCH_STATUS = 0
        BEGIN

                IF @currentid != @theid
                        BEGIN
           
                    if LEN(@sql)>0
                        BEGIN
                                        SET @sql = 'UPDATE #tmp_dados SET '+@sql+' WHERE id='+CONVERT(varchar,@theid)+';';
                                EXEC( @sql );
                        END
                 
                        SET @sql='INSERT INTO #tmp_dados ( id ) VALUES ( '+CONVERT(varchar,@theid)+' );';
                EXEC( @sql );
               
                                SET @currentid = @theid;
                SET @sql = null;
                        END


        SELECT @aspa = CASE @thetipo
            WHEN 'String' THEN ''''
            WHEN 'Text'  THEN ''''
            WHEN 'Data'  THEN ''''
            ELSE ''
        END;
       
        SET @sql = COALESCE(@sql+',','')+'['+@thename+']='+@aspa+COALESCE(@thevalor,'')+@aspa;

-- mostrar dados pegado
--        SELECT 'Pasado > ',@theid,@thename,@thevalor,@thetipo;

                FETCH NEXT FROM thecursor INTO @theid,@thename,@thevalor,@thetipo;
    END;
CLOSE thecursor;
DEALLOCATE thecursor;

-- ultimo atualizado
if LEN(@sql)>0
        BEGIN
                SET @sql = 'UPDATE #tmp_dados SET '+@sql+' WHERE id='+CONVERT(varchar,@theid)+';';
                EXEC( @sql );
        END
*/
SELECT * FROM #tmp_dados

---

Ok. I can EXEC it. Doesn't run right though, not returning data, but returning the empty table.

Back to the debugging...

Oh, is there a way to do a select statement on the exec
example > SELECT * FROM (EXEC spblah 'teste',1)



EDIT:
Ok my fault I commented out the bottom bit to make sure it was compiling. does return the table transposed now on EXEC.... But can I select that?

No... you can't select from the sproc... but you can dump the results into another table that you can then (or should) be able to select from... that's what I was showing in post #18... as long as the sproc returns a dataset, you can redirect that into a table...

IT seems hackish but that's because it is. SQL doesn't seem to want to react very well with dynamic objects and structures, and so it becomes a fight sometimes to get what you want out of it.

-tg

Right. Ok.

So I create a temp table in the Proc, then another in the calling of the proc to select and output.
:)

Found a few niggles in my tsql - mainly when nulls or data isn't as expected... final (fingers crossed) below

Code:

---

--DROP PROCEDURE GetTabelaParametro
CREATE PROCEDURE [dbo].[GetTabelaParametro]
        @nomeparam varchar(200),
    @idpv int
AS
BEGIN

  DECLARE @thename varchar(200);
  DECLARE @thetipo varchar(200);
  DECLARE @theid int;
  DECLARE @thevalor varchar(4000);
  DECLARE @valor varchar(4000);
  DECLARE @coltipo varchar(200);
  DECLARE @sql varchar(8000);
  DECLARE @currentid int;
  DECLARE @aspa varchar(1);
  DECLARE @where varchar(50);

-- isso dois ir passar
-- Debugging / teste
/*
DECLARE @nomeparam varchar(200);
DECLARE @idpv int;
    SET @nomeparam = 'Teste2.0';
    SET @idpv = 12;
*/

BEGIN TRY
  CREATE TABLE #tmp_dados ( id int );

  DECLARE thecursor CURSOR FOR
          SELECT
                  pe.nome,
                  pe.tipodado
            FROM
                  parametrosnew pn
      INNER JOIN        parametroestrutura pe ON pn.idparametro = pe.idparametro
          WHERE
                  pn.nomeparam = @nomeparam;

  -- pegar colunas pra adiciona de tabela temporario           
          OPEN thecursor;
          FETCH NEXT FROM thecursor INTO @thename,@thetipo;
          WHILE @@FETCH_STATUS = 0
              BEGIN

                  SELECT @coltipo = CASE @thetipo
                      WHEN 'Numeric' THEN 'int'
                      WHEN 'String'  THEN 'varchar(255)'
                      WHEN 'Text'      THEN 'varchar(4000)'
                      WHEN 'DECIMAL'    THEN 'decimal'
                      WHEN 'DECIMAL (2)' THEN 'decimal(16,2)'
                      WHEN 'Data'        THEN 'datetime'
                  END;

                  SET @sql = COALESCE( @sql + ',' , '' ) +'['+ @thename + '] '+ @coltipo;

  -- mostrar dados pegado
  --            SELECT @thename,@thetipo,@coltipo;

                  FETCH NEXT FROM thecursor INTO @thename,@thetipo;
              END;
          CLOSE thecursor;
          DEALLOCATE thecursor;


  -- adiciona colunas
          SET @sql = 'ALTER TABLE #tmp_dados ADD '+@sql;
          EXEC( @sql );



  -- mudar o dados pra tabela temporario
          SET @where = 'getting data';
          DECLARE thecursor CURSOR FOR
          SELECT
                      pi.IdParametroVlr,
                      pi.nome,
                      pi.Valor,
                      pe.tipodado

              FROM
                      parametrosnew pn
        INNER JOIN        parametrovlrsnew pvn  ON pn.idparametro = pvn.idparametro
        INNER JOIN        parametrovlrsitens pi ON pvn.IdParametroVlr = pi.IdParametroVlr
        INNER JOIN  parametroestrutura pe ON pn.idparametro = pe.idparametro
                                        AND pi.nome = pe.nome

            WHERE
                      pn.nomeparam = @nomeparam
              AND        (COALESCE( @idpv,0)=0 OR pvn.IdParametroVlr=@idpv );

          OPEN thecursor;
          FETCH NEXT FROM thecursor INTO @theid,@thename,@thevalor,@thetipo;
          SET @currentid = -1;
          SET @sql = '';
          WHILE @@FETCH_STATUS = 0
              BEGIN

                  IF @currentid != @theid
                      BEGIN
                          if LEN(@sql)>0
                              BEGIN
                                  SET @sql = 'UPDATE #tmp_dados SET '+@sql+' WHERE id='+CONVERT(varchar,@theid)+';';
                                  EXEC( @sql );
                              END

                          SET @sql='INSERT INTO #tmp_dados ( id ) VALUES ( '+CONVERT(varchar,@theid)+' );';
                          EXEC( @sql );

                          SET @currentid = @theid;
                          SET @sql = null;
                      END

                  SELECT @aspa = CASE @thetipo
                      WHEN 'String' THEN ''''
                      WHEN 'Text'  THEN ''''
                      WHEN 'Data'  THEN ''''
                      ELSE ''
                  END;

                  SET @valor = @aspa+COALESCE(@thevalor,'')+@aspa;

-- se dados é vazio ou null
                          IF @thevalor=''
                                  SET @valor=null;

-- se coluna esta datetime mas dados não é
                  IF @thetipo='Data' AND ISDATE(@thevalor)=0
                SET @valor=null;

                  SET @sql = COALESCE(@sql+',','')+'['+@thename+']='+COALESCE(@valor,'null');

  -- mostrar dados pegado
  --        SELECT 'Pasado > ',@theid,@thename,@thevalor,@thetipo;

                  FETCH NEXT FROM thecursor INTO @theid,@thename,@thevalor,@thetipo;
              END;
          CLOSE thecursor;
          DEALLOCATE thecursor;

  -- ultimo atualizado
          if LEN(@sql)>0
              BEGIN
                  SET @sql = 'UPDATE #tmp_dados SET '+@sql+' WHERE id='+CONVERT(varchar,@theid)+';';
                  EXEC( @sql );
              END

    SELECT * FROM #tmp_dados
  END TRY
  BEGIN CATCH
        SELECT
                ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage,
        @where AS ErrorWhere,
        @sql AS thesql,
        COALESCE(@thevalor,'[null]') TheValor
  END CATCH
END

---

Again, thanks for the help and pointers in the right direction