Selecting a approximate values by using stored procedure

Using SQL Server 2005 and VB.Net

Table1

ID Name Dept

001 Raja IT
002 Ravi CSE
003 Sun开发者_如何学Gos IT

Stored Procedure

Create Procedure Selecttable1 As Select ID, Name, Dept from table1

I want to execute the stored procedure with condition like

cmd = new sqlcommand("Exec SelectTable1 where id like  '" & idsearch.Text & "%' ", dbcon)
cmd.ExecuteNonQuery

idsearch.Text - Textbox Input value

When I try to run the above query it was showing error.

How to execute a stored procedure with conditions?

Need Query Help

You seem to be looking for running a stored procedure with parameters.

For this both the stored procedure needs to be created with parameters, and the code calling it should pass those in.

Stored Procedure:

Create Procedure Selecttable1 
   @inID INT
As Select ID, Name, Dept from table1
WHERE ID = @inID

Code:

cmd = new SqlCommand("SelectTable1", dbcon)
cmd.Parameters.Add(new SqlParameter("@inID", idsearch.Text))

Do not use a construct like in your example ("Exec SelectTable1 where id like '" & idsearch.Text & "%' "), as this is an open SQL Injection vulnerability.

You should use parameters, as described above in order to avoid this.

I would create a second stored procedure that takes "idsearch" as a parameter and applies the LIKE condition directly on the underlying query.

e.g.

CREATE PROCEDURE MySproc2
    @IDSearch VARCHAR(100)
AS
BEGIN
   SELECT SomeField
   FROM YourTable
   WHERE AnotherField LIKE @IDSearch + '%'
END

Here's an example (not with your data) from the standard SQL Sever ocumentation at http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx

CREATE PROCEDURE au_info2
   @lastname varchar(30) = 'D%',
   @firstname varchar(18) = '%'
AS 
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
   ON a.au_id = ta.au_id INNER JOIN titles t
   ON t.title_id = ta.title_id INNER JOIN publishers p
   ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
   AND au_lname LIKE @lastname
GO

You'll find most of the documentation you need for SQL Server from the site I linked to.