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


Top 5 Contributors of the Month
Sharad
Manish
Post New Web Links

Getting Values output from Stored Procedure

Posted By:      Posted Date: November 01, 2010    Points: 0   Category :ASP.Net
 

Hi,

 

From this below stored Procudure, using VB.Net code can i get the Values like "Number of Rows Affected" and "Yes / No" Value from "Select"

 

Stored Procudure:

ALTER PROCEDURE [dbo].[lookup]  @week_date datetime

AS

DECLARE @currweek datetime,  @inweek datetime

IF @week_date = '10/14/2010'

BEGIN

  UPDATE dbo.work_status SET  ob_flag = 0, ob_update_date = NULL WHERE dbo.work_tatus.week_date ='10/14/2010'

  SELECT 'YES'

END

ELSE

  SELECT 'NO'

RETURN 0

 

 

 

 




View Complete Post


More Related Resource Links

optional output parameter in CLR Stored Procedure

  
I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it. the declaration goes as follow   <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/> Public Shared Sub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> Optional ByVal De As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Para As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Copia As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Oculta As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Assunto As String = "", <SqlFacet(MaxSize:=-1)> Optional ByVal Mensagem As String = "", _<br/> <SqlFacet(MaxSize:=-1)> Optional ByVal Anexo As String = "", _<br/> <strong><SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""</strong> , _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Smtp As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Usuario As String = "",

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

SQL Server 2008 Stored Procedure output parameter

  
I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

Can I pass a value for a output variable in a stored procedure in ADO.NET?

  
I am calling a stored procedure, that exists in a SQL Server 2005 database,  using ADO.NET, in which I instantiate a sql command object.The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in  a stored procedure?

How can I replace values in GridView returned from a Stored Procedure

  
I am working on creating a stored procedure that will output a pivot table.  In the pivot table will be either the string NULL or a number.  How can I reformat this in ASP.NET so the NULL value becomes a blank cell in the gridview and the number (whatever it is) becomes an 'X' ?

update stored procedure for existing values of column(salperyr)

  
tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

  
tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

Incorrect output from Stored Procedure

  

Hello! I am trying to get a stored procedure to show me an email address in the EmailAddress column of the table based off of the input, which in this case is the CustomerID. So basically, say 3 is the CustomerID I want the sproc to show the email address for customer id 3, but instead it's just showing me the EmailAddress column heading. Here is my code below, help!

Thanks

DROP PROCEDURE Email
GO
CREATE PROCEDURE Email(@cid INT, @email [VARCHAR] = OUT)
AS
SELECT * FROM SalesLT.Customer
WHERE @email = EmailAddress AND @cid = CustomerID
PRINT @email


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?


Need a string output from stored procedure

  

hai everyone,,

I got a situation now... i had a stored proc: How can i get the final select statement in the code behind.


Here is the stored proc.

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



/**********************

Getting Output value from a Stored Procedure in Execute SQL

  

Hi,

I have an Execute SQL task ,in which I am having an stored procedure.

My requirement is I have to map the output value of sp to the output value of my Execute SQl Task.

So that once after the Execution of ExecuteSQL Task.I can have a check in precedence constraints and based on that I can move forward.


Thanks, A2H

How do you set SSIS package variable equal to an output parameter from a stored procedure that is ty

  

I have a stored procedure insertXXX that has one OUTPUT parameter of type numeric. The stored procedure looks like:

ALTER

 

PROCEDURE [dbo].[insertIntoTable]
@outputID
AS numeric OUTPUT

AS

BEGIN

 

How to call Oracle Stored Procedure which has an output parameter from SSIS?

  

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:

 

    PROCEDURE Interface_Begin

    (p_from_dttm       OUT varchar2,

     p_error_code            OUT number,

     p_error_text       OUT            varchar2,

     p_proc_name        OUT varchar2);  

 


Need to order stored procedure output based on custom order

  

*Read the whole question before you decide to send this to the chart forum

So here is what I am trying to do:

I have a chart in VB Express (vb.net) that obtains its data from a stored procedure in my SQL Express database. The chart is a stacked column chart and shows events on the X axis. What I need is these events to be displayed in a very specific order in my X Axis.

The problem:

I have been told that I cannot custom order this in the application, but rather I need to change my stored procedure output to get the events in the order I want. It was suggested to me that I should make a new table in my database that has the names of the events in 1 column and the order I want them in, in the other. Then use that to order my output in my stored procedure.

I do not care how I accomplish what I need to accomplish so long as I do indeed accomplish it. Here is some database structure to help you understand my database:

Table: Buyer

BuyerID (PK), ProjectID (FK), Status, Type

There are 12 different statuses, though my stored procedure doesn't know that. These 12 statuses need to be shown in a very specific order. My current stored procedure is as follows:

select Status as Status, Type as Type, count(BuyerID) as Value from Buyer WHERE projectID = @ProjectID AND Status IS NOT NULL AND Type IS NOT NULL GROUP BY Status,Ty

Looping through Textbox Values and Inserting into SQL Server via Stored Procedure

  

Hello All,

I have a database table as follows:

USE [Diel_inventory]
GO
/****** Object:  Table [dbo].[QUOTEDETAILPARTS]    Script Date: 05/09/2010 17:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QUOTEDETAILPARTS](
	[QuoteDetailPartID] [int] IDENTITY(1,1) NOT NULL,
	[QuoteDetailID] [int] NOT NULL,
	[PartNumber] [float] NULL,
	[Quantity] [int] NULL,
 CONSTRAINT [pkQuoteDetailPartID] PRIMARY KEY CLUSTERED 
(
	[QuoteDetailPartID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS]  WITH CHECK ADD  CONSTRAINT [fkQuoteDetailID] FOREIGN KEY([QuoteDetailID])
REFERENCES [dbo].[QUOTEDETAIL] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS] CHECK CONSTRAINT [fkQuoteDetailID]


This table receives data from my web application via a stored procedure, snippet pasted below:

 

nsert into dbo.QuoteDetailParts
	(QuoteDetailID, PartNumber, Quantity)
	values (@QuoteDetailPartID, @PartNumber, @Quantity) 
	
	set @QuoteDetailPartID=scope_identity()

 

In my quote.aspx page, I have a wizard control that collects numerous data points.  In one of the wizard steps, I hav

Passing the values from the Deleted virtual table to another Stored Procedure

  

Hello,

I am wondering if something can be done.  I have a large database that I would like to implement change tracking on selected tables.  We've come up with a way to do it the least painful way we can, but I do not know if it will work.

I've created a trigger that will pass the table name to a stored procedure.  That stored procedure creates a Tracking table by appending the suffix 'Changes' to the past in table name.  But now how do I get the data from the Deleted table?

I can't do a SELECT INTO or INSERT INTO because the tables have timestamp fields and blobs.  I don't care if I do net get the data from those fields but I don't want to have to enter all the field names besides those two types.  I can make a stored procedure get the column names and types from a system table and dynamically build the SQL.  But I would prefer to do that in one place instead of each trigger.

Basically the problem is how do I get the data from the Deleted table to another stored procedure without explicitly naming all \the fields in the trigger.

If you don't understand what I mean then please ask, I will clarify with examples.

Thanks

JM


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