Variables in a SQL View

[jjpark75]

If it's possible, how do you utilize variables in a SQL View? Everytime I try to declare the variable and use it, it gives me the following error:

Server: Msg 156, Level 15, State 1, Procedure sample_this2, Line 5
Incorrect syntax near the keyword 'declare'.

Thanks.

Jung

 

[osjohnm]

Hi

You can't use variables in views and therefore it is failing at the "DECLARE" keyword since it isn't supported.

Remember a view is a "logical" table with certain data extracted from multiple tables or a single table. So when you are querying the view it acts like a table within sql server.

I am a bit confused as to why you would want to pass a variable to a view?
What is it exactly that you are trying to do or achieve?
Perhaps there is another way of doing it?

John

 

[jjpark75]

That makes sense. Thanks.

Ultimately, I would like to call up a View or Stored Proc from Crystal Reports. Crystal Reports will ask for 2 parameters and pass them to the View or Stored Proc and then spit out the resultant data set.

I'm a bit new to Views and Stored Procs. Any ideas on how to begin the above? Thanks.

Jung

 

[osjohnm]

Hi

If you are going to be passing parameters then go the stored proc route.

You could still use views in the stored proc if you want to limit the data being fetched for the report.

here is a basic example

CREATE PROCEDURE sp_test @p1 int, @p2 int
AS
select * from table1
where column1 = @p1
and column3 <> @p2
-- if you wanted to use the view then
and column4 exists in (select * from view1)

or

select t.col1, t.col2 from table t, view v
where t.col1 = @p1
and v.col2 = @p2

then all you do is execute the procedure passing the values:

execute sp_test 13, 14

take a look in BOL about stored procedures

Hope that gets you started

John