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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

Use a stored procedure to return data for algorithm

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server
 

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




View Complete Post


More Related Resource Links

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

Trying to run a stored procedure from vb code with oracle data provider.

  

Hello,

Here is my SP:

create or replace
PROCEDURE ZGETUSERSSIGNONS (vUid IN VARCHAR2, p_getuserssignon_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
FROM ZSIGNON
WHERE substr(UserId,1,2) <> vUid
Order By UserId;
END ZGETUSERSSIGNONS;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 

Second:

Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.

 

 


Entity Framework 4.0 Dynamic Data - stored procedure

  

I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.


can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

  
The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

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 -

inserting data after caliculation using stored procedure

  
hi my requirement is like i want to insert data to table after caliculationliketable name salaryhikecolumns salaryinput,20%hike, 25%hike and 30%hikesalaryinput column details  to be input by the user and hike is to be caliculated and inserted with 20%hike, 25%hike and 30%hike are columns of same table.plz help me.thank you very much.

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?


How to write Stored Procedure for Insert Data & Execute it in MS SQL?

  

How to write Stored Procedure for Insert Data & Execute it in MS SQL?


Getting data from a stored procedure instead of a table

  

Using linq2sql, I've seen examples of using stored procedures for insert, update and delete but how can I use a stored procedure for select?

Thanks, Simon


Getting data from a stored procedure instead of a table

  

Using linq2sql, I've seen examples of using stored procedures for insert, update and delete but how can I use a stored procedure for select?

Thanks, Simon


SSIS DB2 Stored Procedure as a data source

  

Hi,

I have this problem:

In a DTS package I can connect to a DB2 on an AS400 and execute a stored procedure. I can then import this data into a table.

 

I can't do this in SSIS. DTS uses an ODBC connection. I have tried this with SSIS 2008 r2 – I can use a ado.net connection which picks up the ODBC driver.

I pass in the stored proc as a sql command.

I click preview and all the data is there. I click columns and none are defined.

 

Has anybody gotten this to work? It seems like a step back to me- DTS did

Capturing data through a stored procedure as a result of a click event (asp.net)

  

Hello,

I am having trouble capturing data as a result of a click event. Basically, I don't know what code to put and where for the click event.

I need to record information from a text box, into a table in Microsoft SQL Server. Any help would be great.

My stored procedure is below:

/****** Object:  StoredProcedure [dbo].[SearchConcept_SP]    Script Date: 10/23/2010 12:17:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchConcept_SP]
@Concept_Name nvarchar(50),
@Keywords char(50)

AS
BEGIN
IF EXISTS(SELECT Concept_Name FROM Concept WHERE Concept_Name = @Concept_Name)
PRINT 'This concept already exists'

ELSE
INSERT INTO Concept VALUES (@Concept_Name, @Keywords)
END


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.


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