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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Need a string output from stored procedure

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

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 )

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

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

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


View Complete Post

More Related Resource Links

Using IN clause(string parameter) in Stored Procedure

Hi guys. I need to use IN clause in a selection query. From all the resources that I found from google, it's all about adding a function to split a integer parameter into a list. But I got a string parameter, for example 'A1,B1,C1' I tried to use the below where single quotes in @CustCode is double up. ___________________________________ Declare @SQL VarChar(1000)   SELECT @SQL = ' SELECT [something]' SELECT @SQL = @SQL + ' FROM [Table]' SELECT @SQL = @SQL + ' WHERE CustomerCode IN ('  + @CustCode + ')'   EXECUTE (@SQL) _____________ Executing the stored procedure in MSSQL works fine, but it didn't when executing from Visual Studio 2005. Please advice.

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 = "",

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?

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!


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)
set @OutValue = @InValue

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


Using return variable:

create proc dbo.TestReturn (@InValue int)
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?

help inserting an empty string and a default date thru a stored procedure


I want to insert an empty string in a nvarchar field and a default date like a base date in a datetime field inside a stored procedure without passing it the value thru a parameter

how is this done?

INSERT INTO deals_SoldItems (











this dont work



Getting Output value from a Stored Procedure in Execute SQL



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:



PROCEDURE [dbo].[insertIntoTable]
AS numeric OUTPUT




Getting Values output from Stored Procedure




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


DECLARE @currweek datetime,  @inweek datetime

IF @week_date = '10/14/2010'


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










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);  


how to pass XML file path as input string to a Stored Procedure


Hello All,

I need to pass the file path (or hardcode the XML file path ) of an XML file to a stored procedure. This SP will then read the values from the XML file and based on these values the Insertion / updation will be done thereafter.

I am not able to pass the XML file path to this stored procedure.  Right now I an doing as givn below :

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
  <add key="approvalMode" value="On" />
EXEC sp_xml_preparedocument @idoc OUTPUT,'c:\Inetpub\wwwroot\HealthandSafety\Aspx\ApprovalMode.xml'

FROM OPENXML (@idoc,'/configuration/appSettings/add',0)
      WITH ([key]  varchar(10),
            value varchar(20))
EXEC sp_xml_removedocument @idoc

Here any change in the xml file needs to be changed in the SP too. This will lead to double work as later on this SP will be made as an SQL job.

Is there a way I can pass or hardcode the XML file path in the SP rather than duplicating the XML file contents again

Please advice!!

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

dropdown list fill with Linq and convert string to datetime and ordering and stored procedure call.


I wanted to share this more than anything,  it took me most of the morning searching and finding various post on various sites but I got it figured out and working.  It might be better coded, but it is a start for some one trying to understand how linq and ASP.Net things work.

What I have is a database table with a datetime in string format and a stored procedure which returns all the data.
What I want to do is load a dropdown list with MonthName and Year for a selection choice on generating monthly report.

The stored procedure is in a Linq to SQL class dbml and the connection string is dynamic, i.e., made througha call to another class.

here is the code.  Enjoy understanding how it works.

// Miscellaneous Details are filled in for helping you get the big picture.
// Some of the using statements are for other things in the code behind, 
// but I left it in so don't get confused,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections.ObjectModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using MYProject.LinqtoSQLStuff;

namespace MYProject.ChooseReportPage
	 public partial class TheReportPage : System.Web.UI.Page  
	     protected void Page_Load(object sender, EventArgs e)


Passing a SSIS Variable with a string datatype to a Stored Procedure requiring a uniqueidentifier


I have a SSIS package that uses a SQL Server stored procedure as a data source. The parameter in the stored procedure is pulling data from a column with a uniqueidentifer (guid) data type. I created a package level variable in SSIS with a string datatype. SSIS variables do not offer a uniqueidentifier datatype.

I have the following text in the SQL Command text box:

exec dbo.spBillingReferenceSource
@BatchID = ? 

When I click the parameters button the mappings are @BatchID for the parameter (which is the exact name of the parameter in the stored procedure) and User::BatchID (the SSIS variable) 

When I run the stored procedure in preview mode in the SSIS dataflow, it returns an error message -- "No value given for one or more of the required parameters"

If I run the stored procedure by typing the value in the datasource box such as listed below, it works and delivers the proper records. I have even modified the stored procedure to convert the column to use varchar(36). However, it still returns the same error message. Does anyone have any suggestions on how I can get this to work. Thank you.


Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

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