Case Statement!

Hi all,
I am trying to return a true / false value via case statement.
The boolean value returned is determined whether a column contains a null
value.
Can someone help with the following query as it is causing an error...
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN IsNull THEN 0
WHEN Not IsNull Then 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Cheers,
Adam
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IS NULL THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||Try (untested)
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IsNull THEN 0
WHEN a.ColumnID Is not Null Then 1
END AS colAlias
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID
Is Null THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||Try this.
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID Is Null THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl:

> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
I suspect that you're misusing the ISNULL function. AFAICT, the syntax
for ISNULL is: ISNULL ( check_expression , replacement_value )
Try something like:
CASE ISNULL(a.ColumnID, 0)
WHEN 0 THEN 0
ELSE 1
END AS aID
or
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END AS aID
HTH,
Geoff Lane
Cornwall, UK|||Hi Adam,
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
HTH, Jens Suessmeyer.sql

carriage return and line feeds

I have a value of 7.5 when I ask for the length (after I have trimmed it)
the value 5 is returned. Visually this is a 3. Someone suggested there may
be a carriage return and line feed (Char(10) and Char(13)) in the column.
How can I remove these if they exist?
Thanks a bunch,What is the data type? Did you try DATALENGTH? Why aren't you using a
numeric data type to store numeric data?
To remove a CHAR(13), for example, you can use REPLACE():
SELECT REPLACE(column_name, CHAR(13), '')
FROM table_name;
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
>I have a value of 7.5 when I ask for the length (after I have trimmed it)
> the value 5 is returned. Visually this is a 3. Someone suggested there
> may
> be a carriage return and line feed (Char(10) and Char(13)) in the column.
> How can I remove these if they exist?
> Thanks a bunch,|||"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
>I have a value of 7.5 when I ask for the length (after I have trimmed it)
> the value 5 is returned. Visually this is a 3. Someone suggested there
> may
> be a carriage return and line feed (Char(10) and Char(13)) in the column.
> How can I remove these if they exist?
> Thanks a bunch,
What datatype is the column?
declare @.dec decimal(5,3)
set @.dec = 7.5
select len(ltrim(rtrim(@.dec)))
...also returns 5.|||I thoguht this was an interesting behavior, so I did a little more
testing...
declare @.dec decimal(5,3)
declare @.var varchar(20)
declare @.char char(10)
set @.dec = 7.5
set @.var = CAST(@.dec AS varchar(20))
set @.char = CAST(@.dec AS char(10))
select len(ltrim(rtrim(@.dec))) as dec
,len(ltrim(rtrim(@.char))) as char
,len(ltrim(rtrim(@.var))) as var
select @.dec as dec
, @.char as char
, @.var as var
RESULTS
dec char var
5 5 5
dec char var
7.500 7.500 7.500
If the number is defined with 3 decimal places, these 3 places are stored as
zeros. Some applications just happen to display the values without trailing
zeros.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23qk9naLOGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:D6800B7A-C7C1-4473-9517-618E915B2198@.microsoft.com...
it)
column.
> What datatype is the column?
> declare @.dec decimal(5,3)
> set @.dec = 7.5
> select len(ltrim(rtrim(@.dec)))
> ...also returns 5.
>|||More fun...
DECLARE @.dec DECIMAL(5,3)
SET @.dec = 7.5
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))|||Thats cheating.
You reduced the precision.
But here is one more, which I find more puzzling, although I recall other
languages doing strange rounding like this as well.
I can't recall WHY the rounding works like this...
DECLARE @.dec DECIMAL(5,3)
SET @.dec = 7.555
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
, LTRIM(STR(@.dec, 6, 1))
SET @.dec = 7.55
SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
, LTRIM(STR(@.dec, 6, 1))
Results:
3 7.6
3 7.5
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyuhSwLOGHA.2320@.TK2MSFTNGP11.phx.gbl...
> More fun...
> DECLARE @.dec DECIMAL(5,3)
> SET @.dec = 7.5
> SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
>|||> Thats cheating.
> You reduced the precision.
Well, the extra characters are leading, not trailing. Compare LTRIM() and
RTRIM()...
A|||Aaron and Co.,
Thanks for your replies. Unfortunately, the datatype can't be stored as a
number because results that come in are from many different sources
(Laboratories, Radiology etc) and have different formats. The specific
result I am looking at will always return values like 10.3 or 7.5.
The problem is that for some reason, after I strip the extraneous characters
away(which I have done with ease), I try to cast these values as decimal and
it doesn't work.
Here is code and raw data...it's got me stumped...
There is something still contained in the string that is messing up the
cast. (See raw data below code)
select top 7
patientid,
decodedvalue,
convert(varchar,replace(replace(replace(
decodedvalue,'
',''),'>',''),'%','')) value,
-- cast(convert(varchar,replace(replace(r
eplace(decodedvalue,'
',''),'>',''),'%','')) as float) value,
len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%','')) str_length
from
#diabetes_results
where
decodedvalue like '%.%'
order by
patientid
58 6.8 % 6.8 3
58 7.6 % 7.6 3
58 6.7 % 6.7 3
58 7.1 % 7.1 3
58 6.2 % 6.2 3
168 7.5 % 7.5 3
168 7.5 7.5 5
Note the length of '5' in the final record though it is obvious the length
should be 3. I trimmed the column of spaces but they remain.
This led me to look at the string lengths of these characters and I
discovered that a few values had what appeared to be spaces in them even
after they were trimmed.
Thanks to all who give their .02.
"Aaron Bertrand [SQL Server MVP]" wrote:

> More fun...
> DECLARE @.dec DECIMAL(5,3)
> SET @.dec = 7.5
> SELECT DATALENGTH(LTRIM(STR(@.dec, 6, 1)))
>
>|||In your sample output there is an extra space before and after the value in
question.
I know your code is removing spaces, but I'm not sure what accounts for the
values in the output.
Can you post your table DDL? What datatype is decodedvalue?
I suspect you have tabs rather than carriage returns, but try this code...
convert(varchar,replace(replace(replace(
replace(replace(@.dec,'
',''),'> ',''),'%',''),char(10),''),char(13),'')c
har(9),''))
Char(10) and Char(13) are a carriage return and line feed (I can never
recall which is which).
Char(9) is a tab character.
Rather than removing the characters you dont want, change the code to keep
only the characters you do want. I'll see if I can track down a sample of
this, but someone else will probably beat me to it.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:1125946A-8B58-4424-9F4D-C1FBE98CDA6B@.microsoft.com...
> Aaron and Co.,
> Thanks for your replies. Unfortunately, the datatype can't be stored as a
> number because results that come in are from many different sources
> (Laboratories, Radiology etc) and have different formats. The specific
> result I am looking at will always return values like 10.3 or 7.5.
> The problem is that for some reason, after I strip the extraneous
characters
> away(which I have done with ease), I try to cast these values as decimal
and
> it doesn't work.
> Here is code and raw data...it's got me stumped...
> There is something still contained in the string that is messing up the
> cast. (See raw data below code)
> select top 7
> patientid,
> decodedvalue,
> convert(varchar,replace(replace(replace(
decodedvalue,'
> ',''),'>',''),'%','')) value,
> -- cast(convert(varchar,replace(replace(rep
lace(decodedvalue,'
> ',''),'>',''),'%','')) as float) value,
> len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%',''))
str_length
> from
> #diabetes_results
> where
> decodedvalue like '%.%'
> order by
> patientid
> 58 6.8 % 6.8 3
> 58 7.6 % 7.6 3
> 58 6.7 % 6.7 3
> 58 7.1 % 7.1 3
> 58 6.2 % 6.2 3
> 168 7.5 % 7.5 3
> 168 7.5 7.5 5
> Note the length of '5' in the final record though it is obvious the length
> should be 3. I trimmed the column of spaces but they remain.
>
> This led me to look at the string lengths of these characters and I
> discovered that a few values had what appeared to be spaces in them even
> after they were trimmed.
> Thanks to all who give their .02.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Char(10) and Char(13) are a carriage return and line feed (I can never
> recall which is which).
I have two ways of remembering it:
(a) in VB, the constant is named vbCrLf (carriage return, line feed)
(b) they come in inverse order
So, CHAR(13) is carriage return, CHAR(10) is line feed. In some
environments, this is the order it has to be in; in some environments, the
opposite is true. And to make things even more fun, in some environments,
you can have them in either order.
At least that's how I loosely remember it.
A

capturing dynamically returned integer

Hello there.
I have a dynamic SQL query (I had to use it - I swear!) that returns a
single integer value. However, I'm having trouble capturing the value in a
local variable. Any ideas what I should do with my syntax (to get it working
I mean...)?
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select max(Column1) from table1'
--execute sp_executesql @.testSQL --this returns an integer value
--the following gives errors
select @.IntegerVar = (execute sp_executesql @.testSQL)
--the following assigns 0 to @.IntegerVar rather than the query result
execute @.IntegerVar = sp_executesql @.testSQLhttp://www.aspfaq.com/2492
"len" <len@.discussions.microsoft.com> wrote in message
news:B254F6C4-E94F-48B6-BB9C-7C03066D6998@.microsoft.com...
> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it
> working
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Try,
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = N'select @.IntegerVar = max(Column1) from table1'
execute sp_executesql @.testSQL, N'@.IntegerVar int output', @.IntegerVar outpu
t
select @.IntegerVar
go
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/defaul...B;EN-US;q262499
AMB
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Hi Len
Try something like:
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select @.MaxVar = max(Column1) from table1'
execute sp_executesql @.SQLQuery, N'@.MaxVar int OUTPUT', @.IntegerVar OUTPUT
SELECT @.IntegerVar
John
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL

Capture Returned Value From Exec(@Build) into another variable

I am building a SQL statement that returns a number.
when I execute the Built SQL statment EXEC(@.Build). What I need to do
now is take that number that comes back and store it in another
variable so I can do some conditional logic. Any ideas? See SQL below.

Something like @.Count=Exec(@.Build) which I know doesnt work.

Thanks,
Phil

DECLARE @.PullDate varchar(12)

SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
+'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
+'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''

PRINT(@.PullDate)

DECLARE @.COUNTER BIGINT

DECLARE @.SELECT VARCHAR(500)
DECLARE @.SELECT2 VARCHAR(1000)
DECLARE @.BUILD VARCHAR(5000)

SET @.SELECT='

SELECT COUNTER FROM
OPENQUERY(PROD,'

SET @.SELECT2='''
SELECT
COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
FROM
COLLECTOR_RESULTS,
WHERE
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCOUNT.END_DATE ) =
to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
)
GROUP BY
WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'

SET @.BUILD=@.SELECT+@.SELECT2
PRINT(@.BUILD)
EXEC(@.BUILD)

--THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
INTO @.COUNTER so I can do a conditional statement.)
if @.COUNTER>=1
begin
print('yes')
end<philipdm@.msn.com> wrote in message
news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
>I am building a SQL statement that returns a number.
> when I execute the Built SQL statment EXEC(@.Build). What I need to do
> now is take that number that comes back and store it in another
> variable so I can do some conditional logic. Any ideas? See SQL below.
> Something like @.Count=Exec(@.Build) which I know doesnt work.
> Thanks,
> Phil
>
>
> DECLARE @.PullDate varchar(12)
> SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> PRINT(@.PullDate)
> DECLARE @.COUNTER BIGINT
> DECLARE @.SELECT VARCHAR(500)
> DECLARE @.SELECT2 VARCHAR(1000)
> DECLARE @.BUILD VARCHAR(5000)
>
> SET @.SELECT='
> SELECT COUNTER FROM
> OPENQUERY(PROD,'
> SET @.SELECT2='''
> SELECT
> COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> FROM
> COLLECTOR_RESULTS,
> WHERE
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> )
> GROUP BY
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
>
> SET @.BUILD=@.SELECT+@.SELECT2
> PRINT(@.BUILD)
> EXEC(@.BUILD)
> --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD STORED
> INTO @.COUNTER so I can do a conditional statement.)
> if @.COUNTER>=1
> begin
> print('yes')
> end

Instead of EXEC(), you can use sp_executesql with an output parameter:

declare @.sql ntext, @.counter int
set @.sql = 'select @.counter = counter from openquery(...)'
exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
select @.counter

See here for an example:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Simon|||I am able to get this to return a value but I cant get this to work.
Any ideas?

IF @.Counter>1
Print('Yes')

Simon Hayes wrote:
> <philipdm@.msn.com> wrote in message
> news:1107271847.396749.170840@.z14g2000cwz.googlegr oups.com...
> >I am building a SQL statement that returns a number.
> > when I execute the Built SQL statment EXEC(@.Build). What I need to
do
> > now is take that number that comes back and store it in another
> > variable so I can do some conditional logic. Any ideas? See SQL
below.
> > Something like @.Count=Exec(@.Build) which I know doesnt work.
> > Thanks,
> > Phil
> > DECLARE @.PullDate varchar(12)
> > SET @.PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> > +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> > +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
> > PRINT(@.PullDate)
> > DECLARE @.COUNTER BIGINT
> > DECLARE @.SELECT VARCHAR(500)
> > DECLARE @.SELECT2 VARCHAR(1000)
> > DECLARE @.BUILD VARCHAR(5000)
> > SET @.SELECT='
> > SELECT COUNTER FROM
> > OPENQUERY(PROD,'
> > SET @.SELECT2='''
> > SELECT
> > COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> > FROM
> > COLLECTOR_RESULTS,
> > WHERE
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> > to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> > to_date(''+@.PullDate+'',''''mm/dd/yyyy'''')
> > )
> > GROUP BY
> > WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
> > SET @.BUILD=@.SELECT+@.SELECT2
> > PRINT(@.BUILD)
> > EXEC(@.BUILD)
> > --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @.BUILD
STORED
> > INTO @.COUNTER so I can do a conditional statement.)
> > if @.COUNTER>=1
> > begin
> > print('yes')
> > end
> Instead of EXEC(), you can use sp_executesql with an output
parameter:
> declare @.sql ntext, @.counter int
> set @.sql = 'select @.counter = counter from openquery(...)'
> exec sp_executesql @.sql, N'@.counter int', @.counter OUTPUT
> select @.counter
> See here for an example:
> http://www.sommarskog.se/dynamic_sql.html#sp_executesql
> Simon|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. All I need to do is set a
@.variable=@.Output Variable.
Thanks for your help Simon!
Phil|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!

Cant use the NTEXT datatype in SQLCLR scalar-valued functions

From the SQL Server documentation :

"The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, exceptrowversion,text,ntext,image,timestamp,table, orcursor"

This is a problem for me. Here's what I'm trying to do :

I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.

Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.

The sql server 2005 nvarchar(max) is the same as sql server 2000 nText

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

|||Cool. Thanks for the tip. I'll give this a shot and report back.