Query error occurs when filter SQL query using Uniqueidentifier variable type with a static value

Question

User1909155429 posted

Conversion failed when converting from a character string to uniqueidentifier. 

I have a Gridview filtered from a Dropdownlist
i use the following 'Where' clause  in my SQL query string. If i run the page i get the above error. If i exclude either from the string it works.

where @RoleId=-1 OR (vw_aspnet_UsersInRoles.RoleId=@RoleID)

The parameters are obtained from dropdownlist of values bound to another sqldatasource  including a static appended item.

<asp:ListItem Text="-- ALL --" Value="-1" ></asp:ListItem>

 

 

Answer 1

User1208776063 posted

-1 is not a valid Uniqueidentifier. You would need a case statement to check if ALL is selected.

WHERE vw_aspnet_UsersInRoles.RoleID =(CASE WHEN @RoleID='-1' THEN vw_aspnet_UsersInRoles.RoleId ELSE @RoleID END)

This is sort of a query like 1=1 and potentially gets you all roles if you do not append other valid conditions.

Answer 2

User1909155429 posted

That is a brilliant answer. It works !

So if  @RoleID =-1 selected, then things being equal (vw_aspnet_UsersInRoles.RoleID =vw_aspnet_UsersInRoles.RoleId) returns all records.

What is actually  comparing, values  ?

Thanks

Answer 3

User1208776063 posted

What is actually  comparing, values  ?

Yes, when RoleID  is -1, where clause will compare value to itself.

Answer 4

User1909155429 posted

is there an alternative approach besides ?

 

Answer 5

User1208776063 posted

You can write an IF Else statement in a stored procedure

IF @RoleID=-1
BEGIN
   //Get all roles
END
ELSE
BEGIN
  SELECT * FROM Table WHERE RoleID = @RoleID
END

That is much more cleaner way to handle filtering in your scenario.