Dynamic sql problem

Hello: I am tearing my hair out over this.

I am using sql server 2005 and am getting the error "arithmetic overflow error converting nvarchar to numeric." The sp is below. It works fine when I do not include the numeric variable @.z and only insert the other 3 fields. The varbinary(MAX) is not the problem. The problem is syntax error mixing numbers and strings. How do I put the numeric @.z in the @.query string so I can insert a value into the FileID numeric field?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER off

ALTER PROCEDURE [dbo].[uspInsert_Blob] @.x as nvarchar(60), @.y as nchar(5), @.z as numeric(18, 0)

as

DECLARE @.QUERY VARCHAR(2000)

SET @.QUERY = "INSERT INTO myTable(FileID, FileName, FileType, Document)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " +@.z+ " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

EXEC (@.QUERY)

cast the variable to a nvarchar(18)

SELECT '"+@.x+"' AS FileName, '"+@.y+"' AS FileType, " + cast(@.z as nvarchar(18)) + " as FileID, * FROM OPENROWSET( BULK '" +@.x+"' ,SINGLE_BLOB) AS Document"

|||

Yes that works. I thank you so much.

When the exec query runs, does sql server recast it as numeric since the parameter and the field are numeric?

|||

Yes, it does an implicit conversion based on datatype conversion precedence:

You can see them listed here:
http://msdn2.microsoft.com/en-us/library/ms190309.aspx

All lower precedence types get implicitly converted to the highest precedence type if it is possible.

Louis