Can I use a variable for a linked server name in a stored procedure?

HI Experts

I am trying to use a variable for a linked server name in a stored procedure but am coming up short . Some assistance would be greatly appreciated. I am trying to avoid writing the same stored procedure 36 times with different parameters in it

HERE IS THE CODE

CREATE PROCEDURE [dbo].[spIBT]
      @SQLSERVER NVARCHAR(100),@WinUser NVarchar(50),@BranchID INT
      
AS
BEGIN
      
      SET NOCOUNT ON;

   INSERT INTO
      @SQLSERVER -- This would return [ALMACWIN8\SQLEXPRESS].[NCA MasterSQL].[dbo].[Order Details]
      ([MasterID]
      ,[OrderID]
      ,[ProductID]
      ,[BlockNo]
      ,[Length]
      ,[Width]
      ,[DestinationId]
      ,[BMNo]
      ,[Prefix]
      ,[Suffix]
      ,[PalletNo]
      ,[Marker]
      ,[OldProductName]
      ,[IBTDate]
      ,[MarkForIBT]
      ,[WinUser]
      ,[LastModDate]
      ,[ModType]
      )


SELECT [OrderDetailID]
      ,[OrderID]
      ,[ProductID]
      ,[BlockNo]
      ,[Length]
      ,[Width]
      ,[DestinationId]
      ,[BMNo]
      ,[Prefix]
      ,[Suffix]
      ,[PalletNo]
      ,[Marker]
      ,[OldProductName]
      ,[IBTDate]
      ,[MarkForIBT]
      ,@winuser
      ,[LastModDate]
      ,[ModType]

   FROM dbo.[Order Details]
   WHERE MarkForIBT=1 AND completed <>1 AND destinationID=@branchID
END
GO
caandalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
No.  You would have to use dynamic sql instead.  SQL Server does not allow server, table or column names to be variables (nor keywords or operands, for that matter).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
If 38 variable's value is always, you can create a master table and pass the value from this table to stored procedure.
0
caandalAuthor Commented:
Thanks Scott

Please can you elaborate a bit more on "dynamic SQL"
0
caandalAuthor Commented:
Sorry Depak but I think that you are missing the point
0
Scott PletcherSenior DBACommented:
Sure.  Dynamic SQL is when you use a string variable to compose a SQL command and then "tell" SQL to execute that command.  The "Hello World" version would be:

EXEC('SELECT GETDATE() AS current_date')

I'll have to provide more details later, too busy right now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.