Order by in Access procedure - SQL Server Forums

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

Other Forums

MS Access

Order by in Access procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-11 : 08:52:25

Jonathan Miller writes "How can I get a procedure in access to order by a field that is passed in as a paramter? IE, the following does NOT work:
CREATE PROCEDURE test
(arg_test text)
select price,vendor from price_tbl
order by arg_test

Thank you!
Jonathan"

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-02-12 : 01:16:49

Create a string out of your parameter and then execute it..

SqlStr = "Select price, vendor, from price_tbl order by " & arg_test

If you are creating a procedure in the module part of access then all you have to do after you create the string is:

currentdb.execute SQLStr

If from SQLserver, you have 2 options:

exec SQLStr

quote:

EXECUTE (T-SQL)
Executes a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch.

Syntax
Execute a stored procedure:

[[EXEC[UTE]]
{
[@return_status =]
{procedure_name [;number] | @procedure_name_var
}
[[@parameter =] {value | @variable [OUTPUT] | [DEFAULT]]
[,...n]
[WITH RECOMPILE]

Execute a character string:

EXEC[UTE] ({@string_variable | [N]'tsql_string'} [+...n])

sp_executesql SQLStr

quote:

sp_executesql (T-SQL)
Executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

Syntax
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]

Arguments
[@stmt =] stmt
Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions (such as concatenating two strings with the + operator) are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N’sp_who’ is legal, but the character constant ‘sp_who’ is not. The size of the string is limited only by available database server memory.
stmt can contain parameters having the same form as a variable name, for example:

N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'
...

In any case search the forum on Dynamic SQL..

==================================================
Do not argue with IDIOTS. They will take you down to their level and BEAT you with experience.
Master Fisherman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources