Menu
Is it possible to declare a variable within a View? For example:
Declare @SomeVar varchar(8) = 'something' gives me the syntax error:
Incorrect syntax near the keyword 'Declare'.
887
No, we cannot use variables in a SQL Server View. This is because a view is just like a SQL statement with a name. Moreover, it does not exist physically in a database in SQL Server.
You can't declare variables in views. User stored procedure or function instead.
Yes this is correct, you can't have variables in views (there are other restrictions too). Views can be used for cases where the result can be replaced with a select statement. A table function can be replaced with a select statement as well, but table functions cannot be used everywhere that a view can, such as joins.
Unlike base tables, VIEW s are either updatable or read-only, but not both. INSERT , UPDATE , and DELETE operations are allowed on updatable VIEW s and base tables, subject to any other constraints.
You are correct. Local variables are not allowed in a VIEW.
You can set a local variable in a table valued function, which returns a result set (like a view does.)
http://msdn.microsoft.com/en-us/library/ms191165.aspx
e.g.
CREATE FUNCTION dbo.udf_foo() RETURNS @ret TABLE (col INT) AS BEGIN DECLARE @myvar INT; SELECT @myvar = 1; INSERT INTO @ret SELECT @myvar; RETURN; END; GO SELECT * FROM dbo.udf_foo(); GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
