0

I have an T-SQL script where I'm fetching results from the OPENROWSET function, like this:

DECLARE @TableForBalance TABLE
(
    --fields
)
DECLARE @Param  varchar(15) = 'XXXXXXX'
DECLARE @query varchar(255) = CONCAT('EXEC SomeStoredProcedure ''',@Param,'''')
INSERT INTO @TableForBalance select * FROM OPENROWSET('SQLNCLI', 'Server=10.10.10.10;Trusted_Connection=yes;', @query)

and I'm getting a tip:

Incorrect syntax near '@query'. Expecting '.', ID, QUOTED_ID, STRING or TEXT_LEX

But when I do something like:

INSERT INTO @TableForBalance select * FROM OPENROWSET('SQLNCLI', 'Server=10.10.10.10;Trusted_Connection=yes;', 'EXEC SomeStoredProcedure ''XXXXX''')

it works, but I want the @Param to be dynamic.

Any solution ?? Thanks

Fourat
  • 2,366
  • 4
  • 38
  • 53
  • 1
    http://stackoverflow.com/a/13831792/6167855 – S3S Oct 04 '16 at 15:44
  • open rowset won't accept variables,you can use dynamic SQL though to over come that..https://msdn.microsoft.com/en-us/library/ms190312.aspx – TheGameiswar Oct 04 '16 at 15:45
  • @scsimon Yes but when I do that I get `Must declare @TableForBalance` and I can't put all that script in a string and execute it (it's more complicated than what's shown). – Fourat Oct 04 '16 at 15:48
  • Luckily there are some smart contributors to SO who are good at solving complicated and complex problems, but this is pretty improbable without the entire problem. Be sure to let everyone know the entire problem, since solving one specific part, without a general awareness of the overall query, will likely only lead to follow on questions and/or revisions of answers. What's so complicated? – S3S Oct 04 '16 at 15:50

0 Answers0