use variables in where,Orderby,Like in SqlServer

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

How To use variables in where,Orderby Classes & Like Conduction in SqlServer 2005

i mention my code in below lines.. but MyCode Not Returning any Values

@target varchar(30),
@prefixText varchar(100)

As
Begin


SELECT Top 10 ad.Doctor_Code, ad.DoctorName, Admin_Doctor_Timing.AvailabilityStatus, Admin_Department.Department_Name,Admin_Company_Master.CompanyOrBranchName AS Branch FROM Admin_Department INNER JOIN Admin_Doctor AS ad 
	ON Admin_Department.Department_Code = ad.Department INNER JOIN 
	Admin_Company_Master ON ad.Branch = Admin_Company_Master.Branch_Code LEFT OUTER JOIN 
	Admin_Doctor_Timing ON ad.Doctor_Code = Admin_Doctor_Timing.Doctor_Code 
	where @target Like @prefixText +'%' ORDER BY DoctorName Asc


End 

End

Posted 31-Mar-11 18:47pm

sathishreddy A

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-03-31T18:58:00

In the where condition, use column names. You cannot vary a column using a variable. So this:

SQL

where @target Like @prefixText +'%' ORDER BY DoctorName Asc

should be like

SQL

where columnname Like @prefixText ORDER BY DoctorName Asc

Also depending where the variable prefixtext is coming, add the % to the end of the variable or let the user add it, don't put it in the statement directly. So before the select you could have something like:

SQL

set @prefixtext = @prefixtext + '%'

If you want to vary the statement using variables you should put the SQL text into a string variable and then use the target variable to add relevant column. Like:

SQL

SET @sqlText = 'SELECT TOP...WKERE ' @target + ' LIKE...'

And then use EXECUTE[^] to run the statement.

sathishreddy A · Accepted Answer · 2011-04-01T20:56:00

Now i got solution from Mika Wendelius & i applyed his suggestion now it's working fine

my code :

 (
@target varchar(30),
@prefixText varchar(100)
)

as
begin
	declare @sqry as varchar(1000)
set @sqry='SELECT Top 10 ad.Doctor_Code, ad.DoctorName, Admin_Doctor_Timing.AvailabilityStatus, Admin_Department.Department_Name,Admin_Company_Master.CompanyOrBranchName AS Branch FROM Admin_Department INNER JOIN Admin_Doctor AS ad 
	ON Admin_Department.Department_Code = ad.Department INNER JOIN 
	Admin_Company_Master ON ad.Branch = Admin_Company_Master.Branch_Code LEFT OUTER JOIN 
	Admin_Doctor_Timing ON ad.Doctor_Code = Admin_Doctor_Timing.Doctor_Code 
	where '  + @target+ ' Like ''' +@prefixText +'%'' ORDER BY ' + @target+ ' Asc'
--print @Sqry
Exec(@sqry) 
end

use variables in where,Orderby,Like in SqlServer

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0