Below
function takes as parameter a text, split the text in tokens, and returns a
table containing all this tokens.
CREATE FUNCTION ParseText2File(@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while
len(@p_text) > 0 and charindex(@p_delimiter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_delimiter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_delimiter,@p_text))
end
insert @tempResults select @p_text
insert @results
select result
from @tempResults
return
END
Let’s see a practical case when this function can be used. You have a list
of products. Each product belongs to a specific category. You need to return
all products belonging to a list of categories.
An approach could be to create a stored procedure GetProducts(@categories
varchar(4000)) which has a parameter containing all categories ids separated by
comma like this: '1,3,6,11'. Using the above function,
the solution of our problem is simple:
CREATE PROCEDURE
GetProducts(@categories varchar(4000))
As
SELECT p.productId, p.productName
FROM products p
WHERE p.categoryId IN (SELECT id
FROM dbo.ParseText2Table(@categories))
A different approach can be found here:
http://www.codeproject.com/database/SQL_UDF_to_Parse_a_String.asp
P.S. Of course, you can take in consideration sp_executesql function.
Today my boss asked me to add
a new feature to the application I work on: to make a database backup every time
the application is closed.
I was very surprised to do
this in less then 1 hour using SQLDMO library which is shipped with SQL Server
2000. The dll itself is a COM object and you must reference it from your
.net project as such. The IDE will create the necessary COM wrappers
needed to use the library.
using
SQLDMO;
//Necessary
declarations
SQLServer2Class server = new SQLServer2Class();
Database2 database;
Backup2Class backup = new
Backup2Class();
//Connect to server
server.LoginSecure = true;
server.Connect(“server”, “user”, “pass”);
//Select database you want to backup
database = (Database2) databases.Item(“database_name”,
null);
backup.Database
= database.Name;
//Specify
the place where backup to be saved.
backup.Files = “C:\mydb.bak”;
//Effective backup of the database
backup.SQLBackup(server);
//Disconnect, don’t forget it
server.DisConnect();
As you can see, this is a
much easier alternative when SQL information or control is needed.