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


Post New Web Links

Stored Procedure multile values parameter with integer type

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :
 

Hi,

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?

Thanks

Peiwai




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">
                        <Columns>
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                <ItemTemplate>
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <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 = "",

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?

problem with passing parameter values to stored proceedure from asp.net c#

  
hi all, i am facing a problem while passing parameters to a stored proceedure. following is my sql query written for creating this stored procedure:   CREATE proc [dbo].[getstudent]( @wherecolumn nvarchar(2000), @wherevalue nvarchar(2000)) asbegin  declare @sql nvarchar(max) set @sql = 'select * from student2 where ' + @wherecolumn+'='+@wherevalueexec sp_Executesql @sql end   my table contains four columns: And the sql Query For the table is as follows:   CREATE TABLE [dbo].[student2]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [rollno] [int] NOT NULL, [class] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_student2] PRIMARY KEY CLUSTERED ( [rollno] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]   from page behind I am passing the values to parameter as follows:   SqlCommand command = new SqlCommand("getstudent", c.con); SqlDataAdapter datadapter = new SqlDataAdapter(c.cmd); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@wherecolumn", SqlDbType.NVarChar).Value = "rollno"; command.Parameters.Add("@wherevalue", Sq

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

stored procedure with parameter quest

  
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

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

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

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

No Return Type for a Stored Procedure in a Linq to SQL Data Layer

  
Hello. First, I don't know if this is the right forum section to post this in, but I couldn't fine a more suitable one. Feel free to move this if it's totally worng. I have a weird problem when using LINQ to SQL and Store Procedures with a return value... The problem is that when I drag my SP's onto the "Methods Pane" the property field "Return Type" is set to (None) and greyed out. And here's the most weird thing... I created a test SP yesterday wich returned SCOPE_IDENTITY() and it worked flawlessly. Dragged it onto the "Methods Pane", could play with the return type property (hence it was not greyed out) and everything was working out nice. I used a ISingleResult<T> and could fetch the .ReturnValue... Today I was going to contiune to create the rest of the SP's i needed with a return type but today when I drag my Store Procedures to my Methods Pane, I couldn't access the Return Type property... I even dragged the SAME procedure I used yesterday (wich then worked great) and now I can't get the dbml-layer to notice that there is a return value... I've tried to create new solutions, new Data Layers, used different databases/servers, but still the same problem... Don't really know if it's LINQ or something else that's causing this... This is how part of my SP wich I used yesterday to test looks

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

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

ALTER PROCEDURE [QMS].[GetQueries]

@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS



SELECT [QueryID]

,[UserID]

,[QueryName]

,[Comments]

,[CreateDate]

,[UpdateDate]

,[ExpirationDate]

,[LastAccessedDate]

FROM [ReportQueries]

WHERE

[UserID] = @UserID

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