Problem with my stored procedure

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)

Problem with my stored procedure

Author

Topic

Mazdak
Yak Posting Veteran

63 Posts

Posted - 2003-02-08 : 06:51:12

Whats Wrong with this stored procedure?

CREATE PROCEDURE sp_Votes_SetVotes
@Table nvarchar(50),
@ID bigint,
@Vote text
AS

Declare @strUpdate varchar(250)
set @StrUpdate = 'Update ' + @Table + ' Set col1 =' + @Vote + ' WHERE ID = ' + @ID
EXEC @strUpdate
GO

I get this error:
Error 403: Invalid operator for data type. Operator equals add, type equals text.

This happend when I add @Vote parameter.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-02-08 : 07:30:22

try this:

CREATE PROCEDURE Votes_SetVotes -- do not use sp_ prefix
	@Table NVARCHAR(128), -- of SYSNAME
	@ID    BIGINT,
	@Vote  TEXT
AS
	SET NOCOUNT ON

	DECLARE @SQL NVARCHAR(4000)

	SET @SQL = 'UPDATE ' + @Table + 
	           '   SET col1 = @Vote' +
	           ' WHERE ID = @ID'
	EXEC sp_executesql @SQL, '@Vote TEXT, @ID BIGINT', @Vote, @ID
	RETURN 0
GO

not sure the UPDATE statement will work for TEXT values that are larger than 8000 characters.

The reason you use sp_executesql is you can include dynamic parameters.

Of course the current executing user will need to have UPDATE permissions on the @Table passed in, this is the downside of using dynamic sql, it does not support a secure strategy.

Edited by - onamuji on 02/08/2003 07:31:29

Mazdak
Yak Posting Veteran

63 Posts

Posted - 2003-02-08 : 08:59:48

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources