What's the correct syntax to get this CLR stored proc to compile with parameters?

Coreysan 1,806 Reputation points
2023-07-14T00:12:26.59+00:00

(Special thanks to Erland, who helped me get going with CLR. I love this stuff!)

I have a stored proc with a hard-coded table:

ALTER PROCEDURE [dbo].[MSO_InsertMemberCheckData]
(
	@myTableType MyTableType readonly
)
AS
BEGIN
	CREATE TABLE MSO.dbo.tmp220(idx int identity(1, 1) primary key, wstr varchar(2000))

	INSERT INTO MSO.dbo.tmp220 select wstr from @myTableType
END

How can I get this to work if I pass in the table name as a parameter? I've tried dynamic sql, but I don't know how to build a string with the user type @myTableType.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,453 questions
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,511 Reputation points Microsoft Vendor
    2023-07-14T03:27:44.2466667+00:00
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 116.6K Reputation points MVP
    2023-07-14T08:04:15.03+00:00

    I think a better question is: why do you want the table name to be dynamic? In very many cases, this desire is due to a mistake in the design. You probably want one single table, with a key column that distinguishes these small tables you had intended to create.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.