StorProc: Adding a record to a query 3

[TheConeHead]

I have a sp that is simply doing a simple select. Is there a way to add a record that is not in the table. For example, a simple query, say, returns the following records:

1
2
3
4
5

How can I get:

0
1
2
3
4
5

returned when 0 is not in the table?

 

[kaht]

select 0 union select numbers from yourTable

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[TheConeHead]

So if I was adding a string would I just do somthing like:

select "Here I am!" union select field from yourTable

and what if I was returning more than 1 field?

 

[DotNetGnat]

select value1, value2
UNION ALL
Select field1, field from yourtable

-DNG

 

[kaht]

I don't see that it would be a problem, give it a try.

The one important thing to remember with unions is to make sure that all select statements are returning the same datatypes in the same order. For example, you can't union a string and a datetime:

select 'blah' union select getdate()

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[SQLDenis]

So if I was adding a string would I just do somthing like:

select "Here I am!" union select field from yourTable

No because your query returns integers

you will have to do

select "Here I am!"  as field 
union all
select convert(varchar(30),field )
from yourTable

and what if I was returning more than 1 field?
then add spaces or nulls to your top query

select "Here I am!"  as field,null as field2,'' as field3 
union all
select convert(varchar(30),field ),field2,field3
from yourTable

you have to alias your values or your recordset won't have column names


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[kaht]

Additionally, if you use DNG's query above, remember that union [red]all[/red] will return duplicates. So from your first example, if 0 existed in the database then you would have 2 0s in your result set.

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]