how to declare variables in a view

I have a sql query in a view which I would like to use variables.

(@start_date datetime, @end_date datetime)

when I run the query i get an error saying;

must declare scalar variable @start_date.

I am not sure what this means?
Mr_ShawAsked:
Who is Participating?
 
brad2575Commented:
nope, tried that as well.

What you can do is either of the following (I have done both recently):

1.  Create a generic view(s) for your queries without the varaible/criteria and then in the SP do the cross join there and add the criteria there.  

2.  Just create all 3 queries directly in the SP and do the cross join there with the criteria.

NOTE:  You CAN create a view and then add critera when you select FROM the view.

So if you have a veiw called vw_TestView you can do this in your select statement from the view:

select * from vw_TestView
Where field1 = 'test'
and fiedl2 = 2

0
 
brad2575Commented:
you cant,

You can create a user defined function OR a stored procedure and pass the variables to that and have either the UDF or SP return a record set.

I just did this using a stored procedure and it worked great for me.
0
 
Mr_ShawAuthor Commented:
can I have three stored procedures and link them together with a cross join?
0
 
Mr_ShawAuthor Commented:
Thanks, will give it a go.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.