Stored procedure error conversion SQL Server

Question

I want to write a SQL Server stored procedure to retrieve pivoted result from this query

ALTER procedure [dbo].[sp_get_list_penerimaan_pks]
    (@tahun int,
     @bulan int,
     @pks int)
as  
begin
    SET NOCOUNT ON;

    declare @cols AS NVARCHAR(MAX); 
    declare @query  AS NVARCHAR(MAX);

    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.asal) 
            FROM (
              SELECT
                cpo.tanggal,
                dg.asal,
                (((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0))))) )-(((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))))) * 0.02)) 
                as harga_beli_tbs_bersih
              FROM
                cpopko cpo
              LEFT JOIN DAILY_GUU AS dg ON CONVERT (datetime, dg.tglolah, 103) = cpo.tanggal
              LEFT JOIN PNL_TR_HARGA_KOMODITI AS ko ON ko.tanggal = cpo.tanggal
              WHERE
                YEAR (cpo.tanggal) >= @tahun and pks=@pks and month(cpo.tanggal)=@bulan
            ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

    set @query = 'SELECT tanggal, ' + @cols + ' from 
            (
                  SELECT
                  cpo.tanggal,
                  dg.asal,
                  (((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0))))) )-(((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))))) * 0.02)) 
                  as harga_beli_tbs_bersih
                FROM
                  cpopko cpo
                LEFT JOIN DAILY_GUU AS dg ON CONVERT (datetime, dg.tglolah, 103) = cpo.tanggal
                LEFT JOIN PNL_TR_HARGA_KOMODITI AS ko ON ko.tanggal = cpo.tanggal
                WHERE
                  YEAR (cpo.tanggal) >= '+@tahun +' and pks=@pks and month(cpo.tanggal)=@bulan
           ) x
            pivot 
            (
                 max(harga_beli_tbs_bersih)
                for asal in (' + @cols + ')
            ) p '

    execute(@query)
end

but I got this error result :

Procedure execution failed 22018 - [SQL Server]Conversion failed when converting the nvarchar value 'SELECT tanggal, [54],[11],[56],[53],[40],[21],[12] from ( SELECT cpo.tanggal, dg.asal, (((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))) )-(((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0)))))) * 0.02)) as harga_beli_tbs_bersih FROM cpopko cpo LEFT JOIN DAILY_GUU AS dg ON CONVERT (datetime, dg.tglolah, 103) = cpo.tanggal LEFT JOIN PNL_TR_HARGA_KOMODITI AS ko ON ko.tanggal = cpo.tanggal WHERE YEAR (cpo.tanggal) >= ' to data type int.

I'm totally confused about this error. I already do some research from google but still cant fix the issues. Hope you guys can help me - thanks in advance.

Pரதீப் · Accepted Answer · 2016-12-02 06:28:10Z

2

The error is due concatenating the integer @tahun variable with string. Since INT has higher precedence over Varcharthe string will implicitly converted to INT so the error. So you need to convert the @tahun explicitly to varchar

YEAR (cpo.tanggal) >= '+cast(@tahun as varchar(50))+'

Also you have to do the same to renaming variables used in the query. But I will prefer to use SP_EXECUTESQL to pass value to variables

SET @query = 'SELECT tanggal, ' + @cols
             + ' from 
            (
                  SELECT
                  cpo.tanggal,
                  dg.asal,
                  (((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) + ((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0))))) )-(((((cpo - ongkos_ms)* CAST(NULLIF(dg.minyak_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))) +((pko - ongkos_is)* CAST(NULLIF(dg.inti_efektif,0) as float) / (NULLIF(dg.tbs_diolah, 0))))-(447.6 * ((CAST(NULLIF(dg.minyak_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))+(CAST(NULLIF(dg.inti_efektif,0) as float)  / (NULLIF(dg.tbs_diolah, 0)))))) * 0.02)) 
                  as harga_beli_tbs_bersih
                FROM
                  cpopko cpo
                LEFT JOIN DAILY_GUU AS dg ON CONVERT (datetime, dg.tglolah, 103) = cpo.tanggal
                LEFT JOIN PNL_TR_HARGA_KOMODITI AS ko ON ko.tanggal = cpo.tanggal
                WHERE
                  YEAR (cpo.tanggal) >= @tahun  and pks=@pks and month(cpo.tanggal)=@bulan
           ) x
            pivot 
            (
                 max(harga_beli_tbs_bersih)
                for asal in (' + @cols
             + ')
            ) p '

EXEC Sp_executesql
  @query,
  N'@tahun int,@pks int,@bulan int',
  @tahun = @tahun,
  @pks = @pks,
  @bulan = @bulan

edited Dec 2, 2016 at 6:28

answered Dec 2, 2016 at 2:11

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jsnow Over a year ago

i already cast all params in '@query' string to varchar but no result coming out. do i need to cast the params in '@cols' too sir?. what is the difference between execute and Sp_executesql thnks be4 sir

Pரதீப் Over a year ago

@Jsnow - Pass the parameter values to static query and check whether it is returning any records. SP_EXECUTESQL allows you to pass values to variables inside dynamic query it avoids SQL Injection. Also you can get the result out of dynamic query to variable. Check here for more info msdn.microsoft.com/en-us/library/ms188001.aspx

Jsnow Over a year ago

this pivoted table got dynamic column based on value in previous table. from what i learn to return something we can use function, but to return table value we have to predefined the temporary table along with its column. how can we make a temporary table in function where its column is dynamic based on query result. thanks in advance sir.

Collectives™ on Stack Overflow

Stored procedure error conversion SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related