sp_executesql help

ssjaronx4

Hi Guys,

Can someone help me with the below dynamic SQL Query. What I am trying to do is put 4 variables in and get the result out and into a varible. I'm getting a conversion error which I assume is coming from the fact that the column with the result is an int data type and my query is in nvarchar however I'm not sure how to work around that.


DECLARE @SISPartSizeQuery NVARCHAR(MAX)
DECLARE @FingerPrintDB VARCHAR(MAX)
DECLARE @MemberTable VARCHAR(MAX)
DECLARE @CurrentFPHashPart1 INT
DECLARE @SISPartSize INT
DECLARE @SISPartCompressedSize INT
DECLARE @CO NVARCHAR(MAX)
DECLARE @SISPartSizeQueryParams NVARCHAR(MAX)

SET @FingerPrintDB = 'EVVSGVSG1_1_1'
SET @MEMBERTABLE = 'MemberTable_166'
SET @CurrentFPHashPart1 = '1049365198'
SET @CO = 'StoreSize'



SET @SISPartSizeQuery = N'SELECT '+ @CO +' FROM '+ @FingerPrintDB +'.dbo.'+ @MemberTable +
N' WHERE FPHashPart1 = '+ @CurrentFPHashPart1
SET @SISPartSizeQueryParams = N'@Result INT OUTPUT'

EXECUTE sp_executesql @SISPartSizeQuery ,@SISPartSizeQueryParams, @Resuult=@SISPartSize OUTPUT


ERROR
Conversion failed when converting the nvarchar value 'SELECT StoreSize FROM EVVSGVSG1_1_1.dbo.MemberTable_166 WHERE FPHashPart1 = ' to data type int.

king26

i think you might get more help at sql cental forums

RobertKaucher

ssjaronx4 wrote: »

Hi Guys,

Can someone help me with the below dynamic SQL Query. What I am trying to do is put 4 variables in and get the result out and into a varible. I'm getting a conversion error which I assume is coming from the fact that the column with the result is an int data type and my query is in nvarchar however I'm not sure how to work around that.


DECLARE @SISPartSizeQuery NVARCHAR(MAX)
DECLARE @FingerPrintDB VARCHAR(MAX)
DECLARE @MemberTable VARCHAR(MAX)
DECLARE @CurrentFPHashPart1 INT
DECLARE @SISPartSize INT
DECLARE @SISPartCompressedSize INT
DECLARE @CO NVARCHAR(MAX)
DECLARE @SISPartSizeQueryParams NVARCHAR(MAX)

SET @FingerPrintDB = 'EVVSGVSG1_1_1'
SET @MEMBERTABLE = 'MemberTable_166'
SET @CurrentFPHashPart1 = '1049365198'
SET @CO = 'StoreSize'



SET @SISPartSizeQuery = N'SELECT '+ @CO +' FROM '+ @FingerPrintDB +'.dbo.'+ @MemberTable +
N' WHERE FPHashPart1 = '+ @CurrentFPHashPart1
SET @SISPartSizeQueryParams = N'@Result INT OUTPUT'

EXECUTE sp_executesql @SISPartSizeQuery ,@SISPartSizeQueryParams, @Resuult=@SISPartSize OUTPUT


ERROR
Conversion failed when converting the nvarchar value 'SELECT StoreSize FROM EVVSGVSG1_1_1.dbo.MemberTable_166 WHERE FPHashPart1 = ' to data type int.

The problem is exactly what the error message says. You need to cast the INT types to nvarchar. When you are building your query text it is not automatically converting the INT type to a text type.