Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    21

    Unanswered: Varibles in a Stored Proc

    OK guys, here is what I am trying to do,

    CREATE PROCEDURE [dbo].[FM_COMPILE]

    @match1 smalldatetime = NULL,
    @match2 smalldatetime = NULL,
    @sort char (20) = NULL
    @sort2 char (20) = NULL

    AS


    Select * from TABLE1
    groupby colum1
    order by @sort @sort2

    @sort is supposed to be the colum name to sort by
    @sort2 is supposed to be DESC or ASC.

    But I can't get it to compile,

    Any ideas ??

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Varibles in a Stored Proc

    Try this,

    CREATE PROCEDURE [dbo].[FM_COMPILE]

    @match1 smalldatetime = NULL,
    @match2 smalldatetime = NULL,
    @sort char (20) = NULL
    @sort2 char (20) = NULL

    AS
    declare @sqlstmt varchar(1000)
    set @sqlstmt =
    'Select * from TABLE1 groupby colum1 order by ' + @sort + ', ' + @sort2
    exec(@sqlstmt)

  3. #3
    Join Date
    Nov 2003
    Posts
    21

    hmm

    That would work, but,

    the select statement actually uses a @table to pull the data.

    hence, calling an exec in a stored proc puts the @table out of scope.

    any other ideas ?

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    Code:
    declare @by varchar(50), @order varchar(5)
    
    set @by = 'au_lname'
    set @order = 'desc'
    
    if @order = 'asc'
       select * from authors 
          order by case @by 
             when 'au_lname' then au_lname
             when 'au_fname' then au_fname
             when 'phone' then phone
             when 'address' then address
             when 'city' then city
             else au_id end
    else if @order = 'desc'
       select * from authors 
          order by case @by 
             when 'au_lname' then au_lname
             when 'au_fname' then au_fname
             when 'phone' then phone
             when 'address' then address
             when 'city' then city
             else au_id end desc
    else
       select * from authors

  5. #5
    Join Date
    Nov 2003
    Posts
    21

    part of it works

    The ASC / DESC part works,

    but I get this anytime the case statement runs

    Error converting data type nvarchar to numeric.

    any other ideas ?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You made you're phone number numeric...didn't you...

    come on fess up...

    use CONVERT in the CASE...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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