CODEWITHSUNDEEP
sqlsql-servervb.netsql-server-2008
Chad Patrick
Chad Patrick

Reputation: 251

Arithmetic overflow error converting varchar to data type numeric. Could not find stored procedure ''.?

I'm getting this error when executing my stored procedure (SP_StoredProcedure):

enter image description here My stored procedure (SP_StoredProcedure) gathers some data from VB.NET before executing

dim TableName string = "Table1"
dim acctcode as string "Table1.acctcode"
dim month as integer = 1
dim year as integer = 2011

Here is my code for executing stored procedure (SP_StoredProcedure) FROM VB.NET:

execute("EXECUTE SP_StoredProcedure '"& TableName &"','"& acctcode &"',"& month &","& year &"")

Here is my code on SP_StoredProcedure:

CREATE PROCEDURE [dbo].[SP_StoredProcedure]
@TableName as varchar(100),
@AcctCode as varchar(100),
@Month as numeric(18,0),
@Year as numeric(18,0)

AS
BEGIN
DECLARE @Query as varchar(1000)
SET @Query = 
'UPDATE Table2 SET Dat = (SELECT Dat FROM '+ @TableName +' WHERE table2.acctcode = '+ @acctcode +' AND month = '+ @month +' AND year = '+ @year +') FROM Table2'
EXEC @Query
END

Upvotes: 0

Views: 1500

Answers (1)

Mercyful
Mercyful

Reputation: 107

When doing dynamic queries in varchar strings the numeric values need to be converted and this is important for dates too! Otherwise it doesn't append properly to the @Query variable.

Also if @acctcode is a varchar, then you need to escape it with extra single quotes so that that it will handle it like a string properly.

ie.. 

SET @Query = ' UPDATE Table2 ' + 
             ' SET Dat = ( ' +
             '     SELECT Dat FROM ' + @TableName + 
             '     WHERE table2.acctcode = '''+ @acctcode +''' +
             '     AND month = ' + convert(varchar(18), @month) + 
             '     AND year = '+ convert(varchar(18), @year) +
             ' ) ' + 
             ' FROM Table2 '

Upvotes: 1

Related Questions