CODEWITHSUNDEEP
sqlsql-server-2008-r2
s.k.paul
s.k.paul

Reputation: 7291

Save view with variable in sql server

I've designed a complex view in sql server 2008 R2. Which is similar as follows-

SELECT column1,column1 FROM Table WHERE column3=@VarName

I would like to save it. But I'm getting following message-

Must declare the scalar variable "@VarName".

Any help?

Upvotes: 0

Views: 808

Answers (2)

veljasije
veljasije

Reputation: 7092

Views are database object that acts close to a table except to not storing data in itself, like a table.

By itself, views are restricting of using some elements like ORDER BY clause, parameters and so on...

If you want to deal with variables or parameters that you pass from main code, then you use user defined functions (UDFs).

Table-valued functions (TVF) are similar to views and can use them in main code in JOIN clause and also can accept parameters.

Upvotes: 0

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

You can create a function instead of a view.

create function fntest(@varname varchar(max))
returns table as
Return (
SELECT column1,column1 FROM Table WHERE column3=@VarName
)

Upvotes: 2

Related Questions