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

Post New Web Links

my stored procedure not give me result as per input given by me

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :ASP.Net

ALTER Proc [dbo].[Usp_CheckUserLogin]
    @Username varchar(20),
    @Password varchar(20)

    Declare @RowCount int
    Declare @RowCount1 int
    set @RowCount = (select distinct UsrId from UserMast where username=@username COLLATE SQL_Latin1_General_Cp1_CS_AS )    
    set @RowCount1=(select distinct UsrId from UserMast where [password]=@password  COLLATE SQL_Latin1_General_Cp1_CS_AS)    
    if(@RowCount > '0' and @RowCount1 > '0')
        if (@RowCount=@RowCount1)
--        select U.UsrId,U.FirstName,U.LastName,U.Email,U.companyId,U.role,R.RoleName from UserMast U join rolemast R on U.Role=R.RoleId
--        where usrid=@RowCount
        select 'success'
     if(@RowCount > '0' and @RowCount1 = '0')

View Complete Post

More Related Resource Links

How can i send an input param of XML with more than 8K to a stored procedure in ADO?

 My sample SP : CREATE PROCEDURE MyINV   @sData AS XML  AS  BEGIN  SET NOCOUNT ON; select 1 as iTestCount  END The param sData is more than 8000 characters.  till 8000 bytes, it works .But throwing exception on 8001 bytes.   Code = 80040e21,Code meaning = IDispatch error #3105,Source = Microsoft OLE DB Provider for ODBC Drivers,Description = [Microsoft][ODBC SQL Server Driver]String data, right truncation   Im using VC++/ADO. _variant_t varChunk; varChunk = (char*)(sValue.GetBuffer()); pParam->AppendChunk(varChunk); pCommand->Parameters->Append(pParam); pCommand->Execute(...)  

how to include a user defined table type as input for stored procedure

Hi ,  I have a user defined table type which i need to pass as input parameter to the stored procedure .How can i do that?

insert result of stored procedure in new table

hi how can i insert the result of stored procedure in the new table. Best Regards. Morteza

JDBC - calling stored procedure always returns 1 result set


I am running the following against Northwind on Sql Server 2005. It always returns a result set of 1 row regardless of the parameters passed:

Connection conn;
// not showing conn creation

CallableStatement callStmt = conn.prepareCall("{ call [Sales by Year] (?,?) });
callStmt.setDate(1, new Date (long_Jan_1_1996));
callStmt.setDate(2, new Date (long_Jan_1_1997));
ResultSet rs = callStmt.executeQuery();

Why do I only get 1 row of results back?

thanks - dave

Very funny video - What's your Metaphor?

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



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 ******/
ALTER PROCEDURE [dbo].[SearchConcept_SP]
@Concept_Name nvarchar(50),
@Keywords char(50)

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

INSERT INTO Concept VALUES (@Concept_Name, @Keywords)

binding to item template label of a gridview from stored procedure result of common column names


hi  my stored procedure contains a joining result of different tables with common column name to a dataset result can bind to gridview

as follows

CREATE proc SppShowStock 

select p.id, p.ProductId,  p.ProducName,  sh.CompanyName ,  su.CompanyName

TblStock p inner join TblSupplier su on p.SupplierId=su.CompanyCode 
     inner join TblShipper sh on p.ShipperId=sh.shipperid   
           inner join  TblCategory c on p.Category=c.Id 


<asp:TemplateField HeaderText="id">
                    <asp:Label ID="lblid" runat="server" Text='<%# Eval("id") %>'></asp:Label>


i am little bit in confusi

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!!

Get same result when running MDX in stored procedure and Analysis Services


I have a simple MDX query as follows:

select non empty {descendants([dim fund].[fund structure].&[1])} on rows,
[measures].[amount] on columns
FROM [xyz]

When it runs against Analysis Services, it returns two columns:

All Funds        123,435,567
Fund1            122,400,090
SubFund1      6,445,435
SubFund2      546,343

When it runs it in a stored procedure with Openquery, it returns multiple columns to represent the structure:

create procedure usp_OLAPtest
declare @strMDX_Sql as nvarchar(max)

set @strMDX_Sql = 'select non empty {descendants([dim fund].[fund structure].&[1])} on rows,
[measures].[amount] on columns FROM [xyz]'

exec (N'select * from openquery(xyz_OLAP, ''' + @strMDX_Sql + '''); '

All Funds  NULL    NULL          123,435,567
All Funds  Fund1   NULL          122,400,090
All Funds  Fund1   SubFund1   6,445,435
ALL Funds  Fund1   SubFund2   546,343

Is there any way for the stored proce

Input parameter to a Stored Procedure External Content Types and External lists



I am displaing data in form of a External list that gets it data from an External Content Type that gets its data from a stored procedure. This works fine but what I really would like to do is to be able to send an inparameter with the user name down to the store proceder so it only return the rows marked with the users loginname.

Is this possible (out of the box)?

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.



Call SQL Stored Procedure Asyncronously



I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.

Best regards,


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