Creating View

Question

Post reply

Creating View

dsrapid

SSC-Addicted

Points: 485
More actions
September 6, 2011 at 8:17 am

#256721

Hi,
I have a table with below structure:
COLA B C D E
int int int datetime varchar
I want to create a view where I select ColA, B, C , D and I want my user to provide the input value for COl A and COl D.
(Wants to use COL A and D in where clause and user should enter the value.)
Thanks in advance !
Nidhi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

You cannot create a parameterized view in SQL Server. A Table-Valued Function (TVF) would probably be your best bet.

something like this,

CREATE FUNCTION dbo.fxnExample (@Parameter1 int)

RETURNS @Results TABLE(col1 VARCHAR(10), col2 VARCHAR(10))

AS

BEGIN

INSERT @Results

SELECT col1, col2

FROM yourTable

WHERE col3 = @Parameter1

RETURN

END

And you would reference it like a table

SELECT * FROM dbo.fxnExample(3)

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

dsrapid SSC-Addicted Points: 485 More actions · Answer 2

Thanks a lot !!

So does that mean that creating an inline function is same as parametrized view.

Regards,

Nidhi

calvo SSChampion Points: 12930 More actions · Answer 3

nidhi.ds.rapid (9/6/2011)
Thanks a lot !!
So does that mean that creating an inline function is same as parametrized view.
Regards,
Nidhi

There is no such thing as a "parameterized view" in SQL Server.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

dsrapid SSC-Addicted Points: 485 More actions · Answer 4

dsrapid

SSC-Addicted

Points: 485

September 6, 2011 at 9:20 am

#1377499

Thanks !!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

calvo (9/6/2011)
nidhi.ds.rapid (9/6/2011)
Thanks a lot !!
So does that mean that creating an inline function is same as parametrized view.
Regards,
Nidhi
There is no such thing as a "parameterized view" in SQL Server.

Not as such , but inline table valued functions can be thought of as such.

The optimizer can 'see' the code within and optimize it into the 'main' queries plan.

Clear Sky SQL
My Blog[/url]

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 6

calvo (9/6/2011)
You cannot create a parameterized view in SQL Server. A Table-Valued Function (TVF) would probably be your best bet.
something like this,
CREATE FUNCTION dbo.fxnExample (@Parameter1 int)
RETURNS @Results TABLE(col1 VARCHAR(10), col2 VARCHAR(10))
AS
BEGIN
INSERT @Results
SELECT col1, col2
FROM yourTable
WHERE col3 = @Parameter1
RETURN
END
And you would reference it like a table
SELECT * FROM dbo.fxnExample(3)

If you don't need any procedural code to "build" your result set, then it would be more efficient to write this as an inline table valued function:

CREATE FUNCTION dbo.fxnExample (@Parameter1 int)

RETURNS TABLE

AS

RETURN (

SELECT col1, col2

FROM yourTable

WHERE col3 = @Parameter1

);