[dba-SQLServer] Iif in SQL Server

You want a report right? ie a resultset... yes a sproc John.. you
don't have to CREATE a sproc, but doing so will store the
optimizations for the sproc in the server and possibly even caching
parts of the report (since the data hasn't changed).

to create a sproc


CREATE PROCEDURE stp_MyNewSprocNamingConvention 

AS

SELECT FIELD1, Field2, Field3, Case...
          FROM Table1
WHERE ClauseoptionsHere




On Wed, 22 Sep 2004 08:39:44 -0400, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> Are we talking about code in an Sproc here?  I am not talking about
> AccessVB.  I am looking for something to work with 165 million records and
> return results in a few minutes.
> 
> In the end I built a table with a PK field, a ZIPCode field and a Src field.
> I made the PK field a PK so that it has a unique index.  I then wrote two
> queries, one that pulls data from one field plus the PK plus a 1 as Src and
> appends it to my new table.  The other query pulls data from the other field
> plus the PK plus a 2 as the Src and appends to the table.  Run the first
> query.  All records that have anything in the ZIP code are put in the table.
> Run the second query.  All the records that have something in the second ZIP
> field but aren't already in the table get put in the table.
> 
> Now I have a table with ONE field, with data from one or the other field,
> with a column which tells me which source field it came from, with a PK to
> join back up to the main table.
> 
> Of course my second query is failing to append at all because the PK already
> exists in the new table.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
> Tapia
> Sent: Wednesday, September 22, 2004 1:30 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Iif in SQL Server
> 
> JOHN!
> 
> I know you're busy these days but it works like this
> 
> SELECT CASE WHEN FIELD = 'value' THEN FieldWhenTrue
>                         WHEN Fieldothercase = 'othervalue THEN
> FieldWhenOtherValue
>                         ELSE FieldwhenELSE
>                END AS AliasName,
>              Next Field
> 
> >From TableName
> 
> On Wed, 22 Sep 2004 11:45:36 +1000, Stuart McLachlan
> <stuart at lexacorp.com.pg> wrote:
> > On 21 Sep 2004 at 21:38, John W. Colby wrote:
> >
> > > Sorry, my Outlook is totally screwed up
> >
> > That's tautology. <grin>



-- 
-Francisco
http://ft316db.VOTEorNOT.org