Applying criteria at the start of a series of views

I have a series of views that provide a final result set e.g view 5 is select
... from view4; View 4 is select ... from view3 etc.
That all works fine, but what this does is include all of the data from
whatever tables are involved even though I need a subset. My typical
scenario is that the majority of the data are in the table(s) involved in the
initial view(s)
What I would like to do is pass the necessary parameters to the view(s)
involved so as to limit the amount of data that gets passed along. I'm still
learning the right way to do things in SQL, so I appreciate any insights that
anyone can provide.
TIA,
John"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:098BB47D-453F-4B0A-A2E2-B843B10F30AA@.microsoft.com...
>I have a series of views that provide a final result set e.g view 5 is
>select
> ... from view4; View 4 is select ... from view3 etc.
> That all works fine, but what this does is include all of the data from
> whatever tables are involved even though I need a subset. My typical
> scenario is that the majority of the data are in the table(s) involved in
> the
> initial view(s)
> What I would like to do is pass the necessary parameters to the view(s)
> involved so as to limit the amount of data that gets passed along. I'm
> still
> learning the right way to do things in SQL, so I appreciate any insights
> that
> anyone can provide.
>
SQL Server will pass where-clause predicates down through views to the base
tables if it can. If the column in a view is the result of an expression,
however, this won't work.
David

Applying criteria at the start of a series of views

I have a series of views that provide a final result set e.g view 5 is select
... from view4; View 4 is select ... from view3 etc.
That all works fine, but what this does is include all of the data from
whatever tables are involved even though I need a subset. My typical
scenario is that the majority of the data are in the table(s) involved in the
initial view(s)
What I would like to do is pass the necessary parameters to the view(s)
involved so as to limit the amount of data that gets passed along. I'm still
learning the right way to do things in SQL, so I appreciate any insights that
anyone can provide.
TIA,
John
"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:098BB47D-453F-4B0A-A2E2-B843B10F30AA@.microsoft.com...
>I have a series of views that provide a final result set e.g view 5 is
>select
> ... from view4; View 4 is select ... from view3 etc.
> That all works fine, but what this does is include all of the data from
> whatever tables are involved even though I need a subset. My typical
> scenario is that the majority of the data are in the table(s) involved in
> the
> initial view(s)
> What I would like to do is pass the necessary parameters to the view(s)
> involved so as to limit the amount of data that gets passed along. I'm
> still
> learning the right way to do things in SQL, so I appreciate any insights
> that
> anyone can provide.
>
SQL Server will pass where-clause predicates down through views to the base
tables if it can. If the column in a view is the result of an expression,
however, this won't work.
David

Applying criteria at the start of a series of views

I have a series of views that provide a final result set e.g view 5 is selec
t
... from view4; View 4 is select ... from view3 etc.
That all works fine, but what this does is include all of the data from
whatever tables are involved even though I need a subset. My typical
scenario is that the majority of the data are in the table(s) involved in th
e
initial view(s)
What I would like to do is pass the necessary parameters to the view(s)
involved so as to limit the amount of data that gets passed along. I'm stil
l
learning the right way to do things in SQL, so I appreciate any insights tha
t
anyone can provide.
TIA,
John"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:098BB47D-453F-4B0A-A2E2-B843B10F30AA@.microsoft.com...
>I have a series of views that provide a final result set e.g view 5 is
>select
> ... from view4; View 4 is select ... from view3 etc.
> That all works fine, but what this does is include all of the data from
> whatever tables are involved even though I need a subset. My typical
> scenario is that the majority of the data are in the table(s) involved in
> the
> initial view(s)
> What I would like to do is pass the necessary parameters to the view(s)
> involved so as to limit the amount of data that gets passed along. I'm
> still
> learning the right way to do things in SQL, so I appreciate any insights
> that
> anyone can provide.
>
SQL Server will pass where-clause predicates down through views to the base
tables if it can. If the column in a view is the result of an expression,
however, this won't work.
David

Appending Text to a SQL Text Data Type

I am trying to use a cursor to create a mass Text field with the results fro
m
the selections from a series of VarChar(8000) values. I know I need to use
UpdateText, but it only seems to store the 1st one it reads. Can anyone hel
p?
Here's my text:
Declare @.TriggerText nVarChar(4000)
Declare @.ptrval Binary(16)
Declare @.Offset Int
-- Create temporary table to hold Text field
Create Table #tempTrigger
(TextField Text NULL)
Insert Into #tempTrigger Select ''
-- Get Trigger "basis"
Declare curTriggerBasis Insensitive Cursor For
Select c.Text
From sysObjects o (nolock)
Inner Join sysComments c (nolock)
On o.ID = c.ID
Where o.Name = 'cttx_Customer'
Order By ColID
For Read Only
Open curTriggerBasis
Fetch Next From curTriggerBasis Into @.TriggerText
While @.@.Fetch_Status = 0
Begin
Select @.ptrval = TEXTPTR(TextField),
@.Offset = DataLength(TextField)
From #tempTrigger (nolock)
UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
Fetch Next From curTriggerBasis Into @.TriggerText
End
Close curTriggerBasis
Deallocate curTriggerBasis
Select * From #tempTrigger (nolock)> but it only seems to store the 1st one it reads
How are you determining this? What does 'SELECT DATALENGTH(TextField) FROM
#tempTrigger' return?
Happy Holidays
Dan Guzman
SQL Server MVP
"bobnunny" <u17151@.uwe> wrote in message news:59ac1c8e96b9c@.uwe...
>I am trying to use a cursor to create a mass Text field with the results
>from
> the selections from a series of VarChar(8000) values. I know I need to
> use
> UpdateText, but it only seems to store the 1st one it reads. Can anyone
> help?
> Here's my text:
> Declare @.TriggerText nVarChar(4000)
> Declare @.ptrval Binary(16)
> Declare @.Offset Int
> -- Create temporary table to hold Text field
> Create Table #tempTrigger
> (TextField Text NULL)
> Insert Into #tempTrigger Select ''
> -- Get Trigger "basis"
> Declare curTriggerBasis Insensitive Cursor For
> Select c.Text
> From sysObjects o (nolock)
> Inner Join sysComments c (nolock)
> On o.ID = c.ID
> Where o.Name = 'cttx_Customer'
> Order By ColID
> For Read Only
> Open curTriggerBasis
> Fetch Next From curTriggerBasis Into @.TriggerText
> While @.@.Fetch_Status = 0
> Begin
> Select @.ptrval = TEXTPTR(TextField),
> @.Offset = DataLength(TextField)
> From #tempTrigger (nolock)
> UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
> Fetch Next From curTriggerBasis Into @.TriggerText
> End
> Close curTriggerBasis
> Deallocate curTriggerBasis
> Select * From #tempTrigger (nolock)|||I've put Print statements in there to check this out. It shows Datalength a
s
4000 everytime except the last one. BUT, like an idiot I was checking the
loop so hard, but the Select statement at the end will only return the first
4000. Once I changed that to DataLength, it showed it had it all.
Thanx!
Dan Guzman wrote:
>How are you determining this? What does 'SELECT DATALENGTH(TextField) FRO
M
>#tempTrigger' return?
>
>[quoted text clipped - 36 lines]