Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sirmilt
Starting Member
49 Posts |
 Posted - 2013-09-06 : 19:54:57
|
| I am Using an SQL Server 2008 R2 database with Visual Basic 2010. I use the following stored procedure; SELECT DISTINCT Category FROM tblBooks ORDER BY Category;This works fine except if its a new User with an empty or near empty table there will be nothing or very little for the User to select.The following was recommended:SELECT DISTINCT Category FROM tblBooks UNION SELECT 'Add In 1' AS Category, 'Add In 2' As Category ORDER BY Category ORDER BY Category;There will be about 5 or 6 items to be added this way.When testing in SSMS the code throws an error as shown below:All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.I'm relly not sure what they are telling me or if the sql I am trying to use is the right method in doing it.Any help would be really appreciatedMilt |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-09-07 : 10:37:46
|
| Not entirely sure because it looks ok except that you do have too many ORDER BY clauses. |
 |
|
|
sirmilt
Starting Member
49 Posts |
Posted - 2013-09-07 : 10:58:50
|
| Thanks for the response,The extra "Order By" was m error in writing the original post. only one is in the actual statement.Milt |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-09-09 : 15:31:17
|
| [code]SELECT DISTINCT Category FROM tblBooks UNION SELECT Category FROM (VALUES ('Add In 1'),('Add In 2')) As A(Category) ORDER BY Category ORDER BY Category;[/code]You need to split the select up to be severaldjj |
|
|
|
sirmilt
Starting Member
49 Posts |
Posted - 2013-09-10 : 09:22:35
|
| djjThanks for the tip, it worked perfectly. MiltMilt |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-09-10 : 10:48:57
|
| Glad I could help. Had to jump through a couple of hoops to find how to due this as I knew I had seen it but when I first tried got the wrong syntax. :-)djj |
|
|
|
|
|
|
Joining Data from a table to a list of other Items