Returning Errors From Stored Proc
Feb 5, 2007
Hi,
Does sql server return an error code when a stored procedure fails, or an operation inside a stored procedure fails. How does one trap it?
We have an application that executes a number of stored procedures. I would like to verify that the procedures executed successfully, and the operations contained within the stored procedures (i.e. insert into a table) did not result in an error. If there was an error with the stored procedure of the operations inside, I would like to trap it, and return it to the calling program (non sql server).
I have seen the @@error global variable, but it only gives the return code of the last operation, which means I would have to check after every select, insert, delete, etc. I am hoping to avoid this.
Any thoughts?
Thanks in advance
View 2 Replies
ADVERTISEMENT
Mar 7, 2008
I need to return a rowset from a stored procedure. The returned rows will have ten columns and need to be bound to a gridview. Here is a code snippet.
'Create a DataAdapter, and then provide the name of the stored procedure.MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", MyConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@condition_cl", _
SqlDbType.VarChar, 100))
'Assign the search value to the parameter.MyDataAdapter.SelectCommand.Parameters("@condition_cl").Value = sqlwhere & orderby
'ASSIGN THE OUTPUT PARAMETERS. HERE IS WHERE I NEED HELP (?????????????????)
DS = New DataSet() 'Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") 'Fill the DataSet with the rows returned.
'Set the data source for the DataGrid as the DataSet that holds the rows.GridView1.DataSource = DS.Tables("TMPTABLE_QUERY").DefaultView
GridView1.DataBind()
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
MyConnection.Close()
In my little research, I have seen examples of how to return a single value, but not multiple rows. I essentially have two problems. I'm not sure how my output parameters are to be defined and added. Do I need a separate 'Parameters.Add' statement for each column field value returned or can I do a single 'Parameters.Add' statement to define the whole row as an output parameter? Also, upon returning from the call to the SP, will I need a looping mechanism to populate the recordset for each individual record returned, or will the 'MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") suffice, as included in my code above?
Thanks in advance.
View 3 Replies
View Related
Mar 3, 2004
i have a stored proc
CREATE PROCEDURE pop_notes ( @contnum as bigint,@cusnotes varchar(1000) OUTPUT) as
begin
declare @noteid as int
select @noteid=max(noteid) from cusaddnotes where contractnum=@contnum and rtrim(popup)='yes'
if @noteid > 0
begin
select @cusnotes=(notes + ' [' + convert(varchar(100),dateadded) + ']' ) from cusaddnotes where noteid =@noteid
end
else
begin
set @cusnotes='none'
end
print @cusnotes
end
GO
the srored proc is returning the @cusnotes with a size 0 and its throwing out errors in my asp.net page
'@cusnotes' of type: String, the property Size has an invalid size: 0
i tried to run the stored proc frm the QA
declare @note as varchar(1000)
exec @note=pop_notes 3430,'y'
print @note
it returned
none
0 <-- this seems to be causing the error
what could be the error/reason...
thanks in advance
View 3 Replies
View Related
Nov 15, 2005
I am trying to get the identity of an inserted record using this SP:
<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
@AuctionID Int OUTPUT,
@StartDate char(255),
@StartTime char(255),
@Location char(255),
@Title char(255),
@Description char(255),
@Images char(255)
)
AS
INSERT INTO Auctions
(
StartDate,
StartTime,
Location,
Title,
Description,
Images
)
VALUES
(
@StartDate,
@StartTime,
@Location,
@Title,
@Description,
@Images
)
SELECT AuctionID AS ID
FROM Auctions
WHERE (AuctionID = @@IDENTITY)
</code>
Then, I am using this class for a file called dataaccess.cs to return the ID:
<code>
public string SaveImageName(string ImageName, string AuctionID)
{
SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;
SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
paramImagePath.Value = ImageName;
this.newSqlCommand.Parameters.Add(paramImagePath);
SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
paramAuctionID.Value = AuctionID;
this.newSqlCommand.Parameters.Add(paramAuctionID);
SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
paramImageID.Direction = ParameterDirection.Output;
this.newSqlCommand.Parameters.Add(paramImageID);
//Return SqlDataReader Struct
this.newSqlCommand.Connection.Open();
this.newSqlCommand.ExecuteNonQuery();
this.newSqlCommand.Connection.Close();
int returnID = (int)paramImageID.Value;
return returnID.ToString();
}
</code>
The above code returns null.
Can anyone tell me what I am doing wrong?
THanks, Justin.
View 2 Replies
View Related
Jul 18, 2014
In some of our business object reports we have to create variables to decode values to what we want. I am trying to replicate this in SQL Server and remember doing this in SQL server 2000 years ago back can't remember the exact way to do it. I remember running a query and calling stored proc within query which would return the value I wanted but not sure if I can still do this in SQL server 2008 and by that I mean doing it within query or have to do it another way.
Basically what I want is to have a procedure with all the variables replicated within that procedure so that when I run a query I can just call the appropriate bit of code by passing a specific name like
select job.dept, dbo.decodevariable('ShowJobDesc' job.jobtitle), job.salary
from job
so 'ShowJobDesc' and the job.jobtitle would be used to decode each job title to return job description.Just a bit unsure and can't remember if I am doing this the right way, is this possible?
View 2 Replies
View Related
Feb 26, 2007
Hi
I have coded the simple login page in vb .net which calls the stored proc to verify whether the user login details exists in the database. The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()
Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output
'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)
Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1
If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
End Try
Else
ErrorLbl.Text = "Please enter you username and password"
End If
View 5 Replies
View Related
May 14, 2007
Hey guys, could somebody pls provide me with an easy example as to how you would return multiple strings from a stored proc? Even a link to a decent tut would be great!
Muchos gracias!
View 12 Replies
View Related
Jun 6, 2007
I have a stored proc which should be returning a datatable. When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code. I'm not getting any errors during runtime. Any help at all would be a BIG help!
private void PopulateControls() { DataTable table = CartAccess.getCart(); }
public static DataTable getCart() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "sp_cartGetCart";
DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = cartID; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param);
DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm)); return table; }
public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.SendErrorLogEmail(ex); throw ex; } finally { // Close the connection command.Connection.Close(); } return table; }
View 1 Replies
View Related
Jul 20, 2005
HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
View 6 Replies
View Related
Nov 2, 2007
I am having a difficult time figuring this one out. I have a stored procedure that as part of an ASP.Net app connects to a csv file through a mapped network drive and imports the data into a table in SQL Server. Here it is.
------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_ImportComp]
-- Add the parameters for the stored procedure here
@CSV as varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin Try
Begin Tran
Declare @sql as nvarchar(max)
Set @SQL = 'Insert into dlrComp ( SN,
CM,
PM,
MC,
Comp_Total,
XU,
GM,
From_Date,
To_Date)
Select [Serial Number],
CM,
PM,
MC,
[Comp Total],
XU,
GM,
[From Date],
[To Date]
FROM OPENROWSET(' + char(39) + 'MICROSOFT.JET.OLEDB.4.0' + char(39) + ',' + char(39) + 'Text;Database=S:' + char(39) + ',' + char(39) + 'SELECT * FROM ' + @csv + char(39) + ')'
--print @sql
Exec sp_executesql @stmt=@sql
Commit Tran
End Try
Begin Catch
Rollback Tran
--Do some logging and stuff here
End Catch
END
------------------------------------------------------------------------------------
If I am connected to SQL through SQL Management Studio while logged in on the server that is running SQL as DomainUser1 and execute
exec usp_ImportComp @CSV='Comp.csv'
It completes successfully
However if I open SSMS (while logged into Windows on my PC as DomainUser2) using runas to run it as DomainUser1 while logged into my PC and connect to SQL Server using WIndows Auth and run the same I get the following error message.
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'S:' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
If I add
With Execute as 'DomainUser1' and modify the stored procedure I get the same error message above.
If I log onto the Server that is running SQL as DomainUser2 I can successfully run
exec usp_ImportComp @CSV='Comp.csv'
Both User1 and User2 have the same permissions to the Share and csv as does the Domain user under whose context SQL Server is running.
What am I doing wrong?
View 4 Replies
View Related
Jul 7, 2004
Hi
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go
This is asp.net code
Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)
conn.Open()
dr = cmd.ExecuteReader
nRecords = convert.int32(cmd.parameters(@return).value)
conn.close
Thanks
Lbob
View 1 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Nov 17, 2006
Following Proc is returning all records.I have passed any value to parameters but no luck.
CREATE PROCEDURE GetAllUsers(
@persontype varchar(100)="",
@name varchar(100)="",
@adddatetime datetime="",
@activeaccount int =-1,
@country varchar (20)=""
)
AS
declare @cond varchar(1000) ;
if @persontype<>""
begin
set @cond= @cond+"and persontype="+@persontype
end
if @name<>""
begin
set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) "
end
if @activeaccount<>-1
begin
set @cond= @cond+'and activeaccount='+@activeaccount
end
if @adddatetime<>""
begin
set @cond= @cond+'and adddatetime='+@adddatetime
end
if @country<>""
begin
set @cond= @cond+'and x_country='+@country
end
print @cond
exec( " select * from users where 1=1 "+@cond)
GO
zx
View 2 Replies
View Related
Sep 22, 2000
I have my error routines and raiserrors working fine. I can run my stored procs through query analyzer and I get the error messages I expect. WHen the stored proces are run through Access, VB, C++, what is required to return the
error messages I see in QA. Is it specific to the app front end. All my front ends will be using ODBC as their
connection
View 1 Replies
View Related
Aug 24, 2006
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
View 3 Replies
View Related
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Jan 10, 2007
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View 2 Replies
View Related
Feb 20, 2003
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
View 1 Replies
View Related
Jan 13, 2006
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
View 5 Replies
View Related
Jan 20, 2004
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
View 14 Replies
View Related
Aug 30, 2007
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
View 2 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Dec 18, 2007
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
View 3 Replies
View Related
Feb 14, 2007
I am trying to run an update stored procedure that will add 1 revision to the rev field and return the Value back to my Application. My number is incrementing by 2 and not 1.
Here is my Stored Procedure
CREATE PROCEDURE dbo.sp_Update_file
@kbid big,@filename nvarchar(50),@rev big OUTPUT,@moddate datetime,@owner nvarchar(50),@author nvarchar(50)AsUPDATE KBFile
SET rev = rev + 1,filename = @filename,moddate = @moddate,owner = @owner,author = @author,@rev = (Select rev from kbfile where kbid = @kbid)WHERE kbid = @kbIDGO
View 3 Replies
View Related
May 31, 2005
Ok, still a little bit novice on stored procedures, and can't see why this one doesn't return the value I need.In this case, lessonLocation.CREATE PROCEDURE dbo.getLocation @studentId VARCHAR(20), @lessonLocation VARCHAR(50) OUTPUTASBEGIN DECLARE @errCode INT
SELECT lessonLocation FROM cmiDataModel WHERE studentId = @studentId
SET @errCode = 0 RETURN @errCode RETURN @lessonLocation
HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeHANDLE_DBERR:
SET @errCode = -1 RETURN @errCodeENDGOIn the query analyzer, it returns the value in the db, but always blank when run in the .Net app.Thanks all,Zath
View 5 Replies
View Related
Feb 22, 2006
Hi everyone!
I am new to sql server 2005 and visual studio 2005.
I have the following simple stored procedure that checks if a user exists:
-------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]
@UserName varchar(256)
AS
BEGIN
SET NOCOUNT ON;
IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@UserName) = LoweredUserName ))
RETURN(1)
ELSE
RETURN(0)
END
-------------------------------------------------------------------------------------------------------
I use the following code to execute the procedure on visual studio:
-------------------------------------------------------------------------------------------------------
.
.
.
cmdobj As SqlCommand
cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj)
cmdobj.CommandType = CommandType.StoredProcedure
cmdobj.Parameters.AddWithValue("@UserName", "blablalala")
cmdobj.ExecuteNonQuery()
cmdobj.Dispose()
connobj.Close()
.
.
.
-------------------------------------------------------------------------------------------------------
I expected that cmdobj.ExecuteNonQuery() would return 1 if the user
blablab exists or 0 if the user doesnt, but it just return -1 (i think
because no row was affected)
Does anyone knows how to retrieve the value that my stored procedure returns?
Thanx in advance!
View 1 Replies
View Related
Mar 14, 2006
Trying to get a count on records that match search and all I'm getting is 0. I'm using the same basic sp and code elsewhere and it works fine. Anyone see anything wrong here?
Stored Procedure:CREATE PROCEDURE GetResultsCount(@searchCatalog nVarchar(100))ASRETURN ( SELECT COUNT(*) FROM CatalogWHERE itemName LIKE @searchCatalog ORitemLongDesc LIKE @searchCatalog)GO
Code: Dim connStr As SqlConnection Dim cmdResultsCount As SqlCommand Dim paramReturnCount As SqlParameter Dim intResultsCount As Integer connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdResultsCount = New SqlCommand("GetResultsCount", connStr) cmdResultsCount.CommandType = CommandType.StoredProcedure cmdResultsCount.Parameters.Add("@searchCatalog", Request.QueryString("search")) paramReturnCount = cmdResultsCount.Parameters.Add("ReturnValue", SqlDbType.Int) paramReturnCount.Direction = ParameterDirection.ReturnValue connStr.Open() cmdResultsCount.ExecuteNonQuery() intResultsCount = cmdResultsCount.Parameters("ReturnValue").Value connStr.Close() Me.lblResultsCount.Text = intResultsCount
View 8 Replies
View Related
Dec 6, 2004
Im creating an app in VB.NET that calls a stored procedure which inserts data into one of my tables. I need it to return the id it creates (autonumber) to the VB.NET Program
I keep raising an exception in VB.NET...I want to make sure my stored procedure is correct first...sadly this is my first procedure ive created.
CREATE PROCEDURE db_addtocontractInsert
@contractid int,
@playerid int,
@numyears smallint,
@year1 float(8),
@year2 float(8),
@year3 float(8),
@year4 float(8),
@year5 float(8),
@yearsremain smallint
AS
INSERT INTO db_Contracts
VALUES (@playerid,@numyears,@year1,@year2,@year3,@year4,@year5,@yearsremain)
SELECT @contractid
Return @contractid
GO
-----------------------------------------------
Any tips or corrections that need to be made would be greatly appreciated.
Thanks
Tainter
View 1 Replies
View Related
Jan 10, 2015
If I pass in the value of 1, the stored procedure should return the value of (.5360268), else it should return 0 for any other value(null/blank/empty/etc)
The following stored procedure is returning the value 0 if I pass in the value of 1 which is wrong. And I would need to set to 0 for any other value. I am checking only for null or empty, but the if condition should check any value (except 1) and return 0 for it.
ALTER PROCEDURE [dbo].[Calculator]
(
@ExtraCardiacArteriopathy bit
)
AS
BEGIN
declare @zarterio decimal(14,10)
[Code] .....
View 6 Replies
View Related
Sep 29, 2005
Hi Guys, I have a series of count statements in a Stored Procedure that return some values. What I want to do is take all of the values that have been returned by the Count statements, add them up and return the value to my .Net Code, but for some reason whenever I try I carry on getting a returned value of 0.
I have checked that the statements are in fact returning values other than 0, and I was wondering if there is something that I am doing wrong in the code below:
<code>
CREATE PROCEDURE [dbo].[UCOutstanding]
@userid int
AS
DECLARE @Num as Int
DECLARE @Num2 as Int
DECLARE @Num3 as Int
DECLARE @Num4 as Int
SET @Num = (SELECT count(*) FROM images, albums, memorial WHERE (images.active=0 AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num2 = (SELECT count(*) FROM downloads, memorial WHERE (downloads.active=0 AND downloads.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num3 = (SELECT count(*) FROM images, comments, albums, memorial WHERE (comments.active=0 AND comments.imageid=images.id AND images.albumid=albums.id AND albums.memorialid=memorial.id AND memorial.userid=@userid))
SET @Num4 = (SELECT count(*) FROM memorial, story WHERE (story.memorialID=memorial.id and memorial.userid=@userid))
DECLARE @Total as Int
SET @Total = @Num + @Num2 + @Num3 + @Num4
Return @Total
GO
</code>
Thanks for the Help
GP
View 5 Replies
View Related
Jan 22, 2008
I have a Sproc shown below I want the procedure to return the New_ID value back to the calling program how can I do this.
The current approach is not working. Please help.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
.................................
ALTER PROCEDURE [dbo].[SP]
(
@Id int=NULL ,
@site_id int = null,
@Date datetime = null,
)
AS
BEGIN
Declare @New_ID varchar(10)
EXEC [dbo].[SP_GENERATEID] @New_ID output
INSERT INTO A_Table(
CODE,INT_ID,SITE_INT_ID,DATE,
)
Values
(
@New_ID,@Id,@site_id,@Date )
return @New_ID
END
Thanks
View 3 Replies
View Related
