Return Resultset As Output Parameter To VB Application
Nov 27, 2014
I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.
I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:
Code:
while (@I <= @count)
begin
BEGIN TRY
-- delete all previous rows inserted in @customerRow for previous counts @I
delete from @customerRow
-- this is inserting the current row that we want to save in database
insert into @customerRow
[Code] .....
This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:
Code:
begin catch
IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
[Code] ....
Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.
View 3 Replies
ADVERTISEMENT
Mar 13, 2008
I have the below procedure with an output parameter '@rowcount'. Now My issue is how to access the value of '@rowcount' in my ASP.Net application using C#. I have also pasted my C# code which I am using .
create procedure mypro1(@id varchar(10),@rowcount int output)
as
select * from canid where id like @id
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
select @rowcount=@@rowcount
--------------------------------------------------
C# code to retrieve the output parameter valuecmd = new SqlCommand("mypro1", cn);
cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@id", TextBox1.Text).Direction=ParameterDirection.Input;
Parameter cout = new Parameter("@rowcount",SqlDbType.Int);cout.Direction = ParameterDirection.Output;
cmd.Parameters.Add(cout);
can some body assist me in the above code whether its right or what needs to be added to get the output parameter value.
View 3 Replies
View Related
Nov 7, 2007
In my SP I have an output parameter,
the SP work good,
but in the command parameter I alwase get null value/
here is my code:
and in the profiler I see that the query work and return the value, here the query that I copy from the profiler:
thanks!
View 3 Replies
View Related
May 2, 2005
I'm having a terrible time getting a return value from my stored procedure. The sp sets the value and returns it correctly in the query analyzer, but I can't get the calling code - using VB in VS2003 - to read the value. I've tried putting it into an integer, and using a Return, and also putting it into a string, and using an output parameter. Either way: nada. What am I doing wrong????
Here's the sp:
CREATE PROCEDURE sp_HOUSRoomAudits @Result VarChar(1) OUTPUT, @Dorm VarChar(12), @Room VarChar(5), @StuID VarChar(14) AS
DECLARE @Capacity IntDECLARE @Assigned Int--DECLARE @Result VarChar(1)DECLARE @BD BIT
SET @BD=(SELECT Boarding FROM HOUS_StudentMaster WHERE StudentID=@StuID)
SET @Capacity=(SELECT [Student Capacity] FROM HOUS_Rooms WHERE Bldg=@Dorm AND [Room #]=@Room)
SET @Assigned=(SELECT COUNT(PSID) FROM HOUS_StudentMaster a JOIN HOUS_Dorms b ON a.Dormatory=b.Dormitory WHERE b.Bldg=@Dorm AND a.Status='Active' AND a.Room=@Room)
IF (@BD=1 AND @Room='N/A') OR (@BD=0 AND @Room<>'N/A')
SET @Result='3'ELSE IF @Assigned<@Capacity SET @Result='1' ELSE SET @Result='2'
print @Result--RETURN @ResultGO
Here's the calling code; I'm leaving out all the dim's etc down to the output parameter:
Dim Parm4 As SqlParameter = scmdReturn.Parameters.Add("@Result", SqlDbType.VarChar)
Parm4.Direction = ParameterDirection.Output
Parm4.Value = "0"
Try
scmdReturn.ExecuteNonQuery()
strOut = Parm4.Value
Catch ex As Exception
Me.lblProblem.Text = "Failed to execute command, err = " & ex.Message
Me.lblProblem.Visible = True
Finally
scnnReturn.Close()
End Try
Hoping you can help, and thanks!
Ka
View 11 Replies
View Related
Oct 7, 2015
I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.
Here is my code:
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)
[Code] ....
View 4 Replies
View Related
Feb 6, 2008
I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable?
Below is the source code that I have:1 ' Insert question_text into Question table
2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 Dim QuestionID As Integer
5
6 ' Set connection to DB
7 cmd.Connection = conn
8
9 cmd.CommandText = "usp_QuestionResponse_ins"
10 cmd.CommandType = Data.CommandType.StoredProcedure
11
12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output
13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID))
14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID))
15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn))
16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp))
17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn))
18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder))
19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3))
20 QuestionID = sp1.Value
21 cmd.Connection.Open()
22 cmd.ExecuteNonQuery()
23
24 conn.Close()
25 cmd.Dispose()
26
Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins
2 (
3 @question_id int = null output,
4 @assessment_id int = null,
5 @domain_id int = null,
6 @question_text_en nvarchar(1000) = null,
7 @question_text_sp nvarchar(1000) = null,
8 @category_en nvarchar(500) = null,
9 @display_order smallint = null,
10 @question_template_id int = null
11 )
12 As
13 BEGIN
14 DECLARE @Err Int
15
16 DECLARE @QuestionID INT
17 DECLARE @ReturnValue INT
18 SET @ReturnValue = 0
19
20 DECLARE @CategoryId int
21 SELECT @CategoryId = NULL
22
23 -- Check to see if an answer has already been inserted (i.e. revisiting)
24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0)
25 IF ( @CategoryId IS NOT NULL )
26 BEGIN
27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0
28 SET @QuestionID = @ReturnValue
29 END
30
31 IF ( @QuestionID IS NOT NULL )
32 BEGIN
33 DECLARE @question_locale_id int
34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0
35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0
36
37 END
38
39 RETURN @QuestionID
40 End
What am I doing wrong? How do I properly capture the QuestionID from the SP? Running the SP from command line works fine.
Thanks
View 2 Replies
View Related
Jul 20, 2005
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View 2 Replies
View Related
Jul 20, 2004
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO
If I run the query in Query Analyzer directly, I can display @UserName:
DECLARE @UserName as nvarchar(100)
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
Select @UserName
But how can I display @UserName if I call stored procedure:
exec UserLogin 'email', 'password', ''
I believed it return int, right?
Thanks in advance for help.
View 2 Replies
View Related
Mar 16, 2004
Hello all,
I want to be able to be able to return a resultset from a Stored Procedure.
Something like :
CREATE PROCEDURE LSNOnAJob
@MyJobNo AS INT,@MyLsn VarChar(10) OUTPUT
AS
SELECT @MyLsn = dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE (dbo.TJob.IJobN = @MyJobNo)
GO
I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.
I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.
View 3 Replies
View Related
Jul 7, 2006
Hi all,
I need to return only the top row of a ResultSet that is generated from my Query but can't seem to find anything in SQL Server 3.0 Mobile that allows me to do that. I know in SQL Server (Desktop) I could use the TOP keyword. The query I have is as follows, and just need the top row. Now the returned resultset will, in time, be massive, and there is just no way I can afford to return this amount of data in my application only to take to the top/first row. The query I have is as follows...
SELECT *
FROM tbl_NSP_AnswerSet
WHERE (DateCompleted IS NOT NULL)
ORDER BY DateCompleted DESC
(I want the last record that was inserted into the database)
Thanks
View 9 Replies
View Related
Feb 7, 2008
I'm learning ASP.net, transitioning from ColdFusion. So far so go... but there is 1 thing that I can't seem to find a simple answer to. How can I do a simple output of a query resultset to the screen or to a label?
In CF it would be simple like this:
<cfquery name="getSomeData" datasource="somedatasource">SELECT somestuffFROM sometableWHERE something = something</cfquery>
<cfoutput>#getsomedata.somestuff#</cfoutput>
FYI: using the queryname.column returns only the first row in the CF query result
I have seen examples with:
label.text = somevarname
which doesn't work for the query... but what about the query result?
thanks
View 2 Replies
View Related
Apr 19, 2006
I can not get a multiple row resultset to display or even get sent to my client application running on coldfusion.
What is the problem with the code?
How do i display and return a resultset to my coldfusion client application?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.nTransaction
(@pAccountNo varchar(30), @N int, @ntransCursor CURSOR VARYING OUTPUT, @nValue varchar(4000) OUTPUT)
AS
set @N = 0
SET ROWCOUNT @N
SET @ntransCursor = CURSOR FOR
-- FORWARD_ONLY STATIC
SELECT CONVERT(varchar,Eh.EntryID), Eh.EntryReference,E.AccountNo, E.Narrative, E.Amount, Eh.EntryDate
FROM entryheaders as Eh cross join entrys as E
WHERE Eh.EntrySerial = E.EntrySerial and AccountNo = @pAccountNo
ORDER BY Eh.EntryID DESC
SET ROWCOUNT 0
OPEN @ntransCursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @ntransCursor into @nValue
END
CLOSE @ntransCursor
DEALLOCATE @ntransCursor;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Pls i need urgent help!!!
View 2 Replies
View Related
Mar 7, 2008
CREATE TABLE #TEST (Keyfield varchar(30) NULL)
INSERT INTO #Test (keyfield) VALUES ('M-S Logistics');
INSERT INTO #Test (keyfield) VALUES ('Monster Racing');
INSERT INTO #Test (keyfield) VALUES ('Mueller Farms');
DECLARE @Search AS nvarchar(30), @Search2 AS varchar(30)
--Query 1
SET @Search = 'Monster Racing'
SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search;
--Query 2
SET @Search2 = 'Monster Racing'
SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search2;
-- Why does query 2 return different result than query 1
View 3 Replies
View Related
Oct 28, 2006
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Thanks for help on this
SM haig
View 5 Replies
View Related
Oct 8, 2007
Hello! I would like to write a value from a column to a parameter in SSIS with the Execute SQL task. The problem is that I will never get a value for the parameter.
You can recreate the problem with the AdventureWorksDW sample database.
1. Drop an execute SQL task in the control flow
2. Set the connection to the AdventureWorksDw database
3. Write this in the SQL Statement box Select Max(FullDateAlterNateKey) as LastDate
From DimTime
4. Set the resultset to single Row
5. Under result set assign LastDate as the Result Name and create a parameter with a default date.
6. Execute the task, that will finish succesfully but the value of the parameter in 5 have not changed.
I have tried to change the scope to both the package level and the task level without any success. The value of the variable is still the default value. I have also tried a string variable without sucess.
Any ideas?
Kind Regards
Thomas Ivarsson
View 5 Replies
View Related
Apr 17, 2008
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".
I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.
The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...
Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null
if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions
...later as part of an else it does the following...
Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end
...and before the return it's value is inserted to different tables.
View 5 Replies
View Related
Oct 10, 2012
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
View 4 Replies
View Related
Sep 25, 2006
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
View 2 Replies
View Related
Oct 15, 2004
The stored Procedure I am using
CREATE PROCEDURE GetUserID
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserID int OUTPUT
)
AS
SET @UserID = (SELECT fldResourceID
FROM tblResources
WHERE fldEmail = @Email AND fldPassword = @Password)
GO
The Code Behind to call it.
Dim intUserID As Integer = 0
Dim strConnectionString As String = dbConnect()
Dim objConnection As SqlConnection = New SqlConnection(strConnectionString)
objConnection.Open()
Dim objCmd As SqlCommand = New SqlCommand("EXEC GetUserID '" & strEmail & "','" & strPassword & "', " & intUserID, objConnection)
intUserID = objCmd.ExecuteScalar()
objCmd = Nothing
objConnection = Nothing
Return intUserID
However nothing is returned from the objCmd.ExecuteScalar line. Can anyone help me out here. All I need it to return is a single value. Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi,In SQL Books Online in the section on @@Error it gives the followingexample:-- Execute the INSERT statement.INSERT INTO authors(au_id, au_lname, au_fname, phone, address,city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0BEGIN-- Return 99 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"RETURN(99)ENDELSEBEGIN-- Return 0 to the calling program to indicate success.PRINT "The new author information has been loaded"RETURN(0)ENDGOHow do I access the value returned by the RETURN statement (i.e. 99 or0) in my asp application that called the stored proc.Sometimes rather than just return an integer signifying success orfailure I've seen examples where the id of the newly added item isreturned on success and perhaps -1 if the operation fails. Theseexamples make use of ouput parameters to achieve this. If theoperation succeeds then then the output parameters value is set to thenew id and this is accessed from the calling application.E.g.IF @@ERROR <> 0BEGIN-- Return -1 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"SELECT @MyOuptputParameter = -1ENDELSEBEGIN-- Return id to the calling program to indicate success.PRINT "The new author information has been loaded"SELECT @MyOuptputParameter = @@IDENTITYENDWhy go to this trouble if you can use the RETURN statement?
View 4 Replies
View Related
Jan 2, 2006
How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?
View 2 Replies
View Related
Jun 16, 2008
Hi I need a function which gets 3 parameters and input and returns 4 integers as output.
I don't know how to return 4 integers as output
any idea is appreciated.
View 7 Replies
View Related
May 9, 2006
Please help me on this one.
I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc
CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG
When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null
I try to return it with the following code (got the code from a friend)
Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function
PLEASE HELP.
Regards,
Sander
View 1 Replies
View Related
Nov 6, 2006
I am using SQL Express 2005 and VB .net Express.
Question 1:
How can I get the result stored in Inserted.ColName (by Output clause of insert command). In the documentation (BOL) for SQL 2005, there is written "returned to the processing application for use" in Output clause (first paragraph).
Question 2:
How to store Inserted.ColName into local variable when insert command is running in stored procedure.:
View 8 Replies
View Related
Oct 10, 2007
ex:
myprocedure(@Cusname varchar(50), @Cusid int output)as
Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY
i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter
{
get
{if (_CuslistAdapter == null)
_CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter;
}
}
Now how i write function in BLL to receive output paramter from creatcustomer function?
View 1 Replies
View Related
Oct 26, 2000
Hi
How I building a stored procedure that
return a output parameter and value return that I can
call from other stored procedure or VB ?
thank you in advance
View 1 Replies
View Related
Feb 4, 1999
I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)
AS
INSERT INTO tbLabel
(LabelType
, Logo
, Field01)
VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)
How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...
Zack
View 2 Replies
View Related
May 4, 2008
I'm new to SSIS. I'm looking for basic pointers to return temp table created by a proc to be written to file shares. Any pointers/examples appreciated.
Thanks,
Siva
View 5 Replies
View Related
Jun 9, 2014
I have a procedure where after processing, i am required to send multiple message to calling application.
For ex:
create procedure test as
(@a as int,
@b as int
@c as int )
/*
some transformations */
print 'variable a is' + @a;
print 'variable b is' + @b;
print 'variable c is' + @c;
I am only providing a sample of return messages, but in reality there a lot more messages where a string and a parameter value need to concatenated.Unfortunately print is not allowing to concatenate parameter value.I can use RaiseError, but these messages are not really any error messages.
i tried to concatenate all message and output it using OUT type parameter, but the length of all messages combined exceeds 10000 characters.
Is there any other alternate to send these messages out to application?
View 1 Replies
View Related
Mar 21, 2008
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()
View 8 Replies
View Related
Jul 20, 2005
Hi all. I have a stored procedure on my sql server that returns asimple informtation about that tim a database was backed up. I owuldlike to create an HTA that operator types can look at to make surethat the backups finished, can someone help me do this?The stored procedure in called sp_lastback and the output looks likethis:database days since backup timestamp of backupdbase1 0 2004-10-14 00:41:21.000dbase2 0 2004-10-14 00:08:36.000dbase3 1 2004-10-13 22:46:57.000Can this be done? If someone could help me with this simple problem, Icould probably reuse the same method a 100 useful ways.Thanks in advance!
View 3 Replies
View Related
Oct 8, 2015
My stored procedure was running sqlserver 2012 but can't return output parameter value here i paste my SP ...
alter Procedure BD_InsertMobileClient_GpsLogPlanogram
(
@SalesPersonID int,
@MobileRowID int,
@StatusCode int output
)
[Code] .....
View 3 Replies
View Related
Feb 18, 2006
The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?
System::Boolean rs::sp(System::String ^ ssp){
System::String ^ k0s0; bool bOK;
System::Data::SqlClient::SqlParameter ^ parami0;
System::Data::SqlClient::SqlParameter ^ parami1;
System::Data::SqlClient::SqlParameter ^ parami2;
System::Data::SqlClient::SqlParameter ^ paramz0;
System::Data::SqlClient::SqlParameter ^ paramz1;
System::Int32 pz0=0;System::Int32 pz1=0;
k0s = ssp;
k0->CommandType=System::Data::CommandType::StoredProcedure;
k0->CommandText=k0s;
paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
paramz0->Direction=System::Data::ParameterDirection::ReturnValue;
paramz0->DbType=System::Data::DbType::Int32;
parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");
parami0->Direction=System::Data::ParameterDirection::Input;
parami1=k0->Parameters->AddWithValue("@SRCXV","UU");
parami1->Direction=System::Data::ParameterDirection::Input;
//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);
paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);
paramz1->Direction=System::Data::ParameterDirection::InputOutput;
paramz0->DbType=System::Data::DbType::Int32;
//k0->Parameters->GetParameter("@RCOUNT");
r0=k0->ExecuteReader();
//pz0=System::Convert::ToInt32(paramz0->SqlValue);
bOK=k0->Parameters->Contains("@RCOUNT");
//k0->Parameters->GetParameter("@RCOUNT");
pz0=System::Convert::ToInt32(paramz0->Value);
pz1=System::Convert::ToInt32(paramz1->Value);
ndx = -1;
while(r0->Read()){
if (ndx == -1){
ndx=0;
pai0ndx=0;
pad0ndx=0;
r0nf=r0->FieldCount::get();
for (iG1_20=0;iG1_20<r0nf;iG1_20++){
this->psf0[iG1_20]=this->r0->GetName(iG1_20);
this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);
this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
else {
pai0ndx=0;
pad0ndx=0;
for (iG1_20=0;iG1_20<r0nf;iG1_20++)
{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
ndx++;
}
r0nr=ndx;
r0->Close();
k0->Parameters->Remove(paramz0);
k0->Parameters->Remove(parami1);
k0->Parameters->Remove(parami0);
k0->Parameters->Remove(paramz1);
return true;
}
View 5 Replies
View Related