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 |
|
notmyrealname
98 Posts |
 Posted - 2009-03-13 : 10:28:35
|
| Hi.I hear everyone say not to use Select * and to instead explicitly list the columns you are querying.I was hoping someone could help me out with a situation that seems impossible without Select *.I am developing an app that displays production info in a grid. I have given the user the option to add custom columns to the grid that will display calculated results based on the database info. These custom columns are actually added to the database in a secondary table. So i have Table1 storing all of the hardcoded production info and Table2 storing the custom column info. The grid needs to display everything so i have created a stored procedure that joins the two table using Select *. This way i am sure that the grid will display all of the custom columns that the user has added.I have two questions.1. Has anyone done anything similar and used a different approach that might be better?2. I have a report designer that does not allow me to use stored procedures. Only tables and views. So i have set up a view that does pretty much the same thing and joins the two tables together. The only problem is that when i save the view my Table2.* becomes Table2.Col1, Table2.Col2, etc... Now the results will only return the custom columns that were there when the view was saved. Does anyone know how i can use Select * in a view?Thanks,Joel |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-03-13 : 10:34:49
|
| 1. You could do this by creating the SQL within the procedure dynamically, making a call to the Information schema to get the columns, but this is more work on your part.2. What reporting software are you using that will not allow Stored Procs?!? |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-13 : 11:37:14
|
| The Select * advice is to encourage the selection of only columns which are needed because indexes can be used more efficiently and I/O is reduced. If you need all columns then that is what you should select.If not all columns are actually displayed but you aren't sure which ones will be needed, pass the required columns as parameters into a stored procedure and build the select statement dynamically. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-13 : 11:45:35
|
| RE: View - are you building the View outside of SQL Server? You can ALTER VIEW in SQL Server and it should hold your "Select *" if needed. |
|
|
|
notmyrealname
98 Posts |
Posted - 2009-03-13 : 11:47:20
|
| Thanks Guys.The reporting software (DevExpress) allows access to sp's in design mode but not does support sp's in its End User Report Designer. Only tables and views. So any clue how to make sure a view that uses select * will always show all columns even if the columns are modified after the view was created? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-13 : 12:09:57
|
| Are you using some type of wizard to build the view. Right click on the view in SQL Server and choose ALTER. This will give the script which you can modify e.g. replace column names with *. Run this and the * will apply.http://www.sqlinfo.net/sqlserver/sql_server_VIEWS_the_basics.php |
|
|
|
notmyrealname
98 Posts |
Posted - 2009-03-14 : 09:23:48
|
| Hi darkdusky.I do not have SQL Server and am not permitted to use it. I only have Visual Studio to create and modify my SQL databases. Can this be done through Visual Studio?Thanks for your help,Joel |
|
|
|
notmyrealname
98 Posts |
Posted - 2009-03-14 : 09:38:25
|
| Hi DarkDusky.Me again.I tunneled into the database schema through Visual Studio and found the view properties and Select statement. I'm guessing that this is the statement that i would see if i were to look at it in SQL Server. The Select statement looks correct. It includes the Select * statement. However if i execute the view after adding additional columns to the table that i am Selecting * from the new columns are not returned. All i know is that if i create or modify a view in Visual Studio using the Select * statement is that the next time i open the view all of the columns are explicitly selected instead.Thanks again,Joel |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-19 : 06:01:12
|
| Sorry for delay I was off for St Pat's.I don't use Visual Studio to create / alter database objects so I'm not sure. There are ways you can cheat by passing a SQL statement to database using other methods but I think you should make the case to be allowed to use SQL Server. There are free versions you can use to connect to server (e.g. SQL Server 2005 / 2008 Express) if cost is an issue.If your company is worried about giving too much access - you can be given a restricted SQL account (which you probably already use through VS). The account is the limiting factor not the software, so you won't be able to do anything which you can't do already.Also SQL Server can be used to optimise your queries i.e. better performance. |
|
|
|
|
|
|
Select *