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

[Ecniv]

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

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?

[techgnome]

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

[Ecniv]

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

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

I'd like the output to be

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

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..

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

[techgnome]

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

[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

[techgnome]

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

[Ecniv]

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.

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...

[techgnome]

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

[Ecniv]

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?

[techgnome]

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

[Ecniv]

Right.

ok. So just

CREATE TABLE #tmp_data ( id int );

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

[techgnome]

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

[Ecniv]

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...

--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?

[techgnome]

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

[Ecniv]

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

[Ecniv]

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?

[GaryMazzone]

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

[techgnome]

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

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

That returns data... now watch this

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

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

[Ecniv]

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

--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

[Ecniv]

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?

[techgnome]

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

[Ecniv]

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

--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