locked
How to return the result set of multiple select statements as one result set? RRS feed

  • Question

  • Hi All,

    I have multiple select statements in my stored procedure that I want to return as one result set 

    for instance 

    select id from tableA

    union 

    select name from table b 

    but union will not work because the result sets datatypes are not identical so how to go about this ?

    Thanks



    Tuesday, September 23, 2014 10:17 PM

Answers

  • You have to CAST or CONVERT (or implicitly convert) the columns to the same datatype.  You must find a datatype that both columns can be converted to without error.  In your example I'm guessing id is an int and name is a varchar or nvarchar.  Since you didn't convert the datatypes, SQL will use its data precedence rules and attempt to convert name to an int.  If any row contains a row that has a value in name that cannot be converted to an int, you will get an error.  The solution is to force SQL to convert the int to varchar.  So you want something like

    select cast(id as varchar(12)) from tableA
    union 
    select name from tableb 

    If the datatypes are something other that int or varchar, you must find a compatable datatype and then convert one (or both) of the columns to that datatype.

    Tom

    • Proposed as answer by pituachMVP Tuesday, September 23, 2014 11:21 PM
    • Marked as answer by Kalman Toth Saturday, October 4, 2014 5:01 PM
    Tuesday, September 23, 2014 11:19 PM

All replies

  • You have to CAST or CONVERT (or implicitly convert) the columns to the same datatype.  You must find a datatype that both columns can be converted to without error.  In your example I'm guessing id is an int and name is a varchar or nvarchar.  Since you didn't convert the datatypes, SQL will use its data precedence rules and attempt to convert name to an int.  If any row contains a row that has a value in name that cannot be converted to an int, you will get an error.  The solution is to force SQL to convert the int to varchar.  So you want something like

    select cast(id as varchar(12)) from tableA
    union 
    select name from tableb 

    If the datatypes are something other that int or varchar, you must find a compatable datatype and then convert one (or both) of the columns to that datatype.

    Tom

    • Proposed as answer by pituachMVP Tuesday, September 23, 2014 11:21 PM
    • Marked as answer by Kalman Toth Saturday, October 4, 2014 5:01 PM
    Tuesday, September 23, 2014 11:19 PM
  • the tables I am working on have several columns and they are from different data types. what I wanted to do is to add two more columns to the resultset

    Wednesday, September 24, 2014 4:28 AM