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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MS SQL Table Views

Author  Topic 

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-27 : 15:58:01
I have a table I need to sort by 2 columns. The way I out put it in my program, one column is a checkbox so it is stored in an int field with 1's and 0's (default value of 0 and default to unchecked), and the other is a datetime field (also with a default value, of a large date and time). If the checkbox field is checked I need all those to be sorted to the bottom of the records and then everything else to the sorted by the datetime field in ascending order. So the default dates that haven't been changed would be a the bottom and the dates more recent at the top (I would prefer the checked records to also be sorted by the date time field but that is less important).

When I was asking about how to do this through the programming language (C++ in QT) I was told a much more efficient way would be to do it with a table View. I am using MS SQL Server 2005 if that matters. I don't really know very much about SQL programming so when I tried to make the view by going into Databases->theDatabse->Views->NewView->and added in the table. I tried to tell it to order by the one column then the other, then I tried switching which column was 1st and also tried using sort by but kept getting errors.

Can anyone help me with how I am supposed to actually make the view to do this please?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-27 : 16:51:19
That information is incorrect. You cannot order the data in a view. (You could, in older versions, you could fake it, but not reliable, so don't do it). Instead, what you should do is add an order by clause at the very end of the select query that you are using to retrieve the data. The order by clause should be like this:

ORDER BY
YourCheckBoxColumn ASC,
YourDatetimeColumn ASC
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-27 : 16:51:33
Unfortunately, you cannot do a parameterized view in SQL Server. Some possible options are using a stored procedure or a table-valued function to return the data. With each of these options you can do a dynamic type sort. We'd need to see what fields in the table and what parameters you are using to get you an appropriate ORDER BY clause.

Jim



Everyday I learn something that somebody else already knew
Go to Top of Page

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-27 : 17:10:33
James K I right clicked my table and selected "Script table as" and then selected "SELECT To" and then "New Query Editor Window" which I assume is what you meant by a SELECT Query. But as I said I don't have much experience which SQL so let me know if I am wrong. I added in the ORDER BY clause like you had shown and when I pressed execute it seemed to work correctly. My next question is what do you mean by "the select query that you are using to retrieve the data" because the language I am writing in as a set table function for my TableModel but when I set that to the query it didn't work. This could be cause of where it saved or maybe I need to use a different function after set table to get that query I'm not sure.

jimf my fields are called [Below the line] (the checkable one) and [Service Date and Time] (the datetime field) and I'm not sure what you mean by parameters.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-27 : 17:16:16
In the tool you are using, do you have an option to use a Stored Procedure or a Query instead of a TableModel as the data source?
Go to Top of Page

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-27 : 17:25:43
Yeah I think I can use a Query Model. Which I am trying now
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-27 : 17:30:05
If you have the option of using a Stored Procedure that would be even better, for a number of reasons. If you have that option, create a stored procedure (from a query window in SQL Server Management Studio), something like this:
CREATE PROCEDURE dbo.GetYourData
AS
SELECT
col1,
col2,
col3,
YourCheckBoxColumn,
YourDatetimeColumn
FROM
YourTable
ORDER BY
YourCheckBoxColumn ASC,
YourDatetimeColumn ASC
GO
Then, you can use the name GetYourData (in this example) as the stored procedure name to supply to your data source.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-27 : 18:20:33
If that's a your doing, a view will suffice
CREATE VIEW dbo.GetYourData
AS
SELECT TOP 100PERCENT
col1,
col2,
col3,
YourCheckBoxColumn,
YourDatetimeColumn
FROM
YourTable
ORDER BY
YourCheckBoxColumn ASC,
YourDatetimeColumn ASC

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-27 : 18:28:44
I am weary about it, Jim. This MSDN page advises against relying on the order by clause in views http://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-27 : 18:37:26
I just learned something! But then he doesn't need a view or a sproc even. I think we need to hear more from the OP and exactly what it is he trying to do. My original impression was that he is trying to change the sort order based on a checkbox value.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-27 : 18:57:24
I recall reading somewhere that Microsoft tries very hard and goes to great lengths to keep existing functionality to avoid breaking existing programs, but that this was one of those cases where the behavior was changed from SQL 2000 to SQL 2005. In SQL 2000 you could rely on order by in views, but SQL 2005 and later you could not.
Go to Top of Page

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-28 : 09:12:11
James to create a stored procedure do I do that just by clicking "New Query" and make it in there? Also is GetYourData the name of the procedure or the .dbo that already exists? It looks like the name of the procedure but I just want to make sure.

Jim as far as learning more about what exactly I am trying to do. I basically need all the records sorted by the datetime field and then also if the checkable field is checked those records need to be moved to the bottom. I would prefer the records that are checked to also be sorted by the datetime field but if not I can deal with that. Also I don't care which order this is done in as long as it outputs correctly. Does that give you enough information or do you need to know more? If so let me know what information would be helpful to you
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 09:17:42
Allen, yes - you would click the new query window, which should bring up a new blank query window. Paste the code to that window. Be sure to add the 2 lines shown below at the very top:
USE YourDatabaseNameHere
GO
YourDatabaseNameHere is whatever is the name of your database.

GetYourData is a random name that I picked. You can change it to whatever would make sense for you. When you run the script, it is creating an object - a stored procedure - in your database. After you run the script, you can see that it has created the object by navigating down to the Stored Procedures section in the object explorer window in SSMS. Once you create it, it is there forever for you to use. You can ALTER it if you need to make changes, DROP it if you want to get rid of it for some reason etc.
Go to Top of Page

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-28 : 10:35:08
Ok I created it, nothing showed up like when I created the select query, but it said "Command(s) completed successfully." so I assume it worked correctly. You said that I then use the name such as GetYourData "as the stored procedure name to supply to your data source" how do I do that? and then I guess I will have to call this in my program as a query?

And thank you for all the help y'all have given me so far. I would of never been able to figure any of this stuff out in time without y'all's help.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 11:14:10
When you create the stored procedure, you are only creating the object; so no result set would be returned. But now if you were to run the query from a SSMS query window ( exec dbo.GetYourData ), that should return the results correctly, and correctly ordered.

In your client program, you would need to choose the option to run a stored procedure (as opposed to table view or a adhoc query) to execute the stored procedure. Are you using ADO.Net or one of the .Net facilities to connect to the database?
Go to Top of Page

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-28 : 11:52:53
Umm I'm not exactly sure. I'm using C++ in the Framework Qt connecting to MS SQL with an ODBC driver. That's about as much as I know off the top of my head. Do you know how I can find out which I am using? I asked on a Qt forum but am waiting for a response.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 12:34:21
I am not familiar with QT, Allen. I would expect though, that there would be some options to select what kind of query you are running - typically table-direct, query, or stored procedure are the options that you may see. Look at the place where you changed it from table view to query to see if there are other options. Or you may want to ask QT support forums or consult their documentation.
Go to Top of Page

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-28 : 14:06:19
Yeah it looks like I'll have to just call it in similar to a query like query.exec("{CALL StoredProcedure}")

Will the stored procedure allow for updating information to the table and adding and deleting rows and all that?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 14:10:51
The stored procedure that we were testing is just for selecting some data. We can write similar stored procedures for insert/update/delete etc, or for doing anything else for that matter.
Go to Top of Page

AllenMcAfee
Starting Member

9 Posts

Posted - 2013-01-28 : 14:29:19
Awesome, Yeah I would need it to allow inserting new rows, updating information, and deleting rows. And I think that's all
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 15:57:03
You will find several examples and tutorials on the web - here is one for inserting: http://www.mssqltips.com/sqlservertutorial/2519/insert-stored-procedure-in-sql-server/
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -