Execute stored proedure in openquery with parameters

Question

I have a database in SQL Server 2014 and I have a linked server. i want to run a sp which runs inside her a stored procedure which belongs to the linked server.

For example I've got the stored proedure :

PROCEDURE test (@myint  int) 
AS 
      DECLARE @Query VARCHAR(2000)
      SET @Query = 'INSERT INTO mytable SELECT * FROM OPENQUERY([mylinkedserver], ''EXEC  [linkedserverdb].[dbo].[linkedserversp]  
    '+ @myint + '  '')'

       EXEC(@Query)

When I'm running the stored proedure test 42 I'm getting an error:

Conversion failed when converting the varchar value 'INSERT INTO mytable SELECT * FROM OPENQUERY([mylinkedserver], 'EXEC [linkedserverdb].[dbo].[linkedserversp] ' to data type int.

Notice that I'm trying to pass the variable myint from my stored proedure to the linkedserver stored proedure which needs that int.

Any suggestions?

Answer 1

PROCEDURE test (@myint  int) AS 
DECLARE @Query VARCHAR(2000)
SET @Query = 'INSERT INTO mytable SELECT * FROM OPENQUERY([mylinkedserver], ''EXEC  [linkedserverdb].[dbo].[linkedserversp]  
'+ CAST(@myint AS VARCHAR(10)) + ''')'

EXEC(@Query)

You are trying to concatenate a INT @myint with a string 'exec...' you have to cast @myint to a compatible character type e.g. VARCHAR.