"stored query" query?

[Shilohcity]

Hello

I was just wondering if anybody knew if it was possible to redefine grouping, ordering etc for stored procedures i.e.

Dim sql

sql = "storedquery"
sql = "sql ORDER BY date"
sql = "sql GROUP BY colour"

objRS.CreateObject(ADODB.Recordset)

objRS.Open sql, objConn

etc....

I have the stored queries working fine but need to display it in various orders based upon user input. I guess I could just make a number of stored procedures but thought this way would be kinda neat.

well as always all help is appreciated.

Justin.X-)

"Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[Antzz]

R u talking of SQL Server stored procedures or Access queries?

 

[Shilohcity]

sorry forgot to specify...it is in relation to Access queries but come to think of it feedback on SQL stored procedures would also be welcome.

Justin.X-)

"Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[GDX]

you have to order by in the stored procedure but you could pass it a field like

storedquery field1
and have an ORDER BY @field1 in the sproc(Stored PROCedure)
Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[Antzz]

Well, I am not very good at Access but the method mentioned above will not work with SQL Server since here you will be storing a field name in a variable. If I am right, you will probably get an error when you try to compile the SP. On the other hand, you can have a single SP with different queries embedded, which you can invoke based on input by the users.

This way, in SQL Server, you will not have multiple SPs but you WILL have multiple SQL Statements. If you want to do the same thing from your front end like a VB, it will be much easier since you can actually construct your SQL statement dynamically thus eliminating multiple SQL statements. But the catch is here, it will be less efficient since the SQL statement will have to be compiled before execution.

So it depends on what you want to sacrifice - maintenance or speed.

Let me know if it is useful.

PS: It think you will also face an error with Access if you try to use the Order by statement with a variable.