January 2, 2013 at 2:59 am
Hi, 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 msg
Error 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.
January 2, 2013 at 3:04 am
Either 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.
January 2, 2013 at 3:07 am
so is @p_error an integer?
In which case you will need to change the
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
to
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
and that should work.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 2, 2013 at 3:16 am
Jason-299789 (1/2/2013)
so is @p_error an integer?In which case you will need to change the
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
to
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
and that should work.
Should be
Cast(@p_error, varchar(20))
Convert the data type is first, cast the data type is last.
January 2, 2013 at 3:24 am
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + CONVERT(VARCHAR,@p_Error)+ ''
thanks 🙂 it worked
January 2, 2013 at 4:41 am
anthony.green (1/2/2013)
Jason-299789 (1/2/2013)
so is @p_error an integer?In which case you will need to change the
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
to
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
and that should work.
Should be
Cast(@p_error, varchar(20))
Convert the data type is first, cast the data type is last.
Doh!!!!, thanks anthony, its my fist day back after after 14 days holiday, and the brains still warming its cache.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 2, 2013 at 4:43 am
Jason-299789 (1/2/2013)
anthony.green (1/2/2013)
Jason-299789 (1/2/2013)
so is @p_error an integer?In which case you will need to change the
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + @p_Error+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
to
IF @p_Error IS NOT NULL
SET @SQL = @SQL + 'AND RS.ERROR_CODE = ' + Cast(varchar(20),@p_error)+ ''
INSERT INTO @RecipientDetails
EXEC (@SQL)
and that should work.
Should be
Cast(@p_error, varchar(20))
Convert the data type is first, cast the data type is last.
Doh!!!!, thanks anthony, its my fist day back after after 14 days holiday, and the brains still warming its cache.
Same here, hope you had a good holiday.
January 2, 2013 at 4:51 am
I did thanks, hope you had a good christmas and new year as well.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply