.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

stored procedure with parameter quest

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :ASP.Net
I can execute this statement fine: SELECT item,cust_name,OFFICE from sample where cust_name = @p1 but what if I wanted to use "where cust_name like @p1" ? It does not seem to give any results nor error. I have tried "where cust_name like '%' + @p1 + '%' to no avail

View Complete Post

More Related Resource Links

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" />

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

problem for using webservice (sql parameter stored Procedure)

i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

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

Launching a stored procedure with parameter from Excel 2003

I have a stored procedure in SQL Server which needs a parameter and returns a resultset.   sp_xxx 'parameterValue'   I'd like to know if it is possible to launch this stored procedure through Excel 2003 and get the resultset in the active spreadsheet.   I have tried to do this with Microsoft Query but it doesn't allow parameters in queries that can't be graphically represented.   I have tried also through an ODC files but I get an error.   Is it possible to do this?

Executing an SSIS package from an stored procedure and passing a parameter to it:

Hello all, I'm trying to execute an SSIS package from an stored procedure, using xp_cmdshell. When I execute the package, hard coding the value on the variable, I get rows inserted to my destination tables, which indicates my package pulled the correct data based on the TransactionID parameter. When I execute the package from the stored procedure, the package executes without issues, but I get no rows inserted into my destination tables, which leads me to think it is a data type issue, between the package and the variables I'm using within the stored procedure. The variable TransactionID on the SSIS package, is a string data type, therefore I'm passing it as a varchar from the stored procedure. Am I doing this correctly? Maybe I'm missing something, can you guys see anything wrong? Also, I tried login into the server and executing these 2 commands on the cmd prompt: One with "" quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheck_RenewalOrder.dtsx" /CONFIGFILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheckProcess_Config.dtsConfig" /CHECKPOINTING OFF  /REPORTING EWCDI  /SET \Package.Variables[User::TransactionID].Properties[Value];"470219"   And also one without quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrde

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......


@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS









FROM [ReportQueries]


[UserID] = @UserID

Datetime filter value pass to BCS stored procedure parameter


I have data coming from one of the stored procedures that has startdate and enddate as parameters.I have created a business data webpart tht accepts this ECT.When I give the start date and end date in this format----> 2010-02-02 00:00:00Z,I can able to get the data from the database.

               As this is not a feasible approach,I have used a two datetime filters that has calendar control attached to it,so that user can use this to pick the start date and end date.Now,my issue here is when I choose the startdate and enddate,ECT is not accepting the format of the date that these filters are passing.I have changed the filter descriptor LOBDateTimeMode to "Local" in the external content type,but still I am receiving the same error.Does any one has any solution for this??

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




Passing a parameter into a stored procedure from ASP.NET


Via the button1_ click event I retrieve all files in a directory (Example Follows)

protected void Button1_Click(object sender, EventArgs e)



DirectoryInfo di = new DirectoryInfo(@"C:\Test");

foreach (FileInfo filename in di.GetFiles())






I now need to check whether this filename exists as a field in a SQL Server table. For example, if the directory retrieves a file named ExampleFile.txt and does not exist in the (Table = TableFileStore;s Field = FileRetrievals) then I want to insert the filename.

How do I construct a stored procedure to pass the filename(s) into a stored procedure as a parameter? Do I need to also create an array of filenames and pass the array into my stored procedure? Does the stored procedure belong within the foreach loop?

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


SQL timestamp in GridView and manually added update parameter for stored procedure in C#



I have a SQL table containing a timestamp column.  This table is displayed in ASP using GridView control.

The timestamp column is indicated in the DataKeyNames property of the GridView.

I'm using TemplateFields for all columns and a ItemTemplate HiddenField control connected to the timestamp column using Eval.

<asp:TemplateField ItemStyle-Wrap

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

stored procedure WITH PARAMETER


I have a stored Proc with a parameter @rptparameter
i have a sql query to fetch data based on different condition
 but i want to pass the parameter .when ever i pass EXEC SP_name 'LARGE'
it should execute following query
insert into DIM
select value from FACT where value >25,

when i EXEC SP_name 'Small'
it should execute

insert into DIM
select value from FACT where value <10,

when i EXEC SP_name 'Middle'

it should give the output of
insert into DIM
select value from FACT where value between 10 and 25

How should i use my parameter...in sql query

any guidence would be of great help


Stored Procedure multile values parameter with integer type



I have created a store procedure with input paramenter @param1 of integer type to return result from a select statement as below:

SELECT * FROM TableABC WHERE columnabc LIKE (@param1)

the columnabc in the WHERE clause is of integer type.

I will like to be able to pass multiple values of integer to @param1, such as @param1 = 1, 2, 3.

Please advise how do I achieve that?



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