CODEWITHSUNDEEP
sql-server-2008stored-procedures
LoneXcoder
LoneXcoder

Reputation: 2163

SQL Server stored procedure parameters data type error

The following stored procedure in SQL Server 2008 was throwing an error 

/****** Object:  StoredProcedure [dbo].[UpdateCPA]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pdateCPA_INT]
    @CPAColumn_Name nvarchar(100), @value Int, @RecordNum nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    --declare @CPAColumn_Name nvarchar(100) = 'UsrsID'
    --declare @value Int = 3575
    --declare @RecordNum int = 1

    declare @thedate smalldatetime
    set @thedate = GETDATE()
    --select @thedate as NOW

    declare @cmd nvarchar(max)

    set @cmd = 'Update tblTimeCPAReport 
    set ' + @CPAColumn_Name + ' = '+@value+' 
    set ReportLastUpdate = ' + @thedate + '
    where RecordNum='+@RecordNum

    exec sp_executesql @cmd
    select @cmd
END

It's throwing this error

Conversion failed when converting the nvarchar value 'Update tblTimeCPAReport set UsrsID = ' to data type int.

Upvotes: 0

Views: 6627

Answers (3)

Taryn
Taryn

Reputation: 247860

You need to cast() the int parameter -- cast(@value as nvarchar(100)):

/****** Object:  StoredProcedure [dbo].[UpdateCPA]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pdateCPA_INT]
    @CPAColumn_Name nvarchar(100), @value Int, @RecordNum nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    --declare @CPAColumn_Name nvarchar(100) = 'UsrsID'
    --declare @value Int = 3575
    --declare @RecordNum int = 1

    declare @thedate smalldatetime
    set @thedate = GETDATE()
    --select @thedate as NOW

    declare @cmd nvarchar(max)

    set @cmd = 'Update tblTimeCPAReport 
    set ' + @CPAColumn_Name + ' = '''+cast(@value as nvarchar(100))+''' 
       ,  ReportLastUpdate = ''' + convert(nvarchar(25), @thedate, 120) + '''
    where RecordNum='''+@RecordNum+''''

    exec sp_executesql @cmd
    select @cmd
END

Since your @cmd is the datatype nvarchar(max) all of the parameters being used need to be similar, including:

@value  -- use cast(@value as nvarchar(100))
@thedate --- use convert(nvarchar(25), @thedate, 120)

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40359

1) You should cast @value to a varchar

2) That second “set” will cause an error, propt syntax is SET <col> = <values> [, <col> = Value>, …]

3) Cast @thedate as a varchar, and enclose it in quotes

4) If @Recordnum is a string, put quotes around it as well, otherwise it’s good

Using all the above, the following:

set @cmd = 'Update tblTimeCPAReport 
set ' + @CPAColumn_Name + ' = ''' + cast(@value as varchar(10)) + '''
set ReportLastUpdate = ''' + convert(varchar(50), @thedate, 109) + '''
where RecordNum = ''' + @RecordNum + ''''

should produce a string like:

Update tblTimeCPAReport 
 set <CPAColumn_Name> = <@value>
  ,ReportLastUpdate = '<@thedate>'
 where RecordNum = '<@RecordNum>'

(Factor out the quotes around @RecordNum if it is contains a numeric value)

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Whenever the server sees a +, it examines the types on both sides, and if they're different, it has to perform a conversion.

For ' = '+@value+', on the left we have a string (nvarchar(max)) on an the right an int. It decides to convert the string to an int.

To prevent this, convert the int to a string yourself: ' = '+CONVERT(nvarchar(10),@value)+'

Upvotes: 1

Related Questions