Database Discussion Boards

Database

View All Threads

First Prev Next

Conversion failed when converting the varchar value in SQLSERVER 2008

ChandrakanthGaddam22-May-12 5:47

ChandrakanthGaddam

22-May-12 5:47

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

Re: Conversion failed when converting the varchar value in SQLSERVER 2008

scottgp22-May-12 6:02

scottgp

22-May-12 6:02

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:

SQL

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