December2000
asked on
Error converting INT to NVARCHAR in TSQL
Hi experts I am getting an data error on this stored procedure.... how do I cast the int to a nvarchar?
ALTER PROCEDURE [dbo].[Sp_2] (@CompanyId NVARCHAR(10) = '',
@StartDate DATETIME = '',
@EndDate DATETIME = '',
@CostTransState INT = ''--
)
AS
SET nocount ON;
SELECT Trans.costtransstate,--
'PO'
AS PPVType,
Trans.voucher,
Trans.itemid,
Item.itemgroupid,
Trans.transrefid
AS RefId,
Purch.invoiceaccount
AS VendId,
Trans.transdate,
( Cost.price + Cost.markup / CASE Cost.priceqty
WHEN 0 THEN 1
ELSE Cost.priceqty
END ) * CASE
WHEN costtransstate = 1 THEN onhandqty
ELSE accruedqty
END +
TransVarSum.costamountposted
AS
PurchAmount,
CASE
WHEN costtransstate = 1 THEN onhandqty
ELSE accruedqty
END
AS Qty,
itempricerefrecid
AS CostRecId,
Purch.purchname
AS VendName,
( Cost.price + Cost.markup / CASE Cost.priceqty
WHEN 0 THEN 1
ELSE Cost.priceqty
END )
AS CostTotal,
( TransVarSum.costamountposted )
AS PPV
FROM inventcosttrans Trans
INNER JOIN (SELECT Sum(TransVar.costamountposted) CostAmountPosted,
TransVar.inventcosttransrefrecid
FROM inventcosttransvariance AS TransVar
WHERE TransVar.account = '51003'
GROUP BY TransVar.inventcosttransrefrecid) AS
TransVarSum
ON Trans.recid = TransVarSum.inventcosttransrefrecid
INNER JOIN inventtable Item
ON Trans.dataareaid = Item.dataareaid
AND Trans.itemid = Item.itemid
INNER JOIN purchtable Purch
ON Trans.dataareaid = Purch.dataareaid
AND Trans.transrefid = Purch.purchid
INNER JOIN inventitemprice Cost
ON Trans.dataareaid = Cost.dataareaid
AND Trans.itempricerefrecid = Cost.recid
WHERE Trans.dataareaid = @CompanyID
AND Trans.transdate >= @StartDate
AND Trans.transdate <= @EndDate
AND TransVarSum.costamountposted != 0
AND costtransstate = @CostTransState
UNION
SELECT Data2.*,
(SELECT TOP 1 name
FROM vendtable
WHERE dataareaid = @CompanyId
AND accountnum = vendid) AS VendName,
Cost.price + Cost.markup AS CostTotal,
0 AS PPV
FROM (SELECT Data1.itemtype,--,
Data1.ppvtype,
Data1.voucher,
Data1.itemid,
Data1.itemgroupid,
Data1.refid,
Data1.vendid,
Data1.transdate,
( Data1.purchprice * Data1.qty + ppvmisc ) AS PurchAmount,
( Data1.qty ) AS Qty,
(SELECT TOP 1 Cost1.recid
FROM inventitemprice Cost1
INNER JOIN inventdim Dim1
ON Cost1.dataareaid = Dim1.dataareaid
AND Cost1.inventdimid = Dim1.inventdimid
WHERE Cost1.dataareaid = @CompanyId
AND Dim1.inventsiteid = Data1.siteid
AND Dim1.inventsizeid = Data1.sizeid
AND Cost1.pricetype = 0
AND Cost1.itemid = Data1.itemid
AND Cost1.activationdate <= transdate
ORDER BY recid DESC) AS CostRecId
FROM (SELECT Item.itemtype,--
'CONS' AS PPVType,
Trans.voucher,
Item.itemid,
CASE
WHEN Dim.inventsiteid LIKE '%C' THEN
DimTo.inventsiteid
ELSE Dim.inventsiteid
END AS SiteId,
'' AS SizeId,
Item.itemgroupid,
Trans.journalid AS RefId,
'' AS VendId,
Trans.transdate,
Trans.inventtransid,
'' AS InvoiceId,
'' AS PackingSlipId,
Trans.customerownedprice AS PurchPrice,
0 AS PPVMisc,
CASE
WHEN NOT EXISTS(SELECT recid
FROM ledgertrans
WHERE dataareaid = @CompanyId
AND voucher = Trans.voucher
AND accountnum = '51003')
THEN 0
WHEN Dim.inventsiteid LIKE '%C' THEN Trans.qty * -1
ELSE Trans.qty
END AS Qty,
'' AS LastField
FROM inventtable Item
INNER JOIN inventjournaltrans Trans
ON Item.dataareaid = Trans.dataareaid
AND Item.itemid = Trans.itemid
INNER JOIN inventjournaltable TransHeader
ON Trans.dataareaid = TransHeader.dataareaid
AND Trans.journalid =
TransHeader.journalid
AND TransHeader.consignmentjournal = 1
INNER JOIN inventdim Dim
ON Trans.dataareaid = Dim.dataareaid
AND Trans.inventdimid = Dim.inventdimid
-- Inventory Dimension To
INNER JOIN inventdim DimTo
ON Trans.dataareaid = DimTo.dataareaid
AND Trans.toinventdimid =
DimTo.inventdimid
WHERE Item.dataareaid = @CompanyId
AND Item.itemtype IN ( 0 )
AND Trans.transdate >= @StartDate
AND Trans.transdate <= @EndDate
AND Item.itemgroupid = 'RM') Data1) Data2
LEFT OUTER JOIN inventitemprice Cost
ON Cost.dataareaid = @CompanyId
AND Cost.recid = Data2.costrecid
WHERE purchamount <> 0
OR ( Cost.price + Cost.markup ) <> 0
ORDER BY itemgroupid,
itemid,
transdate,
refid,
ppvtype 
I just noticed that your are defaulting your parameters to odd values:
@StartDate DATETIME = '',
@EndDate DATETIME = '',
@CostTransState INT = ''
Since you don't do any validation on these values that I can see I would suggest defaulting them to something viable:
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@CostTransState INT = NULL
...
SELECT @StartDate = ISNULL(@StartDate, GETDATE()),
@EndDate = ISNULL(@EndDate, GETDATE()),
@CostTransState = ISNULL(@CostTransState, 0)
@StartDate DATETIME = '',
@EndDate DATETIME = '',
@CostTransState INT = ''
Since you don't do any validation on these values that I can see I would suggest defaulting them to something viable:
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@CostTransState INT = NULL
...
SELECT @StartDate = ISNULL(@StartDate, GETDATE()),
@EndDate = ISNULL(@EndDate, GETDATE()),
@CostTransState = ISNULL(@CostTransState, 0)
On a side note, you should change the name of your stored procedure so that it doesn't start with "sp_". The sp_ prefix stands for System Procedure, and it should not be used as prefix for regular procedures. If you do, it will first make an extra trip to the master database each time to look for the procedure, and if it would have the same name as one of the procedures there, that procedure will be executed instead of your procedure.
ASKER
Thank you @BriCrowe... Where do you place the cast statement in the stored procedure and do you have to Declare and Set it?
You would place it wherever you refer to that variable:
WHERE Trans.dataareaid = @CompanyID
AND Trans.transdate >= @StartDate
AND Trans.transdate <= @EndDate
AND TransVarSum.costamountpost
AND costtransstate = CAST(@CostTransState as nvarchar(20))
But if you're doing this each time then why is the parameter a different type than the column it is comparing against? Also feel free to change the size to one that matches that of costtranstate
WHERE Trans.dataareaid = @CompanyID
AND Trans.transdate >= @StartDate
AND Trans.transdate <= @EndDate
AND TransVarSum.costamountpost
AND costtransstate = CAST(@CostTransState as nvarchar(20))
But if you're doing this each time then why is the parameter a different type than the column it is comparing against? Also feel free to change the size to one that matches that of costtranstate
ASKER
How do I cast the parameter?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Now SSRS 2005 says ---Cannot read the next row for the data set ReportData.
Conversion failed when converting the nvarchar value '1,2' to data type int.
Conversion failed when converting the nvarchar value '1,2' to data type int.
>> The sp_ prefix stands for System Procedure <<
sp actually stands for "special".
sp actually stands for "special".
ASKER
@ScottPletcher Thank you
ASKER
Good information but didn't fix my problem... Thank you :)
Generally speaking...
CAST(myIntValue as nvarchar(20))