How to run an insert without returning anything

I need to run a select statement in a sproc and at the end insert into a
history table without having the insert return anything to the sproc is is
embedded in. How do I do this? Thank you.If that is all the proc does, you can say:
INSERT INTO HistoryTable EXEC myProcedure;
"JT" <xtf@.microsoft.com> wrote in message
news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>I need to run a select statement in a sproc and at the end insert into a
>history table without having the insert return anything to the sproc is is
>embedded in. How do I do this? Thank you.
>|||Problem is that I do a select first and then insert into history and I am
getting the reults of my insert not my select. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u9cLMC2VGHA.2444@.TK2MSFTNGP14.phx.gbl...
> If that is all the proc does, you can say:
> INSERT INTO HistoryTable EXEC myProcedure;
>
>
> "JT" <xtf@.microsoft.com> wrote in message
> news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
I don't know what all of this means. Could you provide some real code, a
simple repro, and explain in detail what you want to really happen? All
these word problems are not very easy to follow.|||JT wrote:
> I need to run a select statement in a sproc and at the end insert into a
> history table without having the insert return anything to the sproc is is
> embedded in. How do I do this? Thank you.
CREATE PROC usp_insert
AS
SET NOCOUNT ON ;
INSERT INTO tbl (col1, col2, ...)
SELECT col1, col2, ...
FROM ... ;
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||JT (xtf@.microsoft.com) writes:
> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
If this is a quiz, my guess is that your table has a trigger with a
SELECT statement in it.
If it not a quiz, please be more detailed...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to Return SqlDataReader and return value (page count) from SPROC

This is my function, it returns SQLDataReader to DATALIST control. Howto return page number with the SQLDataReader set ? sql server 2005,asp.net 2.0

Function get_all_events() As SqlDataReader
Dim myConnection As NewSqlConnection(ConfigurationManager.AppSettings("........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterState As New SqlParameter("@.State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@.pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Variable Pages is global integer.

This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

please any help ? finally following code works without error, but still not returning any return value (pagecount) along with the list of records.

Dim MyReader As SqlDataReader
Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("Dx918Aveb8ax81"))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterregion As New SqlParameter("@.Region", SqlDbType.VarChar, 5)
parameterregion.Value = Request.Params("reg")
myCommand.Parameters.Add(parameterregion)

Dim parameterPagesize As New SqlParameter("@.pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 10
myCommand.Parameters.Add(parameterPagesize)

Dim parameterPagenum As New SqlParameter("@.pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)

Dim parameterPageCount As New SqlParameter("@.pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)

myConnection.Open()
MyReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
pages = myCommand.Parameters("@.pagecount").Value
DataList1.DataSource = MyReader
DataList1.DataBind()
myCommand.Dispose()
myConnection.Dispose()
Response.Write(pages)

|||Output parameters and return values are not available until after aDataReader is closed (which you should add after your DataBind()).|||thank you. I will try that today!!

How to return a varchar from sproc on remote Linked Server?

The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.

DECLARE@.OutParamVARCHAR(5)

EXEC('{Call getnextpin(1)}')ATALTTEST

GetNextPin takes a single input parameter.

I need to modify the above code snippet to return the next PIN number into @.OutParam.

Any ideas?

Any help appreciated.

Regards,

Paul.

Hi wadep,

Try the following code and see if it solves your problem ( i suppose your table has a primary key named "number_index" and your table is ordered well. If not, you need to use cursor which will be a little complicated)

create procedure getnextpin@.indexint,@.numberint outputasselect top 1 @.number=pin_number from your_table_namewhere number_index>@.indexreturn

-------- call proceduredeclare @.indexint, @.numberintset @.index=whatever_you_wantexecute getnextpin @.index,@.number outputprint @.number -- see if your number can be printed here

Hope my suggestion helps