Solve the listing and creation of a tree with jsTree using SQL Server, problems with the #

Question

I am using JSTREE to list some system modules, the problem is that according to the jsTree documentation I have to use #, in my query to list the tree, but when performing the following query, it returns this error:

Conversion failed when converting the varchar value '#' to data type int. My mysql query is as follows:

select mod_id as id,
    (case when (mod_principal is null or mod_principal = 0)then '#' else mod_principal end) as parent, mod_modulo as text,
    mod_vista from crediguate.dbo.modulo m order by m.orden

en mysql funciona perfectamente el # en la consulta segun exige jstree, alguna forma de listar mis modulos usando # en sqlserver

Answer 1

CASE expression returns only a single value. So all values in THEN or ELSE clause must have same data type. It seems mod_principal is INT so SQL tries to cast '#' to INT.

Try this:

select mod_id as id,
    (case 
         when (mod_principal is null or mod_principal = 0) then '#'
         else Cast(mod_principal  as varchar(1000))
     end) as parent, mod_modulo as text, mod_vista 
    from crediguate.dbo.modulo m 
    order by m.orden

OR

select mod_id as id,
    (case 
         when (mod_principal is null or mod_principal = 0) then 0
         else mod_principal 
     end) as parent, mod_modulo as text, mod_vista 
from crediguate.dbo.modulo m 
order by m.orden