Selecting a approximate values by using stored procedure

Question

Using SQL Server 2005 and VB.Net

Table1

ID Name Dept

001 Raja IT
002 Ravi CSE
003 Suns 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

Answer 1

your proc needs to accept parameters.

Answer 2

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.

Answer 3

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

Answer 4

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.