Creating VIEW with schemabinding VS using two-part name (schema.object)

Question

1.00/5 (1 vote)

See more:

Hi,
I'm trying to create a view with schemabinding. For I got the error:

Cannot schema bind view 'nameOfMyTable.vSomethingAddedHere' because name 'nameOfMyTable' is invalid for schema binding.

In order to avoid the error, I tried to use the two-part name (schema.object) but it didn't work.

I previously crated a schema for the table

nameOfMyTable

in order to create the view.

What I have tried:

CREATE VIEW nameOfMyTable.vSomethingAddedHereWITH Schemabinding
AS
SELECT b.Name
FROM [dbo.][nameOfMyTable][n]
INNER JOIN Tickets t ON [n].ID = t.ID
INNER JOIN Peple p ON p.ID = t.ID

Posted 6-Nov-20 1:18am

xhon

Updated 6-Nov-20 1:37am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-11-06T01:38:00

Solution 1

Quote:

SQL

FROM [dbo.][nameOfMyTable][n]

That's a syntax error. You've put the separator (.) inside the square brackets.

Try:

SQL

CREATE VIEW nameOfMyTable.vSomethingAddedHere
WITH Schemabinding
AS
    SELECT b.Name
    FROM [dbo].[nameOfMyTable] [n]
    INNER JOIN Tickets t ON [n].ID = t.ID
    INNER JOIN Peple p ON p.ID = t.ID

Posted 6-Nov-20 1:38am

Richard Deeming

Comments

xhon 6-Nov-20 8:00am

thank you very much!

xhon 9-Nov-20 12:30pm

I have a doubt: can we call a view by using a variable or it's not allowed because it doesn't output a scalar value?
Given that I can't write the following code because I should provide a data type for the variable and the table doesn't have a datatype, is there any other way to provide a variabile?

DECLARE @myView = name_Of_the_View;
S£LECT * FROM @myView

I suppose I should

Richard Deeming 9-Nov-20 13:09pm

No, you can't use a variable to refer to a table or view name. You'd have to use dynamic SQL for that, which would potentially leave you open to a SQL injection vulnerability.

Creating VIEW with schemabinding VS using two-part name (schema.object)

1 solution

Solution 1

Add your solution here

Preview 0

Creating VIEW with schemabinding VS using two-part name (schema.object)

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us