SQL Stored Procedure Error?

Question

I am attempting to write a stored procedure that will order columns in an SQL table in the same database that the Stored Procedure is located in. I am able to execute the stored procedure and pass the correct values to it from visual C#. The stored procedure runs and completes without errors; however, the table is not ordered by the order is should be in. Any suggestions or help? Hardest part was getting the code to work, now I have to get it to function.

Answer 1

Your stored procedure won't actually change the order of data in the database. That will be in the order it was written. That is unless you write a stored procedure that will order the data in a temp table, then truncate the original and write the data back in order.



Can you show us your stored procedure code?



I get the feeling you're doing an ordered select, in which case the results can be passed back to your C# application and displayed.



The best way to make sure that this is fast is to rely on database indexes. However too many indexes on a table can impact performance, so index only the columns you need ordered.



Fee free to email me with questions

Answer 2

My first question would be - why do you want to reorder the actual columns in the table?



I could be wrong about this, but if I remember correctly, the way the columns are displayed has nothing to do with their actual physical location or properties - it is merely a function to make it easier for us humans to read them, an abstraction. I don't really think there is a way to change the order they display in, unless you are using an editor like TOAD or something similar. But even in that, if I remember correctly, the columns will always display in the order they were created in.



I am thinking you could create a new table, with the columns listed in the order you want, then select the data from the old table into it.



Good luck : )

Answer 3

1. Make sure the delete actually worked. 2. Try a workaround by trapping @@error in an integer local variable and then returning that variable. 3. Try printing @@error and see what you have. 4. Might be due to some scope termination issues. Check if the variable that you are assigning the return value of the SP is an integer variable. 5. Return the @@error value in an output variable to the SP and see if that helps. In short, I do not know the exact reason why this is happening without the entire context. But these are some food for thought.