locked
Using a Variable In a SQL Query RRS feed

  • Question

  • Hello,

    I am trying to figure out how to use a variable in SQL so I don't have to type out the same  status multiple times. 
    I am trying to return records that would not have a 5 or 6 in the Status field in table service_req. I will be using variables for a number of things so I am just trying to understand the syntax. Each time I run the query I get:

    Conversion failed when converting the varchar value '"5" or "6"' to data type int.

    The Status field in this table is of data type int. I know i'm missing something here, but not sure what. Any suggestions would be appreciated.

    Use Database1 
    DECLARE @FilteredOutStatus int
    SET @FilteredOutStatus = '"5" or "6"'
    Select '"Technical Team"' as title
    select id,problem_type,problem_sub_type,title,description,status,responsibility,assigned_group,priority,notes,resolution,solution,insert_time,update_time,close_time,update_user,version,submit_user,request_user,cust_text1,cust_text2
    from service_req 
    Where status not like @FilteredOutStatus 
    order by responsibility
    Friday, August 31, 2018 9:59 PM

Answers

  • Hi Rich Ellis-MC,

    Your issue is related to SET @FilteredOutStatus = '"5" or "6"'. Since you have declared the variable with int data type, you should know that the value "5" or "6" is a string data (char or varchar). So you can't give @FilteredOutStatus this value.

    To achieve your requirement, I think you can just separate the @FilteredOutStatus and the value into two parts like:

    Use Database1 
    DECLARE @FilteredOutStatus1 int,@FilteredOutStatus2 int
    SELECT @FilteredOutStatus1 = 5,@FilteredOutStatus2 = 6
    Select '"Technical Team"' as title
    select id,problem_type,problem_sub_type,title,description,status,responsibility,assigned_group,priority,notes,resolution,solution,insert_time,update_time,close_time,update_user,version,submit_user,request_user,cust_text1,cust_text2
    from service_req 
    Where status not in (@FilteredOutStatus1,@FilteredOutStatus2) 
    order by responsibility

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Rich Ellis-MC Tuesday, September 4, 2018 2:29 PM
    Monday, September 3, 2018 3:27 AM

All replies

  • looks like this to me

    Use Database1 
    DECLARE @FilteredOutStatus varchar(100)
    SET @FilteredOutStatus = '5,6'
    Select '"Technical Team"' as title
    select id,problem_type,problem_sub_type,title,description,status,responsibility,assigned_group,priority,notes,resolution,solution,insert_time,update_time,close_time,update_user,version,submit_user,request_user,cust_text1,cust_text2
    from service_req 
    Where status not in (select Value FROM String_Split(@FilteredOutSTatus,','))
    order by responsibility

    assuming SQL 2016

    if earlier versions, then you've to use a UDF like below

    https://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

    Then use it in query like

    Use Database1 
    DECLARE @FilteredOutStatus varchar(100)
    SET @FilteredOutStatus = '5,6'
    Select '"Technical Team"' as title
    select id,problem_type,problem_sub_type,title,description,status,responsibility,assigned_group,priority,notes,resolution,solution,insert_time,update_time,close_time,update_user,version,submit_user,request_user,cust_text1,cust_text2
    from service_req 
    Where status not in (select Val FROM dbo.ParseValues(@FilteredOutSTatus,','))
    order by responsibility
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, August 31, 2018 10:49 PM
    Answerer
  • You get the error because you are comparing an integer column with a string, and way back in SQL 6.5 you would have been told that you can mix these types. Unfortunately, there is now implicit conversion, so instead you get a less clear error message when it breaks down.

    Anyway, you can do this:

    DECLARE @FilteredOutStatus TABLE (status int NOT NULL PRIMARY KEY)
    INSERT @FilteredOutStatus VALUES(5), (6)

    Select '"Technical Team"' as title
    select id, problem_type, problem_sub_type, title,description, status,
              responsibility, assigned_group, priority, notes, resolution,  solution,
              insert_time, update_time,close_time, update_user,  version, submit_user,
              request_user, cust_text1, cust_text2
    from    service_req
    Where   status not in (SELECT f.status FROM @FilteredOutStatus f)


    • Edited by Erland SommarskogMVP Saturday, September 1, 2018 10:25 AM Formatting
    • Proposed as answer by Xi Jin Monday, September 3, 2018 3:12 AM
    Saturday, September 1, 2018 8:32 AM
  • Hi,

    How about:

    Where cast(status as varchar) not like @FilteredOutStatus 

    Saturday, September 1, 2018 8:34 AM
  • Hi Rich Ellis-MC,

    Your issue is related to SET @FilteredOutStatus = '"5" or "6"'. Since you have declared the variable with int data type, you should know that the value "5" or "6" is a string data (char or varchar). So you can't give @FilteredOutStatus this value.

    To achieve your requirement, I think you can just separate the @FilteredOutStatus and the value into two parts like:

    Use Database1 
    DECLARE @FilteredOutStatus1 int,@FilteredOutStatus2 int
    SELECT @FilteredOutStatus1 = 5,@FilteredOutStatus2 = 6
    Select '"Technical Team"' as title
    select id,problem_type,problem_sub_type,title,description,status,responsibility,assigned_group,priority,notes,resolution,solution,insert_time,update_time,close_time,update_user,version,submit_user,request_user,cust_text1,cust_text2
    from service_req 
    Where status not in (@FilteredOutStatus1,@FilteredOutStatus2) 
    order by responsibility

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Rich Ellis-MC Tuesday, September 4, 2018 2:29 PM
    Monday, September 3, 2018 3:27 AM