Getting Conversion failed error in stored procedure

Question

I don't know anything about procedure, but I have to create one so I tried one. But now I am getting error - please help.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Get_List_Docter_Detail_A]
    @special varchar(MAX),
    @city varchar(MAX),
    @offset int,
    @pageSet int,
    @area varchar(MAX)
AS
Begin
    declare @sql nvarchar(MAX);

    set @sql = 'SELECT'+
        '(SELECT Stuff('+
            '(SELECT N"," + sp.specialization FROM DSpecialization_Master dsp'+
            'LEFT JOIN Specialization_Master sp on sp.id = dsp.specialization'+
            'WHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization='+@special+'))'+
            'FOR XML PATH(""),TYPE)'+
            '.value("text()[1]","nvarchar(max)"),1,1,N"")) as drspec,'+
    'pm.id as profileid,'+
    'pm.loginid as loginid,'+
    'dam.clinicname,'+
    'dam.area,'+
    'dam.address,'+
    'dam.pincode,'+
    'dam.id as addressid,'+
    'dam.feecharge as feecharge,'+
    'pm.fname,'+
    'pm.lname,'+
    'pm.email,'+
    'pm.mobile,'+
    'pm.phone,'+
    'pm.gender,'+
    'pm.dob,'+
    'pm.totexp,'+
    'pm.imagepath,'+
    'pm.languages,'+
    'pm.statement,'+
    'pm.createdby,'+
    'convert(nvarchar, pm.createdon, 103) as createddate,'+
    'convert(nvarchar, pm.createdon, 108) as createdtime,'+
    'pm.hsbit,'+
    'overall_count = COUNT(*) OVER(),'+
    '(SELECT Stuff('+
        '(SELECT N"," + education FROM DEducation_Master WHERE profileid = pm.id'+
        'FOR XML PATH(""),TYPE)'+
    '.value("text()[1]","nvarchar(max)"),1,1,N"")) as dredu'+
    'FROM Profile_Master pm '+
        'LEFT JOIN DAddress_Master dam on dam.profileid = pm.id '+
    'WHERE '+
        'dam.city='+@city;
        if @area!=''
            set @sql+=' and dam.area in('+@area+') and';
        set @sql+=' pm.id IN (SELECT profileid FROM DSpecialization_Master WHERE specialization = (select id from Specialization_master where specialization='+@special+')) ORDER BY dam.city OFFSET '+@offset+' ROWS FETCH NEXT '+@pageSet+' ROWS ONLY';

EXECUTE sp_executesql @sql
END

I am getting this error.

Msg 245, Level 16, State 1, Procedure Get_List_Docter_Detail_A, Line 61
Conversion failed when converting the nvarchar value 'SELECT(SELECT Stuff((SELECT N"," + sp.specialization FROM DSpecialization_Master dspLEFT JOIN Specialization_Master sp on sp.id = dsp.specializationWHERE dsp.profileid = pm.id and (dsp.specialization = (select id from Specialization_master where specialization=Dentist))FOR XML PATH(""),TYPE).value("text()[1]","nvarchar(max)"),1,1,N"")) as drspec,pm.id as profileid,pm.loginid as loginid,dam.clinicname,dam.area,dam.address,dam.pincode,dam.id as addressid,dam.feecharge as feecharge,pm.fname,pm.lname,pm.email,pm.mobile,pm.phone,pm.gender,pm.dob,pm.totexp,pm.imagepath,pm.languages,pm.statement,pm.createdby,convert(nvarchar, pm.createdon, 103) as createddate,convert(nvarchar, pm.createdon, 108) as createdtime,pm.hsbit,overall_count = COUNT(*) OVER(),(SELECT Stuff((SELECT N"," + education FROM DEducation_Master WHERE profileid = pm.idFOR XML PATH(""),TYPE).value("text()[1]","nvarchar(max)"),1,1,N"")) as dreduFROM Profile_Master pm LEFT JOIN DAddress_Master dam on dam.profileid = pm.id WHERE dam.city=surat and dam.area in(Adajan) and' to data type int.

What am I doing wrong? Please correct me.

Your dynamic sql misses spaces between string pieces. For example alias 'dsp' has no space after and this will be a dspLEFT JOIN string which is a mistake. Add spaces. MSSQL allows multy-line strings by the way. — Ivan Starostin
City variable has no quotes around. I'd suggest to turn back to static sql and write your conditions as they have to be. and (@area = '' or dam.area = @area). Put a print @sql before executing it if you still want to do this via dynamic sql. — Ivan Starostin
... OFFSET '+@offset+ ... You've got a massive string on one side. And an integer on the other side. And you're trying to + them together. int has higher precedence than any string type so it's trying to convert the string to an integer. You should manually cast the integer, but there are probably other issues that @Ivan has already pointed out also. — Damien_The_Unbeliever

Jaydip Jadhav Jaydip Jadhav · Accepted Answer · 2016-03-11T07:41:25

Try to replace double quotas with double single quotas

Replace this line

'(SELECT N"," + sp.specialization

With

'(SELECT N'','' + sp.specialization

Getting Conversion failed error in stored procedure

1 Answers