Dynamic SQL Help

I am getting the following error when trying to use dynamic sql. I've tried
several ways to fix it, but have not been able to figure it out. Can someone
show me how to fix this? Thanks in advance for any help.
Error for the following stored procedure:
Invalid operator for data type. Operator equals add, type equals text.
ALTER PROC UpdateTable1Field(
@.tableID uniqueidentifier,
@.fieldUpdated varchar(50),
@.fieldText text)
AS
DECLARE @.sql nvarchar(4000)
SET @.sql = N'DECLARE @.ptrVal binary(16)
SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
EXEC sp_executesql @.sqlTry,
SET @.sql = N'
DECLARE @.ptrVal binary(16)
SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated + N')
FROM Table1 WHERE TableID = @.TableID
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
@.tableID, @.fieldText
go
AMB
"Mike Collins" wrote:

> I am getting the following error when trying to use dynamic sql. I've trie
d
> several ways to fix it, but have not been able to figure it out. Can someo
ne
> show me how to fix this? Thanks in advance for any help.
> Error for the following stored procedure:
> Invalid operator for data type. Operator equals add, type equals text.
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
> EXEC sp_executesql @.sql
>|||TEXT datatype cannot be used with '+' operator, try changing @.fieldText to
varchar()
- Sha Anand
"Mike Collins" wrote:

> I am getting the following error when trying to use dynamic sql. I've trie
d
> several ways to fix it, but have not been able to figure it out. Can someo
ne
> show me how to fix this? Thanks in advance for any help.
> Error for the following stored procedure:
> Invalid operator for data type. Operator equals add, type equals text.
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
> EXEC sp_executesql @.sql
>|||Hello, Mike
You can (but shoudn't) use something like this:
ALTER PROC UpdateTable1Field(
@.tableID uniqueidentifier,
@.fieldUpdated varchar(50),
@.fieldText text)
AS
DECLARE @.sql nvarchar(4000)
SET @.sql = N'DECLARE @.ptrVal binary(16)
SELECT @.ptrVal=TEXTPTR(' + @.fieldUpdated +
') FROM Table1 WHERE TableID = @.tableID
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
@.tableID, @.fieldText
However, you should know that:
1. Using TEXTPTR and WRITETEXT this way assumes that there was a value
in that text column (i.e. it was not NULL), before executing the
procedure.
2. Dynamic SQL is usually a bad idea. If a stored procedure contains
Dynamic SQL, a lot of the reasons for using a stored procedure
(security, performance, maintanability) are now voided. Read the
following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/dynamic_sql.html
especially the part about "Common Cases when to (Not) Use Dynamic SQL".
Razvan|||Thanks, that works except I need to figure out what the following error mean
s.
NULL textptr (text, ntext, or image pointer) passed to WriteText function.
"Alejandro Mesa" wrote:
> Try,
> SET @.sql = N'
> DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated + N')
> FROM Table1 WHERE TableID = @.TableID
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
> EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
> @.tableID, @.fieldText
> go
>
> AMB
>
> "Mike Collins" wrote:
>|||What about when my data is too long for a varchar data type? Although not in
all, in many cases it will be too long.
"Sha Anand" wrote:
> TEXT datatype cannot be used with '+' operator, try changing @.fieldText
to
> varchar()
> - Sha Anand
> "Mike Collins" wrote:
>|||Thanks...will this article point me to an alternative to what I am trying to
do? I will not know the column to update ahead of time.
"Razvan Socol" wrote:

> Hello, Mike
> You can (but shoudn't) use something like this:
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal=TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = @.tableID
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
> EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
> @.tableID, @.fieldText
> However, you should know that:
> 1. Using TEXTPTR and WRITETEXT this way assumes that there was a value
> in that text column (i.e. it was not NULL), before executing the
> procedure.
> 2. Dynamic SQL is usually a bad idea. If a stored procedure contains
> Dynamic SQL, a lot of the reasons for using a stored procedure
> (security, performance, maintanability) are now voided. Read the
> following article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/dynamic_sql.html
> especially the part about "Common Cases when to (Not) Use Dynamic SQL".
> Razvan
>|||Mike,
The error is because the column was not initialized. Put some value or empty
string when you insert the row. See WRITETEXT in BOL for more info.
create table dbo.t1 (
c1 int not null identity,
c2 text
)
go
create procedure dbo.p1
@.c1 int,
@.c2 text
as
set nocount on
declare @.sql nvarchar(4000)
SET @.sql = N'
DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(c2)
FROM dbo.t1 WHERE c1 = @.c1
WRITETEXT dbo.t1.c2 @.ptrval @.c2'
EXEC sp_executesql @.sql, N'@.c1 int, @.c2 text', @.c1, @.c2
go
insert into dbo.t1(c2) values('uno')
go
exec dbo.p1 1, 'dos'
go
select * from dbo.t1
go
drop procedure dbo.p1
go
drop table dbo.t1
go
AMB
"Mike Collins" wrote:
> Thanks, that works except I need to figure out what the following error me
ans.
> NULL textptr (text, ntext, or image pointer) passed to WriteText function.
> "Alejandro Mesa" wrote:
>|||Thanks...it works great now.
"Alejandro Mesa" wrote:
> Mike,
> The error is because the column was not initialized. Put some value or emp
ty
> string when you insert the row. See WRITETEXT in BOL for more info.
> create table dbo.t1 (
> c1 int not null identity,
> c2 text
> )
> go
> create procedure dbo.p1
> @.c1 int,
> @.c2 text
> as
> set nocount on
> declare @.sql nvarchar(4000)
> SET @.sql = N'
> DECLARE @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(c2)
> FROM dbo.t1 WHERE c1 = @.c1
> WRITETEXT dbo.t1.c2 @.ptrval @.c2'
> EXEC sp_executesql @.sql, N'@.c1 int, @.c2 text', @.c1, @.c2
> go
> insert into dbo.t1(c2) values('uno')
> go
> exec dbo.p1 1, 'dos'
> go
> select * from dbo.t1
> go
> drop procedure dbo.p1
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Mike Collins" wrote:
>|||Mike Collins (MikeCollins@.discussions.microsoft.com) writes:
> Thanks...will this article point me to an alternative to what I am
> trying to do? I will not know the column to update ahead of time.
Yes, exactly this case is covered in the article.
And if you don't know the column to update ahead of time, there is
some fishy going on anyway. A stored procedure in a well-designed
data model would always operate on known tables and known columns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx