How To Get A SP Return Value On Another SP?
Sep 12, 2006I have a SP, which will exec other SP depend on the input.
the "other SP" need to return a integer back.
How to do this?
Thanks for give me a hand.
ADVERTISEMENT
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.
How To Return SqlDataReader And Return Value (page Count) From SPROC
Jan 2, 2006This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(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
'Return' Statement In A Function,Get,or Operator Must Return A Value....Question
Aug 28, 2007
I'm a Reporting Services New-Be.
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
Using Output Clause And Return Timestamp And Return Value
Oct 28, 2006I 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
Why SP Return SQLException When No Data Return ?
Jul 24, 2006hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance
Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure
Aug 19, 2015I 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?
Return Value From SP
Dec 6, 2006I have the following SP
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE spGetResettedPassword@Email nvarchar(50)ASBEGIN SET NOCOUNT ON; declare @newid uniqueidentifier set @newid=newid() declare @newPass nvarchar(10) set @newPass=convert(nvarchar(100),@newid) set @newPass=substring(@newPass,1,7) UPDATE aspnet_Membership SET Password=@newPass WHERE Email=@Email return @newPassENDGO
When calling it from code like this: MyConnection.Open() cmd = New SqlCommand("spGetResettedPassword", MyConnection) cmd.CommandType = Data.CommandType.StoredProcedure myTrans = MyConnection.BeginTransaction() cmd.Transaction = myTrans fBeginTransCalled = True bSuccess = False cmd.Parameters.Add(New SqlParameter("@Email", Email)) Dim NewPassword As Object = cmd.ExecuteScalar If NewPassword <> Nothing And CType(NewPassword, String) <> "" _ And TypeOf (NewPassword) Is String Then Return CStr(NewPassword) Else Return "ERROR, CONTACT US ON THE SITE" End If bSuccess = True fBeginTransCalled = False
I get the following error:
Conversion failed when converting the nvarchar value '40DC5F3' to data type int.
What is happening here?!?!
Return Value From SQL
Jun 14, 2008I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE BEGIN INSERT INTO table1 (nameA, NameB) VALUES (@nameA, @nameB) RETURN 03 END **************************************************************************** dim cn as New sqlConnection(connectionstring) dim cm as sqlcommand cn.Open() With Cm .CommandType = CommandType.StoredProcedure .CommandText = "STOREDPROCEDURE" .Connection = cn End With 'INSERT Names Cm.Parameters.Add("@nameA", SqlDbType.VarChar, 25).Value = nameA.text Cm.Parameters.Add("@nameB", SqlDbType.VarChar, 25).Value = nameB.text param_returnValue = Cm.Parameters.Add("RETURN_VALUE", SqlDbType.Int) param_returnValue.Direction = ParameterDirection.ReturnValue*******************************************************************************************This is where I get stuck. I need to display certain error messages depending on the return value.******************************************************************************************* dim insertStatus as integer insertStatus = Cm.ExecuteScalar() If insertStatus = 01 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 02 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 9999 Then Response.Redirect("success.aspx") End If End If End If I am pretty sure the IF statements arent the way to go especially because it is not working but i am curious to how I am to make this work.
View 2 Replies View RelatedReturn
May 18, 2004whats wrong with this SP? I want @id to contain the row identity of the newly created row as a return value.ALTER PROCEDURE setCountry
(
@name varchar( 50 ) = NULL,
@alt varchar( 24 ) = NULL,
@code varchar( 3 ) = NULL,
@id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @name, @alt, @code )
@id = @@identity
RETURN
If...Else Return Value
Oct 11, 2006Hi,
Here is my sp:
Quote: CREATE PROCEDURE dbo.usp_ProfQuals_Add_AwardingBody_nr
(
@awardbody varchar(100)
)
AS
BEGIN
declare @message int
if EXISTS(SELECT vchr_awardingbody from tbl_ProfQuals_AwardingBody where vchr_awardingbody = @awardbody)
set @message = 1
else
set @message = 2
insert into tbl_ProfQuals_AwardingBody (vchr_AwardingBody) values (@awardbody)
END
return @message
The problem I am having is that it returns the correct value if there is a record that exists or indeed if it doesn't BUT ... even when a record exists it still carries out the insert!
Any help much appreciated.
Dave
How Could I Return The Next Row(Or 8th Row For Example)
Sep 25, 2005Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-Thanks
Getting The Return Value
Aug 15, 2007Hello everyone -
Please advise on why the output value is an empty
declare
@DynamicSQL3 NVARCHAR(4000
SET @DynamicSQL3 = 'SELECT Security_Level_ID ' +
'FROM ' + @DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '
exec sp_executesql @DynamicSQL3, N'@i int output', @AdministratorSecurityID output
I run the query and see the grid being populated with the proper
security_level_id in the query analyzer,
but when i execute a print @AdministratorSecurityID
there is a space character in the results window
please advise on what i am doing wrong
thanks
tony
Return SUM That Is Not 0?
Oct 15, 2013I have this table that contains transactions for a specific client and some clients, when I total the transactions, have a zero balance.
I want to be able to filter those clients out, only show the ones that have open balances.
Here's an example:
DECLARE @T1 TABLE( CODE VARCHAR(6), ITEM INTEGER, AMOUNT INTEGER)
INSERT INTO @T1
VALUES
('ABC',4001,110),
('ABC', 4001, -110),
('ABC',4002,-5.5),
[code]....
If you look at the table, client ABC has a lot of transactions but if I SUM them the balance is 0, same with client GHI. The only row that show show on my query is client DEF.
I am trying to do something like this:
select *
from @T1
having Code in ( SELECT code
from @T1
group by code
having SUM(amount) <> 0)
but I get an error.
No Return Value
Mar 6, 2007Hi all. How could i trigger the result in select statement? If the query does not return a value, there must be a prompt message to appear.
thanks
-Ron-
Return The Last Row Only
Jul 5, 2007Hello!
I'm new to SQL and i've searched everywhere for the answer to this question but I can't seem to find the anwer anywhere. My question is.....
If there are multiple rows for a specific field, is there a way in SQL to only return the last row based on that specific field.
For example, I wrote a query that returns a list of patients who have a specific lab type. If a patient has had several labs taken, they show up in the result set several times (for each lab date). I only want to return the most recent lab date.
Here is my query........
SELECT id, resulted_test_desc, result_value, units, update_date
FROM dbo.lab_result LEFT OUTER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE resulted_test_desc = 'Hemoglobin'
Any help would be greatly appreciated.
Thanks!
Amedeo
Return New Id
Jul 20, 2005Dear All,I try this but it keeps returning zero !Dim newid As LongCurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as'newID'"Me.RequeryMsgBox newid >> returns 0Msgbox DMax("ORD_ID","[ORDER]") >> returns the new idMy question stays: is it possible to return the newid into a VB-variabledirectly?Filip
View 1 Replies View RelatedReturn One Row
Jul 20, 2005HelloI was wondering if someone could help me out with something.With the following rows:agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl1 1 1 10-10-2000 01 10-15-2000 021 1 2 10-09-2000 06 051 2 1 10-08-2000 05 10-20-2000 011 2 1 10-05-2000 01 10-15-2000 03What I need is SQL to get one row per agnt/supv with the following:strt_dt, strt_lvl of earliest strt_dttrm_dt, trm_lvl of the latest trm_dt but only if all trm_dts forthe supv arefilled in, otherwise null for bothagnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl1 1 10-09-2000 06 null null1 2 10-05-2000 01 10-20-2000 01If anyone has any ideas on the most efficient way to accomplish this,I'd appreciate a reply.Thanks!
View 5 Replies View RelatedHow To Return Just Any One Row Among So Many?
Sep 19, 2007
I have set of procedures that implement business logic in such a way that I am just interested to return only one row when there could possibly be millions of them. Those procedures are in this form
SELECT Top 1 SGP.GroupID
FROM dbo.SubGroupPerm SGP
INNER JOIN dbo.GroupPermStats GPS on SGP.GroupID = GPS.GroupID
INNER JOIN dbo.IMISProduct IP on GPS.ProductID = IP.ProductID
INNER JOIN dbo.HasMeasureGroup HMG on SGP.GroupID = HMG.GroupID
WHERE SGP.SubscriberID = 40335
AND HMG.MeasureTypeID = 119
AND GPS.IndustryCode = 'CT'
Such tables are properly indexed and most queries return results using clustered index seek/index seek operations. Unfortunately, in presence of large tables I don't get the desired response time because query generates massive IOs. I was wondering if someone could possibly have another clever way to re-write above query where we just have to return one row?
SP Return Value
Mar 14, 2008in my package i am executing a stored procedure from execute sql task which is something like
DECLARE @return_value int
EXEC @return_value = [dbo].[NextDigitalDataLoad]
SELECT 'ReturnValue' = @return_value
GO
Now based on the value returned by this i have to check in a dataflow task whether the file content is correct or not.
please tell me how to do
Return One Row Per ID
Oct 22, 2007I have a table that contains multiple entries per a specific Id similar to the following: 1 09-01-2007 1 09-02-2007 1 09-15-2007 1 09-06-2007 2 09-03-2007 2 06-09-2007 2 07-09-2007 3 01-01-2007 3 02-02-2007 I need to get a single row back for each id which corresponds to the row with the minimum datetime. Using the example I would need to return: 1 09-01-2007 2 06-09-2007 3 01-01-2007 Anyone have any ideas on how to accomplish this with a TSQL query? Thanks.
View 1 Replies View RelatedReturn More Than One Row
Mar 15, 2008Select Model, Kota as [Çıkan Anket] ,
(Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model) as [Verilen Adres] , (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model) as [Donen Adres]
,
(Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model) as [Donen Anket] ,((Select cast(Count(*) as decimal(2,0)) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model ) / (Select top 1 (cast(Kota as decimal(2,0))) From dbo.Model Where Model = tt.Model))*100 as [Oran]
,
(Kota - (Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model)) as [Kalan Anket]
,
((Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model)- (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model)) as [Kalan Adres]
From dbo.Model as tt where tt.Model in('A3')
it's return more than one row :
1. column 2. col..
A3 5 606 0 0 0.000000 5 606
A3 2 606 0 0 0.000000 2 606
A3 2 606 0 0 0.000000 2 606
A3 0 606 0 0 0.000000 0 606
A3 0 606 0 0 0.000000 0 606
A3 0 606 0 0 0.000000 0 606
A3 0 606 0 0 0.000000 0 606
i want it like below
A3 **9*** 606 0 0 0.000000 5 606
PLEASE HELPP
StoredProcedure Return Value
Jun 27, 2006I am using SQL Server 2005 now and I have a table with following columns.
ID, FirstName, LastName, Email
"ID" is the primary key (int) and is set auto generated (1 increment)
I have a StoredProcedure to insert a new record.
CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO
I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
How To Use Return Value From SqlDataSource
Nov 3, 2006I have the following code in my page:
<asp:SqlDataSource ID="SqlHolidayDateRange" runat="server" ConnectionString="(OurConnectionString)"
SelectCommand="CountHolidays" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="WeekEndingDatePicker" Name="EndDate" PropertyName="SelectedDate" />
<asp:Parameter Direction="ReturnValue" Name="RowCount" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
I need to place RowCount, the returned integer value from the stored procedure CountHolidays into a field on the web page. How in the world do I access that data?
Everything I've been able to find works if I'm defining my own command object and writing this thing from scratch. What I - and you - have to work with is what's above.
Return Single Value
Dec 3, 2006Hello, I created a stored procedure which selects a value according to ContentId. I know that will be only one value returned or none. So if a record is found I want to return the string contained in ContentHtml. Else I want to return the string "NotFound" Could somebody help me out with this? Here is my present stored procedure: -- Specifies the SQL-92 equals compliant behavior SET ANSI_NULLS ON GO -- Specifies the SQL-92 quotation mark rules SET QUOTED_IDENTIFIER ON GO -- Alter procedure ALTER PROCEDURE [dbo].[by27_Content_GetContent] -- Define the procedure parameters @ContentName NVARCHAR(100), @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL BEGIN -- Select localized content from by27_ContentLocalized SELECT dbo.by27_ContentLocalized.ContentHtml FROM dbo.by27_Content INNER JOIN dbo.by27_ContentLocalized ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId WHERE (dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName); END -- Create procedure GO Thanks, Miguel
View 1 Replies View RelatedReturn 0 Rather Than Null
Feb 2, 2007i have query which does the following select x from y where t = "House"x is an integer. If no record is found, how do i get it to return 0 rather than null?
View 9 Replies View RelatedHow To Return A Static Value?
Apr 25, 2007I have the following stored procedure...
CREATE Procedure UserGetInfo2 (@UserID int, @SystemTimePeriodID int )As set nocount onSELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccountedFROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @UserID) AND (UserTimePeriod.SystemTimePeriodID = @SystemTimePeriodID)returnGO
The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.
Obviously, I need to wrap this in an IF...EXISTS
IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @SystemTimePeriodID) AND (UserID = @UserID))
(the SELECT above, since that's what needs to come back if the data exists)
ELSE
Do the same select but put in a static value for BudgetCode, like '0000'
GO
How could I do the part where the IF...EXISTS fails?
I'm... not sure I can use RETURNS, since it feeds into this recordset:
rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")
and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>
Need To Know How To Return Row Count From SP
May 16, 2007How can I tell if table "CompanyInfo" has any rows? Below is the partial code behind and the SP I am running to fill CompanyInfo;
PROCEDURE newdawn.AcceptSubmission @CompanyID intASSELECT C_ID, CG_ID, LS_ID, L_Rank, L_Name, L_URL, FROM tblStoreSubmitWHERE C_ID = @CompanyIDRETURN
SqlCommand cmd3 = new SqlCommand("AcceptSubmission", con); cmd3.CommandType = CommandType.StoredProcedure; cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd3; DataSet ds = new DataSet(); try { da.Fill(ds, "CompanyInfo");
How Can I Return Everything While Using A Paramater
Jun 26, 2007I guess a pretty basic sql question here. I am using tableadapters. And some of the queries have multiple parameters. But often a parameter is at a default or null - wherein I'd like to be able to pass it in and have it not filter at all - ie., return everything.
Simple example:
Select * from ReportsWhere ReportID = @ReportID
If I have a dropdownlist of reports where the values are ReportIDs - but the topmost unselected value is say "", then the results will be generated by
Select * from ReportsWhere ReportID = ''
Whis not what I want. What I would want is simply
Select * from Reports
or
Select * from ReportsWhere ReportID = ReportID
So how can one setup say an ObjectDataSource or somehow handle this where I don't need to use dynamic sql to expand/contract my where clause?
Really need hlpe on this and hope I've made my question reasonably clear. Thanks.
Return Inserted Row
Jul 23, 2007how do i insert a record, then return the inserted record back to VS (ASP + VB) to display? maybe just the ID will be enough. then i will do a select
View 6 Replies View RelatedGet A Return Value With EXEC?
Aug 29, 2007Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
View 1 Replies View RelatedSQL Return Parameter
Dec 11, 2007Hi Guys, I can't for the life of me get the output from the stored procedure into my variable in VB.net. Can you guys help out, I'm on my third hour. Trying to learn this peice as I go (output parameters). I have truncated the code to look at the output parameter, everything works except the VB side of the output parameter, my messagebox keeps giving me a value of zero. I have even tried .SQLValue instead of .Value and it didn't work...thoughts????Thanks in advance!Tim
SQL:ALTER PROCEDURE dbo.SDC_RisIncidentInsert @SeqInc int output ASSelect @SeqInc = Max(Sequence) + 1 From _Smdba_._Telmaste_Select @SeqInc---------------------------------------------------------------------------------------------
VB.NetPrivate Sub InsertIncident()Dim conn As SqlConnectionDim comm As SqlCommandDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("SDC_RisIncidentInsert", conn)comm.CommandType = System.Data.CommandType.StoredProcedurecomm.Parameters.Add("@SeqInc", SqlDbType.Int)comm.Parameters("@SeqInc").Direction = ParameterDirection.Output
Dim SeqIncident As Integer = comm.Parameters.Item("@SeqInc").ValueMsgBox(SeqIncident)
Try conn.Open() comm.ExecuteNonQuery()Catch ex As Exception dberrorlabel.Text = "Could not Insert Incident Record"
Finally conn.Close() Response.Redirect("/RISDEV/SDC_Incident.aspx?Name=" & SeqIncident & "#Incident")End TryEnd Sub
Select Return Nothing
Dec 18, 2007Hi...I have table who contain words in Hebrew (right to left language), and when i try to select:WHERE (Topic LIKE '%Hebrew word%')I get no rows...Any idea? thanks...
View 1 Replies View Related