[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8064
  • Last Modified:

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?
0
Mr_Shaw
Asked:
Mr_Shaw
  • 2
  • 2
1 Solution
 
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
 
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
 
Mr_ShawAuthor Commented:
Thanks, will give it a go.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now