VBA Syntax In SQL Server 2000

[Yesca]

Is there any way to have SQL Server 2000 emulate VBA functionallity? For example, I would like to use Instr(...) or IsNull(...) in my SQL statements to the SQL Server 2000. Can I use the user defined functions?

 

[ScottAW]

I heard in the summit for SQL2000 that there are places in 2000 that you can use VBScript. I am still in the world of SQL7 and haven't had a chance to play with it yet. SQL does, however, have similar functions. IsNull works the same with the addition of a parameter (look in books online -- if you don't have access to it post back and I'll give detail). As far as InStr, SQL Server uses a function called charindex. Again, books online will give you the full scoop, but if you need a Cliff notes version, post the question and I'll bore everyone with the details.

 

[tlbroadbent]

Note that the SQL IsNull function is not equivalent to the VB IsNull function. In VB Isnull returns a boolean value True or False depending on the NULL status of the value being tested. In SQL Server IsNull substitutes a value for a NULL value. The VB equivalent is the NZ function.

Charindex functions like Instr but the syntax differs. You can create UDFs in SQL 2000 but not with VBScript. You can use VB script in ActiveX DTS tasks. Apparently, the next version of MS SQL Server will allow SP and function programming more languages than SQL - such as VB and C++. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.