passing a parameter into a view 1

[DanC]

Is there any way to pass a parameter into a view? What I'm trying to do is create a view based on a certain state.

DECLARE @state VARCHAR(2)

SET @state = 'CA'

SELECT * FROM tier2v2
WHERE tier2v2.STATE = @state

works fine, but when I try this:

DECLARE @state VARCHAR(2)
SET @state = 'CA'

CREATE VIEW TEMP_TABLE AS

SELECT * FROM tier2v2
WHERE tier2v2.STATE = @state

I get errors. I know this is probably a simple question, but I did search the forum and couldn't find anything that applied.

 

[MAK9974]

Here u go.

DECLARE @state VARCHAR(2)
SET @state = 'CA'
declare @query varchar(1000)

set @query = "CREATE VIEW TEMP_TABLE AS SELECT * FROM tier2v2
WHERE tier2v2.STATE = '"+@state+"'"
print @query
exec (@query)

 

[DanC]

darn, didn't work for me. this is the error I got:

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name 'CREATE VIEW TEMP_TABLE AS SELECT * FROM tier2v2
WHERE tier2v2.STATE = ''.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name '''.

 

[MAK9974]

use this statement before the procedure or view etc

SET QUOTED_IDENTIFIER OFF

 

[bperry]

I don't think the road you are travelling here is going to work. My feeling is that the DDL statement to construct the view is completely unaware of the variable @state.

And in a way, you might not be really achieving all that much anyway.

i.e. To say this

declare @state varchar(2)
set @state = 'CA'
Select from TempTable

is not really much improvement (even if it had worked) over

SELECT * FROM tier2v2
WHERE tier2v2.STATE = @state

So I don't think there's much solution here. You may as well just stick with that.

----------------------------------

Where you do find Views more commonly used is to save the trouble of passing the variable at all, like this:

CREATE VIEW vwCalifornia
As
SELECT * FROM tier2v2
WHERE tier2v2.STATE = 'CA'

Then users can do this:
Select * from vwCalifornia

However, this is not really what you wanted either.
---------------------
bperry

 

[tlbroadbent]

You cannot pass a parameter to a view. If you need to parametrize, create a stored procedure.

CREATE Procedure uspTier2v2ByState
@state char(2) AS

SELECT * FROM tier2v2
WHERE STATE = @state Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.