Conversion failed when converting the varchar value in SQLSERVER 2008
-
Hi this is Chandra. i have one issue with Sql Stored procedure. Please find the query below. declare @Measure varchar(50) declare @Time varchar(50) declare @Manufacturer varchar(50) declare @CategoryID int SET @Measure ='Value' SET @Time ='4 Weeks&' SET @Manufacturer ='Jti Gallaher Ltd' SET @CategoryID = 377; Declare @SELECTtQuery1 varchar(1000) Declare @FromQuery1 varchar(1000) Declare @GROUPBYQuery1 varchar(1000) Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000) SET @FromQuery1 = 'FROM [dbo].[iView_Data_TCG_Aggregated_Product] WHERE PeriodName = '''+@Time+''' AND Manufacturer = '''+@Manufacturer+''' AND CategoryId = '''+ @CategoryID+ print @FromQuery1 When i execute this query i am getting the conversion error. PLease find the error below. ---------------------- Conversion failed when converting the varchar value ' FROM [dbo].[iView_Data_TCG_Aggregated_Product] WHERE PeriodName = '4 Weeks' AND Manufacturer = 'Test1' AND CategoryId = '' to data type int. ---------------------------------------------- I am not getting categoryid value when i execute. It is giving error. Please suggest me wht to do. Thanks Chandrakanth
-
Hi this is Chandra. i have one issue with Sql Stored procedure. Please find the query below. declare @Measure varchar(50) declare @Time varchar(50) declare @Manufacturer varchar(50) declare @CategoryID int SET @Measure ='Value' SET @Time ='4 Weeks&' SET @Manufacturer ='Jti Gallaher Ltd' SET @CategoryID = 377; Declare @SELECTtQuery1 varchar(1000) Declare @FromQuery1 varchar(1000) Declare @GROUPBYQuery1 varchar(1000) Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000) SET @FromQuery1 = 'FROM [dbo].[iView_Data_TCG_Aggregated_Product] WHERE PeriodName = '''+@Time+''' AND Manufacturer = '''+@Manufacturer+''' AND CategoryId = '''+ @CategoryID+ print @FromQuery1 When i execute this query i am getting the conversion error. PLease find the error below. ---------------------- Conversion failed when converting the varchar value ' FROM [dbo].[iView_Data_TCG_Aggregated_Product] WHERE PeriodName = '4 Weeks' AND Manufacturer = 'Test1' AND CategoryId = '' to data type int. ---------------------------------------------- I am not getting categoryid value when i execute. It is giving error. Please suggest me wht to do. Thanks Chandrakanth
A few things: - Since you're concatenating things together, @CategoryID will have to be converted to a string. Otherwise, SQL Server is trying to perform addition and convert the first variables to an int. Try changing the SET to this:
SET @FromQuery1 = 'FROM [dbo].[iView_Data_TCG_Aggregated_Product]
WHERE PeriodName = '''+@Time+'''
AND Manufacturer = '''+@Manufacturer+''' AND CategoryId = '''+ cast(@CategoryID as varchar(10))- There was a dangling plus sign I removed from what you posted just to get it to execute - If this is going to be a valid query, your'e missing the SELECT clause with the columns to select. Scott
-
A few things: - Since you're concatenating things together, @CategoryID will have to be converted to a string. Otherwise, SQL Server is trying to perform addition and convert the first variables to an int. Try changing the SET to this:
SET @FromQuery1 = 'FROM [dbo].[iView_Data_TCG_Aggregated_Product]
WHERE PeriodName = '''+@Time+'''
AND Manufacturer = '''+@Manufacturer+''' AND CategoryId = '''+ cast(@CategoryID as varchar(10))- There was a dangling plus sign I removed from what you posted just to get it to execute - If this is going to be a valid query, your'e missing the SELECT clause with the columns to select. Scott
Hi Scott,Thanks and lot. I got it. It is working fine from my end . Thanks Chandra