Parameters for table name in stored procedure

[amaaiia]

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 tried with @lakehouse_name.@schema_name.@table_name but it's not working. I've seen I have QUOTENAME option but I don't know how to perform the query.

[govindarajan_d · Accepted solution]

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]

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]

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.