Options
sp_executesql help
ssjaronx4
Member Posts: 37 ■■□□□□□□□□
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.
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.
Comments
-
Optionsking26
Registered Users Posts: 4 ■□□□□□□□□□
i think you might get more help at sql cental forums
-
Options
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
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.