.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Store Procedure Return Value -3, but C#.Net always return value is Convert into 0Xffffff Value ....

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :ASP.Net
 

My Store Prodecure.....

------------------------------------------------------------------------------------

ALTER PROC [dbo].[Polls_Polls_SaveAdmin]  
@UserID INT,
@CategoryID INT,
@TypeID INT,
@OptionID INT=NULL,
@Title nvarchar(4000),
@Description nvarchar(MAX),
@StartDate DATETIME,
@EndDate DATETIME,
@PollID INT,
@OptionA NVARCHAR(100),
@OptionB NVARCHAR(100),
@OptionC NVARCHAR(100),
@OptionD NVARCHAR(100)
AS
    DECLARE @SaveStatus INT
    DECLARE @SaveStatus1 INT
    SET @SaveStatus = 0
    SET @SaveStatus1=0

BEGIN
    IF @PollID>0
       BEGIN
        IF NOT EXISTS (SELECT POLLID FROM POLLS WHERE (CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) ) OR (CAST(CONVERT(VARCHAR, EndDate, 101) AS DATETIME) BETWEEN CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @EndDate, 101) AS DATETIME) AND PollID<>@PollID))
            BEGIN
           &


View Complete Post


More Related Resource Links

How many out parameter we can return by Store procedure?

  

How many out parameter we can return by Store procedure?


Waiting for reply.


Oracle Store Procedure does not return any thing in the out parameter

  

I am goig to execute an oracle store procedure using C# through through ODP.NET. The store procedure executes succsfully but it does not return any value in the out parameter.

When I execute the same store procedure with the same input parameteres from within SQL*Plus. It does return values.

This is to be clearify here that in the oracle store procedure the data type for the out parameter is number and in the c# the variavle that will hold its value is Integer. I also tried using Long on the C# side but it is still not working. After the successfull execution of the store procedure the C# variable contains nulll value.  


How to save return value from store procedure in ssis and use that value to call another store proce

  

I have bunch of store procedures and each procedure is depend on previous procedure's return value.I am using Execute Sql task to call store procedure.I don't have any input parameters,just simple select statement.I want to save the return value from that store procedure into variable and use that value to call another store procedure.

Can someone help on this...

Thanks in advance..


Stored Procedure Default Return Value

  
Tried searching and have not found the answer to this one.  Think I know from testing but want to be sure.   I have a SP on  MSSQL 2000 that I created.  Within the table I call 6 update statements.  All my params are input. I developing software using VS 2008 PRO with a typed dataset.  I added the stored procedure to my tableadapter and it created the subsequent update() method with an int return type.  I coded a statement to test the return value like so... int Success = tableadapter.update(value1,value2,etc) I examined the Success variable and found it to be 6. I looked at another SP that calls 7 update statements within it and the return value after being called was 7. I did not specify any return value in my SP.  I can only presume that the SP is by default returning the aggregate amount of rows affected.  My question is if my presumption is correct.  My reason for asking is I would like to know this value is reliable for testing that all 6 update statements in the SP were  successful.    Thank you.

How to get the return value from stored procedure

  
Hi,I  not getting the return value from stored procedure that I have written so how to get that plzz its urgent I have return the procedure as ALTER PROCEDURE [dbo].[sp_AddPaySlip]  -- Add the parameters for the stored procedure here @inDMLType varchar(1), @EmpID varchar(2000) = '', @BankAccountNumber nvarchar(50) = '', @NoOfDaysWorked varchar(500)='', @GrossSalary varchar(2000) = '', @Basic varchar(500) = '', @PerformanceAllowance numeric=0, @PersonalAllowance numeric=0, @Hra numeric=0, @PF numeric=0, @PTax varchar(10)='', @Tds numeric=0 , @Total varchar(500) = '', @NetPay varchar(500) = '', @PFAccountNumber varchar(100)='', @Year varchar(10)='', @Month varchar(10)='', @ou_intRetValue int=0 output AS BEGIN declare @l_sqlqry nvarchar(1000) ,@intError INT ,@inCnt int --set @l_sqlqry ='' --set @interror = 0-- Set @inCnt =0 --If @inDMLType  not in ('I') goto Insufficient_Data  --IF @inDMLType  ='I' -- for Inserting  DECLARE @result int BEGIN /*if exists(Select 1 from users where Year(DateofJoin) >= @Year and EmpID = @EmpID) Begin set @interror = 2 End if exists(Select 1 from users where month(DateofJoin) > @Month and EmpID = @EmpID) Begin -

No Return Type for a Stored Procedure in a Linq to SQL Data Layer

  
Hello. First, I don't know if this is the right forum section to post this in, but I couldn't fine a more suitable one. Feel free to move this if it's totally worng. I have a weird problem when using LINQ to SQL and Store Procedures with a return value... The problem is that when I drag my SP's onto the "Methods Pane" the property field "Return Type" is set to (None) and greyed out. And here's the most weird thing... I created a test SP yesterday wich returned SCOPE_IDENTITY() and it worked flawlessly. Dragged it onto the "Methods Pane", could play with the return type property (hence it was not greyed out) and everything was working out nice. I used a ISingleResult<T> and could fetch the .ReturnValue... Today I was going to contiune to create the rest of the SP's i needed with a return type but today when I drag my Store Procedures to my Methods Pane, I couldn't access the Return Type property... I even dragged the SAME procedure I used yesterday (wich then worked great) and now I can't get the dbml-layer to notice that there is a return value... I've tried to create new solutions, new Data Layers, used different databases/servers, but still the same problem... Don't really know if it's LINQ or something else that's causing this... This is how part of my SP wich I used yesterday to test looks

How to return looped procedure results so that they display as multiple rows of a single output inst

  

I understand that the below fragment will return a separate 1-row results set for each successful loop (also unnecessarily repeating the column header each time). How can I alter my procedure so that each value returned by the loop is appended underneath the previous value as multiple rows of a single results set? Do I have to store each return value in a temp table or someother storage object? It would be nice to just be able to spit them out row by row as each loop returned. Possible? Easy? Hard? I really appreciate the help I've received here so far.

DECLARE techCursor CURSOR 
FOR 
SELECT (whatever)

OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE pr_FindExpenses4Tech @techNumLoop, @itemCount OUTPUT
SELECT @itemCount Item_Count
F

How to get the return value from a stored procedure

  

Hi everyone..

I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

this is my procedure..

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/******************************************
Check if the PO has been invoiced or paid
************************

How to get the return value from a stored procedure

  

Hi everyone..

I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

this is my procedure..

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/******************************************
Check if the PO has been invoiced or paid
************************

How to get the return value from a stored procedure

  

Hi everyone..

I've a stored procedure and procedure works well. In that procedure, there is one output parameter which i need to get returned.

this is my procedure..

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/******************************************
Check if the PO has been invoiced or paid
************************

Stored Procedure, when to use Output paramemter vs Return variable

  

When would you use an output parameter vs a return variable, or vice versa? In the following simple example, I can achieve the samething using either one.

Using output parameter

create proc dbo.TestOutput (@InValue int, @OutValue int output)
as
set @OutValue = @InValue

declare @x int
exec TestOutput @InValue = 3, @OutValue = @x output
select @x

 

Using return variable:

create proc dbo.TestReturn (@InValue int)
as
return @InValue

declare @x int
exec @x = TestReturn @InValue = 3
select @x

 

As you can see, they both do the samething. Can someone show me an example where the choice of a output parameter vs a return variable would make a difference?


Use a stored procedure to return data for algorithm

  

Hi,

I am looking at using the time series algorithm to predict a products future.

However, I seem to have missed the obvious. How do I return a subset of a tables data as the input to the algorithm?

Can I specifiy a SP to use or do I specify the parameters within DMX?

 

Thanks


Stored procedure doesn't return value

  

In my asp.net page I pass paremeters to a stored procedure and then inside a try blog the foloving line: affectedRows = ssqldbsorce.insert
never returns expected result from stored procedure.

Inside stored procedure event if I explicitely write the following line "return 5" for example, it never returns 5, only -1, 1, 2

For example here a very simple one without output parameters(I still should be able to return a value without output parameters, right?):

ALTER PROCEDURE [dbo].[createUser]

@regCode varchar(10)


AS
SELECT * FROM dbo.reg_code WHERE reg_code=@regCode
....
---other statements, and for the sake of the example lets return 5
RETURN 5

Is there something wrong with doing it thi way,  here is also a tutorial, according to which it should work just fine: http://www.developerfusion.com/samplechapter/91/stored-procedures/4/

Is there another way maybe in the assp.net page to capture the value in the variable from a stored procedure.


Error on getting return value from Oracle procedure in SSIS package

  
In my case, I want to execute a stored procedure in Oracle database using SSIS package and if there has error in Oracle, I want to get the error message from Oracle and pass it to SSIS to log the error.

I can execute the stored procedure through SSIS successfully, but the problem is I cannot get the error message using OUT parameter of procedure in Oracle, the following part is what I have done to test whether SSIS can fulfill my requirement:

1. Oracle client installed, use Microsoft OLE DB Provider for Oracle, Test Connection to Oracle succeeded.

2. The stored procedure created in Oracle database:
    create or replace p1(in1 varchar2, out1 OUT varchar2) as begin
        out1 := 'nice';
    end;


3. In the SSIS package, I add a "Execute SQL Task" component:

Variable defined = User::output (String datatype)
ResultSet = Single Row
SQLSourceType = Direct Input
SQLStatement =

    declare
        in1 varchar2(10);
        out1 varchar2(10);
    begin
        in1 := 'wow';
        out1 := '1a2';
&nbs

using a stored procedure for a listview, I am trying to only return items in a column of a table tha

  

I am trying to create a stored procedure that when fed a session parameter, it will only find items that  start with a particular character.  For example, if I have a table named table1, and a column name col1, then if I have 10 items in the column and 5 of them start with the letter c, I want to use a stored procedure to onlly return the items that start with a c.

I am sure it is somethin like:

SELECT col1

FROM table1 

WHERE col1 = "c??????????"


ListView DataPager with SQL-Statements that already return only paged data.

  

Hi,

As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.


Need Syntax To Make Results of LINQ Union Return Non-Generic Type

  

Hi.

I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
UNION
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter
ORDER BY CreateDate DESC

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
        {
            using (WorkbookDataContext dc = _conn.GetCont

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend