Concetenate variable in sp_executesql

Hi

I am trying to cinstruct two strings/variables and conetenate it in sp_executsql

Isnt this the right way to do it ?

CREATE PROCEDURE dbo.XML_Final

(

@.Filepath varchar(50),

@.DBName varchar(20))

AS

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

--DECLARE @.SQL nvarchar(200)

DECLARE @.INSERT nvarchar(max)

DECLARE @.WITH nvarchar(max)

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc OUTPUT

EXECsp_xml_preparedocument @.hdoc OUTPUT, @.doc

SELECT @.INSERT = N' INSERT INTO'+ @.DBName + N'.dbo.Student( Name,Age,Address)'

--SET @.WITH = N @.TEMP1

SELECT @.WITH = N' SELECT * FROM OPENXML (@.hdoc,'+N'''/Name/Age'/Address+N''')' +N'

WITH

(

Name text'+ N' ' + '''http://Name' + N ''',

Age int' + ' ' + N'''http://Age+ N''',

Address int'+ ' ' + N'''http://Address'+ N'''

)'

exec sp_executesql @.INSERT+@.WITH

-- Remove the internal representation.

EXEC sp_xml_removedocument @.hdoc

Gives me a error at

exec sp_executesql @.INSERT+@.WITH :

Incorrect syntax near '+'

Thanks

You cannot concatenate the input variables like that. Try:

CREATE PROCEDURE dbo.XML_Final

(

@.Filepath varchar(50),

@.DBName varchar(20))

AS

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

--DECLARE @.SQL nvarchar(200)

DECLARE @.INSERT nvarchar(max)

DECLARE @.WITH nvarchar(max)

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc OUTPUT

EXECsp_xml_preparedocument @.hdoc OUTPUT, @.doc

SELECT @.INSERT = N' INSERT INTO'+ @.DBName + N'.dbo.Student( Name,Age,Address)'

--SET @.WITH = N @.TEMP1

SELECT @.WITH = N' SELECT * FROM OPENXML (@.hdoc,'+N'''/Name/Age'/Address+N''')' +N'

WITH

(

Name text'+ N' ' + '''http://Name' + N ''',

Age int' + ' ' + N'''http://Age+ N''',

Address int'+ ' ' + N'''http://Address'+ N'''

)'

set @.SQL = @.INSERT + @.WITH

exec sp_executesql @.SQL, N'@.hdoc int', @.hdoc

-- Remove the internal representation.

EXEC sp_xml_removedocument @.hdoc