Visual Studio Database Project - Use SqlCmd Variable as Schema Name

Question

I am trying to create stored procedures with different schemas using SQL Command Variables, however I cannot build the project due to the error:

SQL71501: Procedure: [$(SchemaName)].[pr_MySproc] has an unresolved reference to Schema [$(SchemaName)].

This is how I have my stored procedure written:

CREATE PROCEDURE [$(SchemaName)].[pr_MySproc]
    @Param1 tinyint = 0,
    @Param2 INT = 0
...

And then a series of publish profiles all containing variables similar to this:

<SqlCmdVariable Include="SchemaName">
  <Value>SCHEMA1</Value>
</SqlCmdVariable>

Is what I'm trying to do even possible? Is there a way to specify the schema via SQL Command Variables?

You could, but you would have have to use dynamic SQL; you can't parametrise a schema. This sounds like an XY problem to me. — Thom A
– Thom A ♦, Commented May 14, 2019 at 17:48

user11380812 · Accepted Answer · 2019-05-14 19:01:18Z

In your case, you should use dynamic T-SQL. However, you should consider some additional options.

First, if you really know your schema name it is completely safe to create a schema and then the stored procedure. From the project context menu, choose 'Add new item' and then under 'Security' node choose 'Shema', as shown in the image below.

After that you can add the stored procedure by prefixing with newly created shema, see the image below.

Visual Studio is clever enough to put the schema creation before the stored procedure creation. There is a second option as well. You could create your stored procedure in 'dbo' schema. Then in the PostDeployment script, you could transfer your stored procedure to the 'right' schema. The schema name could be replaced by using SQLCMD variable.

So, basically in the PostDeployment script you put the statement like this

ALTER SCHEMA myShema TRANSFER dbo.myStoredProcedure;

Before that you should execute dynamic T-SQL like shown in the following snippet.

:setvar ShemaName "MyShema"


IF NOT EXISTS (
    SELECT schema_name
    FROM information_schema.schemata
    WHERE schema_name = '$(ShemaName)')

BEGIN
    EXEC sp_executesql N'CREATE SCHEMA $(ShemaName)'
END

Collectives™ on Stack Overflow

Visual Studio Database Project - Use SqlCmd Variable as Schema Name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related