CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Archish
Archish

Reputation: 870

Getting Conversion failed error in stored procedure

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.

Upvotes: 0

Views: 302

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try to replace double quotas with double single quotas

Replace this line 

'(SELECT N"," + sp.specialization

With 

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

Upvotes: 0

Related Questions