Data Type Error In Stored Procedure -Updating a Table

Question

Post reply

Data Type Error In Stored Procedure -Updating a Table

cajun_sql

SSCommitted

Points: 1842

May 31, 2019 at 9:54 pm

#3649191

I have the below stored procedure, and I am getting this error message:

Error converting data type nvarchar to int.

Thanks for any leads.

Here is my table structure:

CREATE TABLE [dbo].[01_news_test]   
(
     [interestid] [INT] IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
     [username]   [VARCHAR](50) NULL,
     [approval]   [BIT] NULL,

     CONSTRAINT [PK_news_test] 
         PRIMARY KEY CLUSTERED ([interestid] DESC)
                     WITH (pad_index = OFF, statistics_norecompute = OFF, 
                           ignore_dup_key = OFF, allow_row_locks = on, 
                           allow_page_locks = on, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Here is the code for the stored procedure (below):

ALTER PROCEDURE [dbo].[01_cms_news_mgr_bull_appr_update]
    @InterestID INT,
    @Approval BIT
AS
    DECLARE @sql NVARCHAR(4000)

    SELECT @sql = ' UPDATE [01_news_test] ' +
              ' SET ' +
              ' Approval = @Approval ' +
              ' WHERE ' 

    IF @InterestID IS NOT NULL 
        SELECT @sql = @sql + ' InterestID = @InterestID'

    SELECT @sql = @sql + '; '

    EXEC sp_executesql @sql, N'@InterestID INT, @Approval BIT', InterestID, Approval

And, here is the code upon execution of the stored procedure in SQL Server Management Studio:

DECLARE @return_value INT

EXEC    @return_value = [dbo].[01_cms_news_mgr_bull_appr_update]
               @InterestID = 1,         
               @Approval = 0

SELECT  'Return Value' = @return_value
GO

Finally, the resulting error msg:

Error converting data type nvarchar to int

This topic was modified 5 years, 5 months ago by cajun_sql.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

Should be like this, me thinks:

EXEC sp_executesql @sql, N'@InterestID INT, @Approval BIT', @InterestID, @Approval

I didn't encounter 'Incorrect syntax near '01' thou.

--Vadim R.

cajun_sql SSCommitted Points: 1842 More actions · Answer 2

(Your answer solved the error - thanks!)

Thanks for your input.

The first part of my post was an typo on my part - I had been getting the 'incorrect syntax' previously but enclosed my table name in brackets to take care of that. Just forgot to change the error to the error that appears now.

The error that I then received was the 'Error converting data type nvarchar to int'.

Yes, I had neglected to prepend the variables on the last line with '@'s, and now the script does work without error (below).

Thanks for helping me out!

ALTER PROCEDURE [dbo].[01_cms_news_mgr_bull_appr_update]
    @InterestID INT,
    @Approval BIT
AS
    DECLARE @sql NVARCHAR(4000)

    SELECT @sql = ' UPDATE [01_news_test] ' +
              ' SET ' +
              ' Approval = @Approval ' +
              ' WHERE ' 

    IF @InterestID IS NOT NULL 
        SELECT @sql = @sql + ' InterestID = @InterestID'

    SELECT @sql = @sql + '; '

    EXEC sp_executesql @sql, N'@InterestID INT, @Approval BIT', @InterestID, @Approval

This reply was modified 5 years, 5 months ago by cajun_sql.
This reply was modified 5 years, 5 months ago by cajun_sql.
This reply was modified 5 years, 5 months ago by cajun_sql.

Michael L John One Orange Chip Points: 27054 More actions · Answer 3

It does not appear that this procedure needs to be Dynamic SQL.

Is there a reason that this is written using Dynamic SQL?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/