SQL Server Table Parameter without defining fields

Question

I've built a SQL Server table from my C# code by taking the ColumnNames from a DataTable as it's derived from an Excel file I import.

This works fine; now I'd like to use SQL table I created in a separate stored procedure without using dynamic SQL because the query is fairly large and it's going to be a pain to maintain if I go that route.

I can't specify the columns in the stored procedure if I create a Table parameter because it's not a static set of columns, which is why I had to build the table dynamically from my C# code.

So I'm essentially looking to do something like this:

CREATE PROCEDURE usp_SprocName

    @TableName VARCHAR(50)

AS

SELECT Something FROM Somewhere R
LEFT JOIN
(
    SELECT
        *
    FROM @TableName HR
    WHERE Field = Whatever
) HR ON R.Field= HR.Field

However, I get the error to declare my @Table variable in my subquery.

Is there some way to get around this without using dynamic SQL?

As @MichałTurczyn stated, you can't use parameters for object names without dynamic sql. If you go the dynamic route, I suggest using quotename as well. — S3S, Dec 06 '17 at 14:16
Something in your design sounds like it went horribly wrong. — Sean Lange, Dec 06 '17 at 14:22
@SeanLange: Please elaborate? Is there something wrong with building a table dynamically, based on your source, if you don't know how many fields it's going to have? Would you rather design a static solution and be fixing exceptions forever and a day? — GrammatonCleric, Dec 07 '17 at 05:45
@MichałTurczyn & scsimo: Thanks for the input. Guess I'll have to use dynamic SQL :( — GrammatonCleric, Dec 07 '17 at 05:47

SQL Server Table Parameter without defining fields

0 Answers0