October 19, 2009 at 6:53 am
Does anyone know if you can put a 'Declare' statement in a view statement?
Yes I have tried.
It (the query) seems to work fine it I just run it in a query. Also it seems to work fine in a Stored Procedure, but not in a view.
I need a view to create joins with other views and tables.
P.S. Yes I am new to all this.
Thank you for any help,
October 19, 2009 at 7:08 am
You cant do that, a view is a single select statement.
Can you give an example of what you are attempting ?
October 19, 2009 at 8:04 pm
Please post your code, so we can understand what you need the declare statement for. You may be able to do what you want using a user defined, table-valued function. Hopefully an inline table-valued function.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 5:52 am
It is an open query with a variable, I'm new at this so.... it may not be the most conservative choice of coding.
Declare
@CurrentDay int
, @dtDateFirst smalldatetime
, @dtDateEnd smalldatetime
, @strMyDateEnd nvarchar(8)
, @MyQuery_01 nvarchar(4000)
, @MyQuery_02 nvarchar(4000)
, @MyQuery_03 nvarchar(4000)
Set @CurrentDay = Day(getdate())
set @dtDateFirst = DateAdd(day,- @CurrentDay + 1, getdate())
Set @dtDateEnd = DateAdd(Day,-1,@dtDateFirst)
Set @strMyDateEnd = Convert(nvarchar(8), @dtDateEnd,112)
Set @MyQuery_01 =
'
SELECT
Job_Posting.Co_No As Co_No
, Job_Posting.Div_No As Div_No
, Case
When IsNull(Job_Posting.sJob_No,0) = 0 Then RTrim(Job_Posting.Job_No)
Else RTrim(Job_Posting.Job_No) + ''-'' + RTrim(Job_Posting.sJob_No)
End
As Job_No
, RTrim(Job_Posting.Job_No) As Main_Job_No
, Job_Posting.sJob_No As Sub_Job_No
, Sum(Job_Posting.Billings) As Billings
, Sum(Job_Posting.Costs) As Costs
, Sum(Job_Posting.Hours) As Hours
FROM
OPENQUERY
(
ALPHA,
''
SELECT
blah, blah blah,
'
Set @MyQuery_02 =
'
Where
(Job_Posting.Transaction_Date<= ' Set @MyQuery_03 = ')
blah, blah blah,
'
Execute
(
@MyQuery_01
+ @MyQuery_02
+ @strMyDateEnd
+ @MyQuery_03
)
October 20, 2009 at 6:01 am
A View should be
Create View JobPostingView
as
SELECT Job_Posting.Transaction_Date,
Job_Posting.Co_No As Co_No
, Job_Posting.Div_No As Div_No
, Case
When IsNull(Job_Posting.sJob_No,0) = 0 Then RTrim(Job_Posting.Job_No)
Else RTrim(Job_Posting.Job_No) + ''-'' + RTrim(Job_Posting.sJob_No)
End
As Job_No
, RTrim(Job_Posting.Job_No) As Main_Job_No
, Job_Posting.sJob_No As Sub_Job_No
, Sum(Job_Posting.Billings) As Billings
, Sum(Job_Posting.Costs) As Costs
, Sum(Job_Posting.Hours) As Hours
FROM
OPENQUERY
(
ALPHA,......
Then you would...
Select * from JobPostingView
where Transaction_Date between blah and blah
Does that help ?
October 20, 2009 at 7:11 am
Dave's right. What you are doing is usually done in a stored procedure. You can direct output from the stored procedure to a table using INSERT INTO if there are other steps that will process the resulting data. Also, it appears that all you are doing is testing a date value in your where clause. If that's the case, you could write an inline table-valued function that could serve as a source of rows just like a view, and which would accept a parameter to be passed to the query. See below.
CREATE FUNCTION dbo.itvfRecentDBs
(
@startDate datetime
)
RETURNS TABLE
AS
/* test
select *
from dbo.itvfRecentDBs('1/1/2009')
order by create_date DESC
*/
RETURN
(
select * from sys.databases
where create_date >= @startDate
)
GO
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 7:20 am
Be careful of the functions in your queries as they might cause performance issues.
October 20, 2009 at 7:59 am
Steve is right, sorry I forgot to mention that.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 8:03 am
Scalar udf's yes, inline tables udf are fine....
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
October 20, 2009 at 8:44 am
Yes, there is a world of difference between inline table-valued functions and multi-statement table-valued functions. I believe that stored procedures execute faster than ITVFs, but dang if the ITVFs aren't convenient.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 9:24 am
Bob Hovious 24601 (10/20/2009)
I believe that stored procedures execute faster than ITVFs, but dang if the ITVFs aren't convenient.
Hmm.. Dont see how that is true, as they effectively treated as views :ermm:
But very convenient. Theres nothing like a ITVF , using CTE's to compartmentalize multiple business logics processes. Easy to use, understand and performs well , the holy trinity of SQL 😀
October 20, 2009 at 10:11 am
It seems to me that this came up in another thread this spring, but I can't recall now whether the functions being compared to their stored proc counterparts were ITVFs or multiline functions. I'll try to look back and see if I can find it tonight.
I understand and agree with your logic that, since the optimizer treats the ITVF like a view, it shouldn't negatively impact performance.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 28, 2009 at 7:26 am
Well guys,
I have to say thank you. It took a bit but I can put the funcation (i.e. GetDate()) into the View directly. Again, thank you.
However, most of the rest was well over my head and I believe will take some reading on my part to get there.
Thank you again,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply