Transact SQL :: Stored Procedure To Return Results

May 27, 2015

How can I return results from this SP?

Alter
Procedure  sp_Blocking
as
      SET NOCOUNT
ON
truncate
table blocked

[Code] ....

View 4 Replies


ADVERTISEMENT

Return Results Set From Stored Procedure

Oct 8, 2004

Two questions - one slightly off topic:

1) How do I write a stored procedure in MS SQL so that it returns a results set?

2) How do I get this into ASP.NET so that I can put the values into controls?

Jags

View 1 Replies View Related

Return Results Of Stored Procedure

Sep 30, 2006

I need to return the results of a stored procedure into a temporary table. Something like this:

Select * into #temp from exec (the stored procedure). It appears that I can not do this.

The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)

insert into (...) exec (the stored procedure.

Does anyone have any ideas how I could do this.



View 4 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

Can't Get Sqldatasource To Return Results From A Stored Procedure

Aug 1, 2006

I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================

ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here

@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...

AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?

AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...

RETURN @WinnerID
END
 It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page

Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
 As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
 
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content> 

View 3 Replies View Related

T-SQL (SS2K8) :: Stored Procedure And SELECT Statement Return Different Results

Dec 4, 2014

I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:

USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

When I do this in the same SSMS window:

exec mobile.GetAllMobileDeviceUsers

SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION

[Code] ....

I get two result sets. The first is 422 rows; the second is 467 rows. Why?

View 4 Replies View Related

Transact SQL :: Stored Procedure Return Values

Sep 23, 2015

If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer?  Is that simply the success flag?

View 5 Replies View Related

Transact SQL :: Exporting Stored Procedure Results To Excel

Jun 30, 2015

Goal: To run a stored procedure and export its results to Excel.
Challenge: The stored procedure produces multiple result sets. We want to programmatically export each result set into just 1 Excel file but into different Worksheets. Ex: result set 1 into Worksheet 1, result set 2 into Worksheet 2 and so on. Tools like

BCP utility and OPENROWSET are not enabled for use on the server. So those are out of the league for this one.After I run "EXEC [sp_select_ view_ columns_ from_pp]" -- Within the Results tab of Microsoft SQL Server Management Studio, I see:

viewName columnName columnDataType columnLength column
test test_id varchar 8 0
viewName columnName columnDataType columnLength column
test1 test1_id varchar 8 0
viewName columnName columnDataType columnLength column
test2 test2_id varchar 8 0

As you can see from above, that stored procedure sp_select_view_columns_from_pp is producing multiple result sets within the same Results tab in Microsoft SQL Server Management Studio. We want to make it so it exports to Excel into its own worksheets.

Code:

ALTER PROCEDURE [dbo].[sp_select_view_columns_from_pp]
AS
DECLARE @object_id INT;
DECLARE cur CURSOR FOR SELECT object_id

[code]....

View 7 Replies View Related

Transact SQL :: Stored Procedure To Return Multiple Records

Oct 21, 2015

I am looking out for sample  stored procedures returning multiple records

Example: GetOrderDetailsByOrderId

The above stored procedure should take orderId as parameter and should return the the order details along mutiple line item details.

View 4 Replies View Related

Transact SQL :: Insert Or Update Stored Procedure Return ID

Nov 1, 2015

I have the following stored procedure, to insert or update a record and return the id field, however the procedure returns two results sets, one empty if it's a new record - is there a way to supress the empty results set?

ALTER PROCEDURE [dbo].[AddNode]
@Name VARCHAR(15),
@Thumbprint VARCHAR(40),
@new_identity [uniqueidentifier] = NULL OUTPUT
AS
BEGIN
UPDATE dbo.NODES

[Code] ....

View 7 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

Stored Proc Failing To Return Results

Aug 9, 2000

I have a search stored proc which fails to return results when called by more than one user.

I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.

This seems to be consistent.

We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.

The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.

Anyone know of any problems or can suggest anything to try?

View 3 Replies View Related

Can We Use Stored Procedurem To Return Several Results (accessing SQL Server)

Jun 5, 2006

Can I use sqlDatareader with a stored procedure to return more than one reultset that I ll be accessing through MyDatareader.MoveNext
Any Little example on both the stored proc and the .Net side
Thanks

View 4 Replies View Related

Transact SQL :: Why Store Procedure Not Return Any Value / Result After Using Exec

Jul 22, 2015

I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??

USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]

[Code] ....

View 6 Replies View Related

How To Stored A Stored Procedure's Results Into A Table

Jul 23, 2005

Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Need To Know How To Get Stored Procedure Results Into VB

Jun 18, 2004

datagrid. Sorry, I'm new to it. For the few times I've done datagrids in the past, I've built
my own selects and filled the grid. This time I need to use someone elses stored procedure.
I simply don't know how to get the results back into my VB datagrid. Can someone point
me in the right direction? Thanks.

View 6 Replies View Related

Results Of A Stored Procedure

Feb 1, 2007

How do I filter the results of a stored procedure?

View 2 Replies View Related

T-Sql Help - BCP Results Of Stored Procedure

Feb 25, 2008

Hi

I am trying to execute a stored procedure (which returns multiple tables) and use these results to populate an Excel file. I am totally lost on how to capture the results and use it. Any help will be appreciated.

Thanks

View 8 Replies View Related

Using The Return Value From A Stored Procedure In VB

Nov 20, 2007

 Hi guys I know this is a really common question, and I have read loads of replies on it but everything I try does not work.  I have written a small stored procedure in SQL server to upload images to a table and return the new ID using scope_identity.  I have tested it and it works fine.  here it is:*******    @siteID numeric(18,0),    @imgNum numeric(18,0),    @title NVarchar(50),    @MIMEtype nchar(10),    @imageData varbinary(max)ASBEGINSET NOCOUNT ONdeclare @imageID intINSERT INTO [site_images] ([img_siteID], [img_num], [img_title], [img_MIME], [Img_Data]) VALUES (@siteID, @imgNum, @Title, @MIMEType, @ImageData) SET @imageID = SCOPE_IDENTITY()RETURN @imageIDSET NOCOUNT OFF************If I run this in management studio express it runs fine and returns the ID under 'return value'. The problem I have is trying to actually call that return value in VB.  If I try using these lines:Dim returnParam As SqlParameterreturnParam = New SqlParameter("@imageID", SqlDbType.UniqueIdentifier)returnParam.Direction = ParameterDirection.OutputcmdTest.Parameters.Add(returnParam)withcnBKTest.Open()cmdTest.ExecuteNonQuery() imageIDparam = returnParam.value.toStringcnBKTest.Close()  I get the error "procedure has too many arguments specified"And if I try to access the return value like this: imageIDparam = cmdTest.Parameters("@return_value").ValueI get the error "@return_value is not contained by this sqlparametercollection"  What am I doing wrong?  Any help would be greatly appreciated. Robsa         

View 3 Replies View Related

No Return Value From This Stored Procedure

Nov 28, 2007

I have written this stored procedure but I get no return value (neither 0 nor 1). What I hope is when the transaction successful, return value 1. If fails, return value 0.1 set @TransactionOk = 0
2
3 BEGIN TRAN
4
5 UPDATE WhiteList_IMEI SET WhiteList_IMEI_Used = 1, Whitelist_IMEI_UsedDate = getdate()
6 WHERE WhiteList_IMEI_Code = @IMEICode_New
7
8 IF @@ERROR <> 0
9 BEGIN
10 ROLLBACK TRAN
11
12 PRINT ('Error. Contact Software Engineer.')
13 RETURN
14 END
15
16 COMMIT TRAN
17 set @TransactionOk = 1
  

View 6 Replies View Related

Return Value From Stored Procedure

May 16, 2008

Hi,
I have been trying to this this for quite a while with no joy can someone please tell me the error of my ways.  I am trying to add a new record by stored procedure, this I can do, but my problem lies with the returnvalue part of the procedure.  I cannot get it to work. When I debug it tells me that the "Specified cast is not valid" see C# code as i comment the line where it errors.  I enclose a sample stored procedure and its c# code.  Please can someone tell me where I am going wrong? as this is annoying me alot
SQL:create procedure SPUAddVehicleInsert
@VehicleDetailsRegistrationNumber varchar(50),
@VehicleDetailsMake varchar(50),
@VehicleDetailsModel varchar(50),
@NID bigint =null
as
insert into tblvehicledetails
(
VehicleDetailsRegistrationNumber,
VehicleDetailsMake,
VehicleDetailsModel,

)
values
(
@VehicleDetailsRegistrationNumber,
@VehicleDetailsMake,
@VehicleDetailsModel,

);
select @NID = scope_identity();c# code on sqldatasource:  protected void dsAddVehicle_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
Int64 VID = (Int64)e.Command.Parameters["NID"].Value; //errors with specified cast is invalid

Response.Redirect("details.aspx?VID=" + VID.ToString());

}
protected void dsAddVehicle_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
SqlParameter p = new SqlParameter("NID", SqlDbType.BigInt);
p.Direction= ParameterDirection.ReturnValue;
e.Command.Parameters.Add(p);

sql datasource:<asp:SqlDataSource ID="dsAddVehicle" runat="server" ConnectionString="<%$ ConnectionStrings:National %>"
InsertCommand="SPUAddVehicleInsert" InsertCommandType="StoredProcedure" SelectCommand="SPUAddVehicleSelect"
SelectCommandType="StoredProcedure" OnInserted="dsAddVehicle_Inserted" OnInserting="dsAddVehicle_Inserting">
<InsertParameters>
<asp:Parameter Name="VehicleDetailsRegistrationNumber" Type="String" />
<asp:Parameter Name="VehicleDetailsMake" Type="String" />
<asp:Parameter Name="VehicleDetailsModel" Type="String" />
</InsertParameters>
</asp:SqlDataSource> 

View 4 Replies View Related

Stored Procedure Return Value

Jun 20, 2005

I have a stored procedure that returns an integer value, declared as:Public Function MyProc(..) As Int32   .   .   Return <integer>End FunctionI return an integer value, and can do this and get the value returned from the method, as such:declare @rc intexec @rc = dbo.MyProc <params>select @rcThis returns the value; how do I return the value to code and get the value; I've been debugging and that is my problem, I can't get the value to return.Thanks a lot.

View 2 Replies View Related

Return Value In Stored Procedure

Nov 20, 2005

Hello all of members, I have written a Stored Procedure.that creates a new account and then returns a value witch displays a result to me.if result is 1 "Username already exists" or 2 "E-Mail already exists".I did it with "Return" instruction.But, I don't know how can I get the returned value in ASP.NET(VB.NET)? Please help me. Thanks in advance

View 3 Replies View Related

Getting A Return Value From A Stored Procedure

Nov 25, 2005

Hi all,Is there anyway to get a returned value from a called Stored Procedure from within a piece of SQL? For example, I have the following code...DECLARE @testval AS INTSET @testval = EXEC u_checknew_dwi_limits '163'IF (@testval = 0)BEGIN     PRINT '0 Returned'ENDELSEBEGIN     PRINT '1 Returned'END...which
as you can see calls a SP called 'u_checknew_dwi_limits'. This SP
(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want to
assign that value to the '@testval' variable (as you can see in my
code) - but Query Analyser is throwing an error at me. Is this the
correct way to do this?ThanksTryst

View 2 Replies View Related

How To Get The Return Value Of A Stored Procedure

Apr 28, 2006

I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end: Return Scope_Identity()
I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string.
I have intReturn defined as an integer
I've tried :Dim retValParam As OleDbParameter = cmd.Parameters.Add("@RETURN_VALUE", OleDbType.Integer)retValParam.Direction = ParameterDirection.ReturnValueintReturn=cmd.Parameters("@RETURN_VALUE").Value
whenever I add this section - I get an error that there are too many arguments for the sProc.
I've tried:intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors.
What is the best way to do this in the code part (VB.Net)?
Thanks ahead of time

View 2 Replies View Related

Stored Procedure Return Value

May 16, 2006

I don't know whey this code does not return the values when I run it in sql server 2005 manager.set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[usp_usr_Add]

@FName nvarchar(30),
@LName nvarchar(30),
@UserName nvarchar(20),
@Password nvarchar(20),
@Email nvarchar(50),
@Country nvarchar(2),
@AIM nvarchar(10)
AS
SET NOCOUNT ON
BEGIN

DECLARE @usrID int
DECLARE @usrEmail int

SELECT @usrID = NULL
SELECT @usrEmail = NULL


SELECT @usrID = usrID FROM usr WHERE usrName = @UserName

IF (@usrID IS NOT NULL )
BEGIN
RETURN 1
END

SELECT @usrEmail = usrID FROM usr WHERE usrEmail = @Email

IF (@usrEmail IS NOT NULL)
BEGIN
RETURN 2
END



INSERT INTO usr (usrFName, usrLName, usrName, usrPassword, usrEmail, usrCountry, usrAIM, usrJoinDt)

VALUES (@FName, @LName, @UserName, @Password, @Email, @Country, @AIM, GetDate())
RETURN 0

END



 When i run this code i executes fine except when the two conditions become true they do not return thier values, nor does it return 0 when it inserts a row.

View 5 Replies View Related

Stored Procedure Return Value.

Oct 17, 2000

Hi

I have the following stored procedure which when exectuted within the Query Analyzer adds a record and returns the @SeqRunNo value. However when I call
it in the from ado the record is added but the value is empty.

I enter the parameter as:

.Parameters.Append .CreateParameter("@SeqRunNo", adVarChar, adParamOutput, 4)

and try and access it as:

SeqRunNo = oCommand.Parameters("@SeqRunNo").Value

Any ideas?

Thanks.


john








CREATE PROCEDURE up_mix_Set_Seq_Run_No
@IID int,
@TrackingID int,
@ADP_Code varchar(6),
@ClientID varchar(20),
@SeqRunNo varchar(4) OUTPUT
AS

DECLARE @Max_Seq_No int,
@Seq_No varchar(4),
@LastUpdate datetime

SELECT @Seq_No = QTR_SEQ_RUN_NO FROM Transmission_Quarter
WHERE IID = @IID
AND Tracking_ID = @TrackingID
AND ADP_Code = @ADP_Code

IF ISNULL(@Seq_No,0) = 0
BEGIN

SET @LastUpdate = GETDATE()

SELECT @Max_Seq_No = CAST(MAX(QTR_SEQ_RUN_NO) AS int)
FROM Transmission_Quarter
WHERE IID = @IID
AND ADP_Code = @ADP_Code

IF ISNULL(@Max_Seq_No,0) = 0
BEGIN
SET @SeqRunNo = '0001'
END
ELSE
BEGIN
IF @Max_Seq_No = 9999
BEGIN
SELECT @SeqRunNo = '0001'
END
ELSE
BEGIN
SELECT @SeqRunNo = REPLICATE('0', 4 - DATALENGTH(CAST(@Max_Seq_No AS VARCHAR))) + CAST((@Max_Seq_No + 1) AS VARCHAR)
END
END

INSERT INTO Transmission_Quarter
(IID, Tracking_ID, QTR_SEQ_RUN_NO, ADP_Code, Last_Updated_By, Last_Updated_Date)
VALUES
(@IID, @TrackingID, @SeqRunNo, @ADP_Code, @ClientID, @LastUpdate)

END
ELSE
BEGIN
SELECT @SeqRunNo = @Seq_No
END

GO

View 2 Replies View Related

Return A Value From Stored Procedure

Mar 27, 2002

How do I get then return value from an insert stored procedure?

for example:


CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS

INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)

SELECT @Iden=@@Identity
GO


Thanks

View 1 Replies View Related

Return A Value From Stored Procedure

Mar 27, 2002

How do I get then return value from an insert stored procedure?

for example:


CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS

INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)

SELECT @Iden=@@Identity
GO


Thanks

View 2 Replies View Related

Return Value In A Stored Procedure

Jul 20, 2005

Hello Newsgroup !My Tools are:Windows 2000, VBA(Access 2000) and MS SQL Server 7.0I wrote in an *.adp project (Access 2000) a Stored Procedure "xyz"with parameters a,bIn my VBA Code i wrote:Dim par As New ADODB.ParameterCmd.CommandType = adCmdStoredProcCmd.CommandText = "[prcSucheUNRWIAEStichtag]"Set par = Cmd.CreateParameter("@a", adInteger)Cmd.Parameters.Append parSet par = Cmd.CreateParameter("@b", adVarChar, adParamInput, 5)Cmd.Parameters.Append par........Cmd.Parameters(0) = aCmd.Parameters(1) = bSet rsTemp = Cmd.ExecuteNow my Problem is the following:there is an error in the stored procedure and i want to use something likethis:if @idontknow = '000000000'Beginreturn(1) -- Something <>0endHow can i use this return value in my VBA code ? Maybe i should ask thisquestion in an other Newsgroup. Pleaselet me know in which oneGreetingsFrank

View 2 Replies View Related

Get Return Value From Stored Procedure

Apr 22, 2008

In one of my SSIS jobs I have an 'Execute SQL Task' which is calling a stored procedure. This SP returns 0 or 1, if the SP returns 1 the package needs to stop executing. Is there a way to get the value that is returned from the SP and either continue or stop the job from continuing to run?

so if value is 0 it goes to the next step, if its 1 it stops running

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved