Executing ASCAN( ) on SQL Server

[cochise54]

After successfully establishing a SQL connection, rather than using the following:

Select * from sqltable where fieldname NOT IN ('v1',...,'v8')

I would prefer something like the following:

Select * from sqltable where ASCAN(gArray, fieldname) = 0

any suggestions?

 

[mrdenny]

That's not a valid syntax. SQL Server doesn't support arrays.

If the values you want to include are stored in a table you can use that table as the NOT IN clause.

SELECT *
FROM sqltable
WHERE fieldname NOT IN (SELECT AnotherField FROM AnotherTable)

If the values aren't in another table then you either need to put them in a table or statically put then in the NOT IN list.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[cochise54]

Denny,

Thanks for your quick reply. I guess SQL Server ain't all that if it can't support arrays. Nevertheless, I have successfully used the SQL statement with the NOT IN list on a 3GB sql table. Other than placing values in another table, what about creating a method in a class?

 

[mrdenny]

SQL Doesn't support methods or classes. Unless of course you load a .NET assembly into the SQL Server and create a SQL Function to call the .NET assembly.

As far as I know most database platforms don't support array's within the database.

The closest thing that SQL would have to an array would be a table variable which is a table which exists mostly in memory which you can load up with values and is destroyed when your batch has completed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLSister]

Perhaps this article will help you out

http://www.sommarskog.se/arrays-in-sql-2005.html

"NOTHING is more important in a database than integrity." ESquared