Updating text and ntext fields in a stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating text and ntext fields in a stored proc

Author

Topic

alinp75
Starting Member

2 Posts

Posted - 2002-01-23 : 16:25:10

I am trying to read the content of a file and update the text field.
I'm doing this in VO (CA Visual Objects) and I'm trying to build a stored procedure to which I want to pass the name of the table, name of the field, primary key value and the text that I read from the file and I want to do an update.
How do I do this? I read in chunks and use WRITETEXT???
I have no idea how to approach the stored procedure...
Please help!

Al

Edited by - alinp75 on 01/23/2002 17:28:59

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-01-23 : 17:45:51

alinp75,

G'day, there are alot of gaps in what you are saying here, it's a bit unclear. That is probably why nobody has tried to answer your question. Now if all you want to do is pass TABLENAME, FIELDNAME, Primary Key, and TEXT to update a field in the passed table then it's quite simple.

You do know how to create a sproc don't you.. Look up BOL CREATE PROCEDURE.

All your parameters apart from may be the Primary Key would be strings, so the best way I think you would do this is through DYNAMIC SQL. You can either:

Declare @SQLStr nvarchar(500)
Set @SQLStr = N'UPDATE ' + TABLENAME
Set @SQLStr = @SQLStr + ' SET ' + FIELDNAME + ' = ''' + TEXT
Set @SQLStr = @SQLStr + ' WHERE PRIMARYKEY_1 = ' + cast(Primary Key 1 as varchar(X - length of datatype))
Set @SQLStr = @SQLStr + ' AND PRIMARYKEY_2 = ' + cast(Primary Key 2 as varchar(X - length of datatype))

execute sp_Executesql @SQLStr

OR

Declare @SQLStr varchar(500)
Set @SQLStr = 'UPDATE ' + TABLENAME
Set @SQLStr = @SQLStr + ' SET ' + FIELDNAME + ' = ''' + TEXT
Set @SQLStr = @SQLStr + ' WHERE PRIMARYKEY_1 = ' + cast(Primary Key 1 as varchar(X - length of datatype))
Set @SQLStr = @SQLStr + ' AND PRIMARYKEY_2 = ' + cast(Primary Key 2 as varchar(X - length of datatype))

exec(@SQLStr)

Hope that helps.. Well then again I'm only working off what you have given me..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman

alinp75
Starting Member

2 Posts

Posted - 2002-01-24 : 09:54:34

Thanks fisherman_jake!

That's close to what I want. The field I'm trying to update is a "text" or "ntext" type. The "book" says something about using WRITETEXT and using text pointers. My scenario is this: I have files sizes between 1k to 1000k and I want to store them in a table in a TEXT or NTEXT type field using stored procedures. My database functions are failing and using ODBC API is not an option...
Need your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources