Re: Sorting data in a stored procedure

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mark (mark_at_edwards-family-server.co.uk)
Date: 12/28/04 

Date: Tue, 28 Dec 2004 13:41:41 +0000

David wrote:

> Hello.
>
> I have some stored procedure which selects some data, but I want to pass
> some field(s) to this procedure via parameters by which selected data will
> be sorted (ORDER BY).
> How can I do this?
>
> ===================================================
> CREATE PROCEDURE GetTable /* Some parameters */
> AS
> SELECT * FROM MyTable
> /* ORDER BY ?????????? */
> GO
> ===================================================
>
> Thank you.
>
>
David,

Alternatively, you could do something like:-

Alter Proc usp_Something
@SortBy nVarChar(255) = Null

As

Declare @Cmd nVarChar(4000)

Select @Cmd = 'Select * from tb_Source'

If @SortBy is not null
  Select @cmd = @cmd + ' order by ' + @SortBy

Exec sp_ExecuteSQL @Cmd
Go

Then call it via

exec usp_Something

or

exec usp_Something 'f_ID Desc'

--Mark.

Relevant Pages

  • Re: SELECT TOP with variable not working
    ... command but looks to be pretty powerful and has additional capability over ... "David G." wrote: ... > You can certainly execute the SQL with just the EXEC command inthe ... > stored procedure, but I prefer using sp_executesql, especially since I ...
    (microsoft.public.sqlserver.server)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... EXEC sp_fulltext_catalog 'adsfull', 'stop' ... > 1) check noise words inside stored procedure ... > can be solved by changing the language specific file with noise words ... ...
    (microsoft.public.sqlserver.fulltext)
  • source text file as input parameter from a stored procedure
    ... I am using an active X script in combination with a global ... transform task that loads data from a .txt file to a table. ... I can also tell that the stored procedure is correctly passing in the ... EXEC sp_OAGeterrorinfo @object, @hr ...
    (microsoft.public.sqlserver.dts)
  • Re: Repeating 3 Datapump actions between the same datasource/destinati
    ... How to loop through a global variable Rowset ... > exec p_PullDataForDates1 20050101,20050131 ... > task that configure the SourceSQLStatement for the 2 pumps (to fill in the ... > blanks for the stored procedure parameter). ...
    (microsoft.public.sqlserver.dts)
  • Re: Stored Procedure Fails Only When Called By Agent
    ... Stored Procedure 1: ... EXEC sp_addlinkedserver '10.1.2.10', 'SQL Server' ... LOCALMACHINE\myaccount/pass3, the same account that owns the job. ...
    (microsoft.public.sqlserver.replication)