Forum Discussion

amaaiia's avatar
amaaiia
Skilled Sharer
2 years ago
Solved

Parameters for table name in stored procedure

I want to have a stored procedure to call it from Lookup activity in several Data Pipelines. I want the procedure to be as follows: I want the "from" table to be ingested with parameters. I've...
data engineering
  • govindarajan_d's avatar
    govindarajan_d
    2 years ago

    Hi amaaiia ,

     

    Declare the parameters as NVARCHAR. It will show an error but still it will work. After that use SET to form the string you want to execute and then use 'sp_executesql' with the string you formed. Make sure you use N in front of the strings.

     

    In the below example, I have formed two strings one with Table name and the other with select query using the table name. 

    DECLARE @MergeQuery NVARCHAR(MAX), @TableName NVARCHAR(MAX);
SET @TableName = N'dbo.Product_Info';
SET @MergeQuery = N'SELECT * FROM ' + @TableName;

EXEC sp_executesql @MergeQuery;
govindarajan_d's avatar
govindarajan_d
Super User
2 years ago

Hi amaaiia ,

 

Declare the parameters as NVARCHAR. It will show an error but still it will work. After that use SET to form the string you want to execute and then use 'sp_executesql' with the string you formed. Make sure you use N in front of the strings.

 

In the below example, I have formed two strings one with Table name and the other with select query using the table name. 

DECLARE @MergeQuery NVARCHAR(MAX), @TableName NVARCHAR(MAX);
SET @TableName = N'dbo.Product_Info';
SET @MergeQuery = N'SELECT * FROM ' + @TableName;

EXEC sp_executesql @MergeQuery;
  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    2 years ago

    Hi amaaiia 


    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
    In case if you have any resolution please do share that same with the community as it can be helpful to others.
    Otherwise, will respond back with the more details and we will try to help.


    Thanks.

    • v-cboorla-msft's avatar
      v-cboorla-msft
      Microsoft Employee
      2 years ago

      Hi amaaiia 

       

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
      If you have any question relating to the current thread, please do let us know and we will try out best to help you.
      In case if you have any other question on a different issue, we request you to open a new thread.


      Thanks.