T-SQL question: is this possible?

T-SQL question: is this possible?

Post by Moshe Plotki » Sat, 12 Jan 2002 23:22:30



B"H

Steve,
The case statment is actualy made to handle more than one posibility. The
statment you gave Aaron is realy a variation of the way case was meant to
work, which would be cleaner and better for this situation:


    WHEN    1    Then
            a
    WHEN    2    Then
            b
END


> Aaron,

>   You don't need dynamic SQL here.  You can do the following:

> ORDER BY






> along with the value you want to order by.

> Steve Kass
> Drew University


> > hello
> > I have a table T with columns a, b, c, d, e

> > I wrote a simple stored procedure that does a SELECT on T and takes a
search
> > text parameter ...

> > CREATE PROCEDURE foo

> > AS

> > GO

> > it worked great.

> > Next I wanted to add another parameter to this to control an ORDER BY
clause
> > in the query.. ie given a number from 1 thru 5,  I want the result set
to be
> > sorted by one of the corresponding columns (a thru e)

> > I suppose I could have a series of five IF statements that invoke five
> > seperate copies of the SELECT staetement , each with a different ORDER
BY
> > clause(but in paractice my actual query is huge and managing this amount
of
> > duplicated text would be a nightmare), so what I'm really looking for is
a
> > clever piece of syntax that I can use to parameterize this ORDER BY
clause
> > from within the stored procedure...

> > any ideas?

> > thanks
> > A

 
 
 

T-SQL question: is this possible?

Post by Steve Kas » Sun, 13 Jan 2002 02:41:13


Moshe,

  Just be sure if you use the single case expression
that a, b, c, d and e are of the same type.  If they aren't,
it will fail in some cases:


  select top 3 * from pubs..sales
  order by

                 when 'ord_num' then ord_num end
go

exec Moshe 'qty'
go

exec Moshe 'ord_num'
go

drop procedure Moshe

Steve


> B"H

> Steve,
> The case statment is actualy made to handle more than one posibility. The
> statment you gave Aaron is realy a variation of the way case was meant to
> work, which would be cleaner and better for this situation:


>     WHEN    1    Then
>             a
>     WHEN    2    Then
>             b
> END



> > Aaron,

> >   You don't need dynamic SQL here.  You can do the following:

> > ORDER BY






> > along with the value you want to order by.

> > Steve Kass
> > Drew University


> > > hello
> > > I have a table T with columns a, b, c, d, e

> > > I wrote a simple stored procedure that does a SELECT on T and takes a
> search
> > > text parameter ...

> > > CREATE PROCEDURE foo

> > > AS

> > > GO

> > > it worked great.

> > > Next I wanted to add another parameter to this to control an ORDER BY
> clause
> > > in the query.. ie given a number from 1 thru 5,  I want the result set
> to be
> > > sorted by one of the corresponding columns (a thru e)

> > > I suppose I could have a series of five IF statements that invoke five
> > > seperate copies of the SELECT staetement , each with a different ORDER
> BY
> > > clause(but in paractice my actual query is huge and managing this amount
> of
> > > duplicated text would be a nightmare), so what I'm really looking for is
> a
> > > clever piece of syntax that I can use to parameterize this ORDER BY
> clause
> > > from within the stored procedure...

> > > any ideas?

> > > thanks
> > > A


 
 
 

1. TSQL question - what am I doing wrong?

Good Afternoon everyone,

I stumbled across this issue today, and am perplexed.  Haven't find anything
in Books Online about it yet.

I'm running SQL2000, and executing the following code thru query analyzer,
and it runs fine:

----------------------
USE Northwind
GO

SELECT CompanyName
INTO #tmp
FROM Customers

ALTER TABLE #tmp
ADD TestColumn int NULL

SELECT *
FROM #tmp
--WHERE TestColumn IS NULL

DROP TABLE #tmp
---------------------

With the comment, the last SELECT statement returns both the CompanyName and
TestColumn fields.  However, when I uncomment the WHERE clause, I get an
"Invalid Column Name" error.

Anyone know why this happens?  Thanks!

Jon Richt

2. What can cause ODBC error 01S03???

3. Differences between SyBase TSQL, and SQL Server 2000 TSQL

4. Prototyping Software

5. Is Propercase possible in TSQL??

6. How can I generate SQL script to create indexes not using GUI

7. Possible to set backup trough TSQL?

8. VERY STRANGE ERROR MSG - Please Help

9. Possible to connect to ODBC within TSQL?

10. Is this possible, or am I pushing it?

11. nettiquette question...where to post TSQL questions (MSSQL)?

12. Is this Possible Using DTS?Could you say the details.I am new

13. SQL*Plus question (Where am I)