mardi 8 décembre 2015

Dynamic SQL Error - Set value to a parameter

I have a dynamic sql.

declare @CustomerId int
set @CustomerId = 1
declare @SDate datetime 
set @SDate = '2015/12/07'
declare @ItemId int
set @ItemId = 2

declare @QtyS nvarchar(max)
declare @QtyOut decimal(18,3)

set @QtyS = 'SELECT isnull(sum(d.Qty),0)
FROM InvoiceDetail AS d INNER JOIN
InvoiceHeader AS h ON d.InvoiceNo = h.InvoiceNo
where
h.CustomerId = '''+@CustomerId+''' and
d.itemmasterid = '''+@ItemId+''' and
h.Deleted = 0 and
h.invoicedate = '''+@SDate+''''

exec sp_executesql @QtyS, N'@Qty decimal(18,3) out', @QtyOut out
select @QtyOut

when executing this attached error coming. Error as a text also attached with this. Any idea?

Error:
Msg 245, Level 16, State 1, Procedure rpt_SpecialLaunch, Line 76
Conversion failed when converting the varchar value 'SELECT isnull(sum(d.Qty),0)
    FROM InvoiceDetail AS d INNER JOIN
    InvoiceHeader AS h ON d.InvoiceNo = h.InvoiceNo
    where
    h.CustomerId = '' to data type int.

enter image description here

Aucun commentaire:

Enregistrer un commentaire