0

I am fighting with a sql query for the past 1 day. My requirement is like I want to get the values from user and have to pass my query

select * from table1 where column1 like '%healt%'

But how to use the same query when we are passing the variable from client side or UI side?

I tried the query by using ?,@., like given below 

select * from table where column like '%' ?'%'

but nothing worked. And nother issue I am facing with is using the OR operator.

When I am going with search criteria I want to display results by giving value to any one of the field. But in sql it is asking for all the columns which I am passing.

select * from table1 where column1 like ? OR cloumn2 like ?

But in the above SQL server is asking me to pass the value for both column1 and column2. what changes I want to make to get results by passing any one of the column?

Kindly Help me in solving this issue.

ram
  • 149
  • 1
  • 4
  • 10

4 Answers4

0
select * from table where column like '%' + @variable +'%'

Also add the same variable as a parameter to the command in the code.

Pavel Ronin
  • 576
  • 4
  • 11
  • @variable?? you mean my colum name? and another thing, whether it can be applicable for string variables? because it should use string concatenation – ram Aug 30 '13 at 15:47
  • No. I mean the value. "healt" in your example. If you are using c#, then the SQLCommand object has a Parameters property. Add a parameter with the value you seek. – Pavel Ronin Aug 30 '13 at 15:49
  • what does this `@test`,`@variable` denotes? so how does this helps me to pass the value from clientside or from UI side? – ram Aug 30 '13 at 15:52
  • What technology is used on the client side/UI side? Here is an example of how to do it from C# : http://stackoverflow.com/questions/4056872/how-to-pass-variable-into-sqlcommand-statement-and-insert-into-database-table – Pavel Ronin Aug 30 '13 at 15:55
  • clint side is java. so when i call this query through a java code, i have to use get() method and in the implementation layer I have to use set() method. so by using ? in the query, the get and set method can pass the value to the ? in the query. But i am failing in using partial search using LIKE keyword – ram Aug 30 '13 at 15:57
  • Okay. Then try '%' + ? +'%'. – Pavel Ronin Aug 30 '13 at 15:58
  • @bornas: thanks much for your reply. But the language that I use on front end is JAVA. Can you provide me for JAVA – ram Aug 30 '13 at 15:59
  • From this article http://stackoverflow.com/questions/12078744/inserting-variable-values-into-sql-server-using-java It would look kinda like this : PreparedStatement stmt = connection.prepareStatement("select * from table1 where column1 like '%' + ? + '%'"); – Pavel Ronin Aug 30 '13 at 16:04
  • The above select statement which you have given is not working. I am also trying the same with different possibilities, but everything is failing – ram Aug 30 '13 at 16:14
  • Sorry. I don't even have a Java environment to test it. You should add the Java tag to the question. – Pavel Ronin Aug 30 '13 at 16:18
  • If you are working with DB2, you should remove the SQL Server tag and add a DB2 tag. – Pavel Ronin Aug 30 '13 at 16:38
0
  select * from table where where col1 like '%' + @test + '%'
Sonam
  • 3,406
  • 1
  • 12
  • 24
  • what does this `@test`,`@variable` denotes? so how does this helps me to pass the value from clientside or from UI side? – ram Aug 30 '13 at 15:52
0
Declare @yourParam1 varchar(20), @yourParam2 varchar(20)

set @yourParam1 = 'healt'
set @yourParam2 = 'abc'

select * from table where column1 like '%' + @yourParam + '%'
or column2 like '%' + @yourParam2 + '%'

my test is this.

my table

My Table

my Result

My Result

Procedure is like this 

--exec Pr_Test 'healt' --this is your execute SQL

ALTER PROCEDURE [dbo].[Pr_Test]
      @yourParam varchar(20),

AS
BEGIN
   select * from table where column like '%' + @yourParam + '%'    
END

in java like this 

  String prName ="call "Pr_Test(?, ?)"
    .....
    cs.setString(1, "healt")
very9527
  • 891
  • 7
  • 9
  • So everytime I cant set the `@param value`. It should be passed from the UI side or setvalue function. so when i call this query through a java code, i have to use `get()` method and in the implementation layer I have to use `set()` method. so by using ? in the query, the get and set method can pass the value to the ? in the query. But i am failing in using partial search using LIKE keyword – ram Aug 30 '13 at 15:56
  • as of now if the get the set methods from java has to work. for that I have to pass ? in the query – ram Aug 30 '13 at 16:09
  • I use "sql server management studio", it could be work with Procedure – very9527 Aug 30 '13 at 16:10
  • But I am using DB2 server in which the server is configured directly in RAD, so that you can work with the tables form the datasource explorer view – ram Aug 30 '13 at 16:16
  • could you give me a vote,thanks! you could find a UI for your DB2 – very9527 Aug 30 '13 at 16:23
0

Use SQL parameters on Client/UI side:

in your C# or other code create a SqlParameter :

String searchValue = "test values";
// adding '&' signs
searchValue = String.Format("%{0}%", searchValue);

SqlParameter sqlParam = new SqlParameter("@YourParam", searchValue);

After this add this parameter to your SqlCommand object

Fabio
  • 31,528
  • 4
  • 33
  • 72
  • :I am specific to java. think this might work on C#, but following similar kind of method in java wont help – ram Aug 30 '13 at 16:34
  • Point of my answer is only add `'%' + yourValue + '%'` before creating a Parameter – Fabio Aug 30 '13 at 16:49