The IIF logical function has been added to SQL Server 2012 to ease migration for Access users.

Currently, user-defined table types are for passing data...

You need a separate 'type' column to tell FTS what sort of document is in the binary data. See http://msdn.microsoft.com/en-us/library/ms142499.aspx and the TYPE COLUMN clause of the CREATE FULLTEXT...

Please take a look at the differences between this code and your original - there are several learning points in there...

CREATE TABLE dbo.Customer

(

Code ...

CREATE TABLE dbo.Customer

(

Code integer NOT NULL PRIMARY KEY,

LastName ...

Some new features, for fun (though the execution plan is quite cool too):

ALTER PROCEDURE dbo.IDStatusExists3

@ID integer,

@status integer

AS

BEGIN

SET NOCOUNT...

Very generous of you to stop at two 😉

(Sorry Lowell).

You will find it easier to avoid errors like this if you (a) choose more meaningful table alias names; (b) always include schema names; (c) always include alias names...and so...

Alternatively:

CREATE PROCEDURE dbo.IDStatusExists2

@ID integer,

@status integer,

@Result bit OUTPUT

AS

BEGIN

SET NOCOUNT ON;

...

The idea behind the requirement worries me - what are you looking to do with the result? Anyway, this is one reasonably efficient way to do what you ask:

CREATE...

Thanks Dev, that is exactly the link Bhuvnesh needs to read.

I had a client that used this for tempdb and the full-text catalog, worked well, and...

bathawes 16438 (1/19/2012)

---

It seems that both Target Server Memory (KB) and Total Server Memory (KB) eventually end up being equal to whatever I set as the minimum amount of memory...

One of the SQL collations should do what you need. This one works for your sample data:

DECLARE @Apples TABLE

(

sequence integer NOT NULL,

...

An alternative, that I find easier to read (and write!):

DECLARE @data TABLE

(

CustomerID integer IDENTITY(1,1) PRIMARY KEY,

LastName ...