InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 09:18:32 GMT20http://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspxI did thanks, hope you had a good christmas and new year as well.Wed, 02 Jan 2013 04:51:04 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspx[quote][b]Jason-299789 (1/2/2013)[/b][hr][quote][b]anthony.green (1/2/2013)[/b][hr][quote][b]Jason-299789 (1/2/2013)[/b][hr]so is @p_error an integer? In which case you will need to change the [code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]to[code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]and that should work.[/quote]Should beCast(@p_error, varchar(20))Convert the data type is first, cast the data type is last.[/quote]Doh!!!!, thanks anthony, its my fist day back after after 14 days holiday, and the brains still warming its cache.[/quote]Same here, hope you had a good holiday.Wed, 02 Jan 2013 04:43:27 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspx[quote][b]anthony.green (1/2/2013)[/b][hr][quote][b]Jason-299789 (1/2/2013)[/b][hr]so is @p_error an integer? In which case you will need to change the [code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]to[code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]and that should work.[/quote]Should beCast(@p_error, varchar(20))Convert the data type is first, cast the data type is last.[/quote]Doh!!!!, thanks anthony, its my fist day back after after 14 days holiday, and the brains still warming its cache.Wed, 02 Jan 2013 04:41:34 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspxSET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + CONVERT(VARCHAR,@p_Error)+ ''thanks :-) it workedWed, 02 Jan 2013 03:24:36 GMTsmriti.subramanianhttp://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspx[quote][b]Jason-299789 (1/2/2013)[/b][hr]so is @p_error an integer? In which case you will need to change the [code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]to[code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]and that should work.[/quote]Should beCast(@p_error, varchar(20))Convert the data type is first, cast the data type is last.Wed, 02 Jan 2013 03:16:42 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspxso is @p_error an integer? In which case you will need to change the [code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]to[code="sql"]IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ '' INSERT INTO @RecipientDetails EXEC (@SQL) [/code]and that should work.Wed, 02 Jan 2013 03:07:41 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspxEither change the error code variable to a varchar or convert the variable when appending it to the string using cast or convert.SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + CONVERT(VARCHAR,@p_Error)+ ''As SQL is trying to convert the whole string to a number which is cannot do, so you need to make the number a string.Wed, 02 Jan 2013 03:04:17 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1401721-391-1.aspxHi, I have the following stored proc written using dynamic query. i am appending an integer variable to the dynaic query. but i am getting an issue only with the integer variable.DECLARE @SQL VARCHAR(4000) SET @SQL= 'SELECT [RECORD_TYPE], [RECORD_VERSION_NUMBER], [1LINK_BOOKING_REF], [RESERVATION_NUMBER], [CUSTOMER_ACCOUNT_NUMBER], [CUSTOMER_NAME], [CUSTOMER_ORDER_NUMBER], [PURCHASE_ORDER_NUMBER], [COST_CENTRE], [HIRE_CONTACT], [HIRE_CONTACT_TELEPHONE_NUMBER], [LEAD_SOURCE_DESCRIPTION], [LEAD_SOURCE_CODE], [RELIEF_VEHICLE_REGISTRATION], [HIRE_REASON_DESCRIPTION], [HIRE_REASON_CODE], [HIRE_REASON_NOTES], [DRIVER_TITLE], [DRIVER_FORENAME_OR_INITIAL], [DRIVER_SURNAME], [DRIVER_MOBILE_NUMBER], [DRIVER_FAX_NUMBER], [DRIVER_TELEPHONE], [DRIVER_EMAIL], [DRIVER_REFERENCE], [ADDITIONAL_DRIVER_1_TITLE], [ADDITIONAL_DRIVER_1_FORENAME_OR_INITIAL], [ADDITIONAL_DRIVER_1_SURNAME], [ADDITIONAL_DRIVER_2_TITLE], [ADDITIONAL_DRIVER_2_FORENAME_OR_INITIAL], [ADDITIONAL_DRIVER_2_SURNAME], [ADDITIONAL_DRIVER_3_TITLE], [ADDITIONAL_DRIVER_3_FORENAME_OR_INITIAL], [ADDITIONAL_DRIVER_3_SURNAME], [ADDITIONAL_DRIVER_4_TITLE], [ADDITIONAL_DRIVER_4_FORENAME_OR_INITIAL], [ADDITIONAL_DRIVER_4_SURNAME], [ADDITIONAL_DRIVER_5_TITLE], [ADDITIONAL_DRIVER_5_FORENAME_OR_INITIAL], [ADDITIONAL_DRIVER_5_SURNAME], [REQUESTED_MODEL_GROUP], [START_DATE_OR_TIME_OF_HIRE], [START_BRANCH_CODE], [START_BRANCH_NAME], [DELIVERY_ADDRESS_LINE_1], [DELIVERY_ADDRESS_LINE_2], [DELIVERY_ADDRESS_LINE_3], [DELIVERY_ADDRESS_LINE_4], [DELIVERY_ADDRESS_LINE_5], [DELIVERY_ADDRESS_POSTCODE], [REQUEST_OPTION_NOTES], [ADDITIONAL_EMAIL_OR_FAX_NOTES], [END_DATE_OR_TIME_OF_HIRE], [END_BRANCH_CODE], [END_BRANCH_NAME], [COLLECTION_ADDRESS_LINE_1], [COLLECTION_ADDRESS_LINE_2], [COLLECTION_ADDRESS_LINE_3], [COLLECTION_ADDRESS_LINE_4], [COLLECTION_ADDRESS_LINE_5], [COLLECTION_ADDRESS_POSTCODE], [COLLECTION_KEY_LOCATION], [INSURANCE_TYPE], [FLIGHT_NUMBER], [MEET_AND_GREET], [CSI_EMAIL_ADDRESS], [CHILD_SEATS], [SATELLITE_NAVIGATION], [HAND_CONTROLS], [DAYS_ABROAD], [LICENCE_AVAILABLE], [YEARS_LICENCE_HELD], [OFFENCE_CODES], [FUEL_DEPOSIT_TYPE], [EQUIFAX_CHECK], [DRIVER_ADDRESS_LINE_1], [DRIVER_ADDRESS_LINE_2], [DRIVER_ADDRESS_LINE_3], [DRIVER_ADDRESS_LINE_4], [DRIVER_ADDRESS_LINE_5], [HIRE_SUPPLIER], [TARIFF_ACCOUNT_REFERENCE], [SUPPLIERS_ACCOUNT_REFERENCE], [BOOKING_STATUS], [ORIGINAL_1LINK_BOOKING_REF], [AIRPORT_PICKUP], [BOOKING_TYPE_INDICATOR], [EMPLOYEE_NUMBER], [STORE_NUMBER], [CANCELLATION_REFERENCE], [ADDITIONAL_BOOKING_REFERENCE], [TARIFF_TYPE_CODE], [DATE_DAYS_ABROAD_FROM], [DATE_DAYS_ABROAD_TO], [DAYS_ABROAD_COUNTRIES], [BOOKING_TIME_TYPE], [REQUEST_TYPE], [QUOTE_TYPE], [QUOTE_NUMBER], [RESERVED1], [RESERVED2], [RESERVED3], [RESERVED4], [RESERVED5], [RESERVED6], [DRIVER_PICKUP_SERVICE_CHOSEN], [DRIVER_DROP_OFF_SERVICE_CHOSEN], [DATE_CREATED], [STATUS_ID], [ERROR_CODE] FROM [dbo].[WIP_RESERVATION_STATUS] RS WHERE(1=1)' -- If 1link booking reference number is not null IF @p_1LINK_BOOKING_REF IS NOT NULL SET @SQL = @SQL + 'AND RS.1LINK_BOOKING_REF = ''' +@p_1Link_Booking_Ref + '''' -- If customer account number is not null IF @p_Customer_Account_Number IS NOT NULL SET @SQL = @SQL + ' AND RS.CUSTOMER_ACCOUNT_NUMBER = ''' + @p_Customer_Account_Number+ '''' -- If customer name is not null IF @p_Customer_Name IS NOT NULL SET @SQL =@SQL + ' AND RS.CUSTOMER_NAME LIKE ''' + @p_Customer_Name + '%''' -- If Error is not null IF @p_Error IS NOT NULL SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ '' INSERT INTO @RecipientDetails EXEC (@SQL) In the table i have declated error as int. When i execute i get an error msgError Conversion failed when converting the varchar value 'SELECT [RECORD_TYPE], [RECORD_VERSION_NUMBER], [1LINK_BOOKING_REF], [RESERVATION_NUMBER], [CUSTOMER_ACCOUNT_NUMBER], [CUSTOMER_NAME],Please help me.Wed, 02 Jan 2013 02:59:38 GMTsmriti.subramanian