.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

Incorrect output from Stored Procedure

Posted By:      Posted Date: September 19, 2010    Points: 0   Category :Sql Server

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

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

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?

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?

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 )

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'


Incorrect syntax near '%'. When trying to create/alter system stored procedure


Sql Server 2005 SP3

After finally figuring out that for whatever reason the sp [sys].[sp_refreshsqlmodule] was not loaded I found the procedure on another machine and tried to create it on the machine missing the sp. I get an error: Incorrect syntax near '%'. The error applies to 2 lines in the sp:


EXEC %%Object(MultiName = @name).LockMatchID

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


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

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

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

Stored procedure generator?


Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.

Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



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