Here is another variation with excellent performance.

select #tableA.conName,

isnull((select sum(total) from #tableB where conid = #tableA.conid),0),

isnull((select sum(total) from #tableC where conid = #tableA.conid),0)

from #tableA

order by 1

;

Jeff: Thanks for the test harness and profiler results. Just out of curiosity, I tested a CONVERT/DATEDIFF variation in your test harness and it appears to be a little faster...

I tried an example containing a charindex column in SQL2005, and while it did create the index on the view, the following warning was shown when it was created.

Warning: The...

Here is another method that works for basic needs where quoted words and special strings like those found in names are not involved. (i.e. Jim O'Brian, III DDS).

declare @string varchar(250)

select...

halifaxdal:

Here is another option for producing the order you want without adding a column to the table or the output.

create table #temp

(num int,

cd varchar(5),

nm varchar(10))

set nocount on

insert #temp...

halifaxdal:

Here is another option for producing the order you want without adding a column to the table or the output.

create table #temp

(num int,

cd varchar(5),

nm varchar(10))

set nocount on

insert #temp...

Steve: Here is a variation on your dynamic SQL example:

CREATE PROCEDURE [dbo].[usp_UpdateTable]

@PK_TableID int,

@FK_ID int = NULL,

@somecol nvarchar(15) = NULL

...

AS

SET NOCOUNT ON

if @FK_ID is not null or

...

I tried the following on SQL 7.0, without a space before the 'from', so this does go back aways.

set nocount on

create table #cdarea (

cd int not null,

descr varchar(30) not null)

insert...

Here is one more solution variation.

declare @mm char(2)

declare @gdate datetime

select @gdate = '03/31/2009'

select @mm = right(100 + month(@gdate),2)

select @mm

Here is some code for review. The three select statements are there to show the progression. Obviously, the final one combines everything.

declare @TBL table

(amt int)

insert into @TBL

select...

I was able to reproduce this in a SQL2000 environment. It can be fixed by changing the order of the columns selected in the first line of the select statement...

Here is another solution that builds on the previous suggestions. This one handles a few more variations of the text data and the number to be added.

create table #tester

(seq...

Here is suggestion that uses a case statement in the order by clause.

create table #t(c nvarchar(10))

set nocount on

insert into #t values('001')

insert into #t values('12')

insert into #t values('112')

insert into #t values('123')

insert...

For the specified serial number which includes the string representing year 2007 and week 44, both solutions return a date that falls on a Monday. The first solutioin returns '11/05/07'...

Here is an example that will compensate for the day of the week for Jan 1 of the year in question to make sure the result date is a Monday.

DECLARE...