Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    5

    Unanswered: How to send "order by "parameters to a SP

    Hi!

    I want to know if it is possible to send parameters to an "order by .." command in a Stored procedure.

    What I am doing is something like the following:


    Create procedure misp (@order1 int, @order2 int, @order3 int) AS
    begin
    select * from table1
    order by @order1, @order2, @order3
    end


    But I get message code 1008.

    I have also tried:
    CREATE PROCEDURE misp( @order1 int) AS
    begin
    declare @sqlstr varchar
    set @sqlstr ='
    select * from table1'
    if @order1 = 1 set @sqlstr=@sqlstr +' order by 1'
    execute (@sqlstr)
    end

    But when I run the command "execute misp 1" from Query Analyzer no recordset is returned but "The command(s) completed successfully." message.

    Any idea?
    Thanks in advanced.

  2. #2
    Join Date
    May 2002
    Posts
    10
    Hi

    Just a simple fix:

    replace

    declare @sqlstr varchar

    with

    declare @sqlstr varchar(1000)

    That should work.

    Lionel

  3. #3
    Join Date
    May 2002
    Posts
    5
    Thank you. It worked fine!

  4. #4
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    Talking Yes you can do it

    You will have to create a stored procedure and also you will have to run the query by using cmdexec option inside the store procedure.

    The query would look like:

    Create procedure ABC
    @order1 varchar(200),
    @order2 varchar(200)
    as

    cmdexex='select * from Table1' +'Order by' +@order1 +' '+@order2

  5. #5
    Join Date
    May 2002
    Posts
    5
    Thanks! This is really what I needed.

    At last my procedure is :

    CREATE PROCEDURE misp( @order1 varchar (100)) AS
    begin

    declare @sqlstr varchar (1000)
    set @sqlstr ='
    select * from pl_articulos order by ' + @order1
    execute (@sqlstr)

    end


    And I can call it in different ways:

    execute misp '1,2,3'

    or

    execute misp 'company, customers, sales'

    Thanks everybody.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •