"John Walker" wrote:

> Hi,
> I have a stored proc (called spTrucker) SQL query that I need to use in two
> places: in an ASP.NET app (results of the query will be put in a datagrid),
> and in another stored procedure (called spDistinctCodes) where the results
> will be processed using a cursor. 
>
> I'm hoping to not to have to duplicate to query code in order for it to be
> used in these two places, but when the stored proc spTrucker is designed
> using a cursor output parameter, then it is unusable in the ASP.NET app; and
> when I remove the cursor output parameter then it is unusable from the cursor
> stored proc (spDistinctCodes).
>
> I do not want to use a View because the query seems to take to long to
> process because the indexes are not being properly utilized.
>
> Is there a central place where I can put this query?
>
> -------------------------------------------------------
> Here's a sample of the Asp.net code:
>
> strSQL = "EXEC spTrucker @argTruckNo"
> dad = New SqlDataAdapter(strSQL, conn)
> dad.SelectCommand.Parameters.Add("@argTruckNo", Session("TruckerNo"))
> dad.Fill(dset)
> -------------------------------------------------------
> Here's a sample of the spDistinctCode code:
>
> declare @cur cursor
> exec spTrucker @argTruckNo, @cur out
> -------------------------------------------------------
>
> Thanks,
> John Walker
>

"Alejandro Mesa" wrote:

> John,
>
> You do not need a cursor output parameter. You can grab the result of the sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL.
>
> Example:
>
> use northwind
> go
>
> create procedure dbo.p1
> @sd datetime,
> @ed datetime
> as
> set nocount on
>
> select
>     orderid, orderdate, customerid
> from
>     dbo.orders
> where
>     orderdate >= convert(char(8), @sd, 112)
>     and orderdate < convert(char(8), dateadd(day, 1, @ed), 112)
>
> return @@error
> go
>
> create procedure dbo.p2
> @sd datetime,
> @ed datetime
> as
> set nocount on
>
> declare @orderid int
> declare @orderdate varchar(25)
> declare @customerid nchar(5)
>
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
>
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @sd, @ed
>
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
>
> open my_cursor
>
> while 1 = 1
>     begin
>     fetch next from my_cursor into @orderid, @orderdate, @customerid
>
>     if @@error != 0 or @@fetch_status != 0 break
>
>     raiserror('%d    %s    %s', 10, 1, @orderid, @orderdate, @customerid) with nowait
>     end
>
> close my_cursor
> deallocate my_cursor
> go
>
> exec dbo.p2 '19970701', '19970731'
> go
>
> drop procedure p2, p1
> go
>
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
>
> AMB
>
>
>
> "John Walker" wrote:
>
> > Hi,
> > I have a stored proc (called spTrucker) SQL query that I need to use in two
> > places: in an ASP.NET app (results of the query will be put in a datagrid),
> > and in another stored procedure (called spDistinctCodes) where the results
> > will be processed using a cursor. 
> >
> > I'm hoping to not to have to duplicate to query code in order for it to be
> > used in these two places, but when the stored proc spTrucker is designed
> > using a cursor output parameter, then it is unusable in the ASP.NET app; and
> > when I remove the cursor output parameter then it is unusable from the cursor
> > stored proc (spDistinctCodes).
> >
> > I do not want to use a View because the query seems to take to long to
> > process because the indexes are not being properly utilized.
> >
> > Is there a central place where I can put this query?
> >
> > -------------------------------------------------------
> > Here's a sample of the Asp.net code:
> >
> > strSQL = "EXEC spTrucker @argTruckNo"
> > dad = New SqlDataAdapter(strSQL, conn)
> > dad.SelectCommand.Parameters.Add("@argTruckNo", Session("TruckerNo"))
> > dad.Fill(dset)
> > -------------------------------------------------------
> > Here's a sample of the spDistinctCode code:
> >
> > declare @cur cursor
> > exec spTrucker @argTruckNo, @cur out
> > -------------------------------------------------------
> >
> > Thanks,
> > John Walker
> >

"Alejandro Mesa" wrote:

> John,
>
> You do not need a cursor output parameter. You can grab the result of the sp
> "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL.
>
> Example:
>
> use northwind
> go
>
> create procedure dbo.p1
> @sd datetime,
> @ed datetime
> as
> set nocount on
>
> select
>     orderid, orderdate, customerid
> from
>     dbo.orders
> where
>     orderdate >= convert(char(8), @sd, 112)
>     and orderdate < convert(char(8), dateadd(day, 1, @ed), 112)
>
> return @@error
> go
>
> create procedure dbo.p2
> @sd datetime,
> @ed datetime
> as
> set nocount on
>
> declare @orderid int
> declare @orderdate varchar(25)
> declare @customerid nchar(5)
>
> create table #t (orderid int, orderdate datetime, customerid nchar(5))
>
> insert into #t(orderid, orderdate, customerid)
> exec dbo.p1 @sd, @ed
>
> declare my_cursor cursor local fast_forward
> for
> select orderid, orderdate, customerid
> from #t
> order by orderdate
>
> open my_cursor
>
> while 1 = 1
>     begin
>     fetch next from my_cursor into @orderid, @orderdate, @customerid
>
>     if @@error != 0 or @@fetch_status != 0 break
>
>     raiserror('%d    %s    %s', 10, 1, @orderid, @orderdate, @customerid) with nowait
>     end
>
> close my_cursor
> deallocate my_cursor
> go
>
> exec dbo.p2 '19970701', '19970731'
> go
>
> drop procedure p2, p1
> go
>
> How to share data between stored procedures
> http://www.sommarskog.se/share_data.html
>
>
> AMB
>
>
>
> "John Walker" wrote:
>
> > Hi,
> > I have a stored proc (called spTrucker) SQL query that I need to use in two
> > places: in an ASP.NET app (results of the query will be put in a datagrid),
> > and in another stored procedure (called spDistinctCodes) where the results
> > will be processed using a cursor. 
> >
> > I'm hoping to not to have to duplicate to query code in order for it to be
> > used in these two places, but when the stored proc spTrucker is designed
> > using a cursor output parameter, then it is unusable in the ASP.NET app; and
> > when I remove the cursor output parameter then it is unusable from the cursor
> > stored proc (spDistinctCodes).
> >
> > I do not want to use a View because the query seems to take to long to
> > process because the indexes are not being properly utilized.
> >
> > Is there a central place where I can put this query?
> >
> > -------------------------------------------------------
> > Here's a sample of the Asp.net code:
> >
> > strSQL = "EXEC spTrucker @argTruckNo"
> > dad = New SqlDataAdapter(strSQL, conn)
> > dad.SelectCommand.Parameters.Add("@argTruckNo", Session("TruckerNo"))
> > dad.Fill(dset)
> > -------------------------------------------------------
> > Here's a sample of the spDistinctCode code:
> >
> > declare @cur cursor
> > exec spTrucker @argTruckNo, @cur out
> > -------------------------------------------------------
> >
> > Thanks,
> > John Walker
> >

"John Walker" wrote:

> Alejandro,
> Sorry, one more question:  In your example, would there be any way to return
> only a subset of the fields in the stored proc query.  For example, if you
> wanted a temporary table with only the orderid field.  Like this:
>
> create table #t (orderid int)
>
> insert into #t(orderid)
> exec dbo.p1 @sd, @ed
>
> I'll need it like this because my store proc "spTrucker" has many, many
> fields, and I only need about 4 or 5 of them in the cursor.
>
> Thanks again,
> John
>
> "Alejandro Mesa" wrote:
>
> > John,
> >
> > You do not need a cursor output parameter. You can grab the result of the sp
> > "spTrucker" in sp "spDistinctCodes". See "insert into ... exec ..." in BOL.
> >
> > Example:
> >
> > use northwind
> > go
> >
> > create procedure dbo.p1
> > @sd datetime,
> > @ed datetime
> > as
> > set nocount on
> >
> > select
> >     orderid, orderdate, customerid
> > from
> >     dbo.orders
> > where
> >     orderdate >= convert(char(8), @sd, 112)
> >     and orderdate < convert(char(8), dateadd(day, 1, @ed), 112)
> >
> > return @@error
> > go
> >
> > create procedure dbo.p2
> > @sd datetime,
> > @ed datetime
> > as
> > set nocount on
> >
> > declare @orderid int
> > declare @orderdate varchar(25)
> > declare @customerid nchar(5)
> >
> > create table #t (orderid int, orderdate datetime, customerid nchar(5))
> >
> > insert into #t(orderid, orderdate, customerid)
> > exec dbo.p1 @sd, @ed
> >
> > declare my_cursor cursor local fast_forward
> > for
> > select orderid, orderdate, customerid
> > from #t
> > order by orderdate
> >
> > open my_cursor
> >
> > while 1 = 1
> >     begin
> >     fetch next from my_cursor into @orderid, @orderdate, @customerid
> >
> >     if @@error != 0 or @@fetch_status != 0 break
> >
> >     raiserror('%d    %s    %s', 10, 1, @orderid, @orderdate, @customerid) with nowait
> >     end
> >
> > close my_cursor
> > deallocate my_cursor
> > go
> >
> > exec dbo.p2 '19970701', '19970731'
> > go
> >
> > drop procedure p2, p1
> > go
> >
> > How to share data between stored procedures
> > http://www.sommarskog.se/share_data.html
> >
> >
> > AMB
> >
> >
> >
> > "John Walker" wrote:
> >
> > > Hi,
> > > I have a stored proc (called spTrucker) SQL query that I need to use in two
> > > places: in an ASP.NET app (results of the query will be put in a datagrid),
> > > and in another stored procedure (called spDistinctCodes) where the results
> > > will be processed using a cursor. 
> > >
> > > I'm hoping to not to have to duplicate to query code in order for it to be
> > > used in these two places, but when the stored proc spTrucker is designed
> > > using a cursor output parameter, then it is unusable in the ASP.NET app; and
> > > when I remove the cursor output parameter then it is unusable from the cursor
> > > stored proc (spDistinctCodes).
> > >
> > > I do not want to use a View because the query seems to take to long to
> > > process because the indexes are not being properly utilized.
> > >
> > > Is there a central place where I can put this query?
> > >
> > > -------------------------------------------------------
> > > Here's a sample of the Asp.net code:
> > >
> > > strSQL = "EXEC spTrucker @argTruckNo"
> > > dad = New SqlDataAdapter(strSQL, conn)
> > > dad.SelectCommand.Parameters.Add("@argTruckNo", Session("TruckerNo"))
> > > dad.Fill(dset)
> > > -------------------------------------------------------
> > > Here's a sample of the spDistinctCode code:
> > >
> > > declare @cur cursor
> > > exec spTrucker @argTruckNo, @cur out
> > > -------------------------------------------------------
> > >
> > > Thanks,
> > > John Walker
> > >