dynamic sql

SELECT     *
FROM         dbo.tbl1
Where Specialty = '502' or Specialty = @SpecifiedSpecialty

I am trying to make this query dynamic by using a parameter.

For example @SpecifiedSpecialty might equal

'503' OR Specialty = 138

Thereby making the total query

      SELECT     *
FROM         dbo.tbl1
Where Specialty = '502' or Specialty = '130' OR Specialty = 138

However, when I run sql profiler to what is actually being passed to the query I get

'502 or Specialty = 130'

Here is my c# code which sets the parameter values.

   Session["specialty_multiselect"] = Session["specialty_multiselect"].ToString() + " or Specialty = " +  itemValue + "'";
Who is Participating?
Anurag ThakurConnect With a Mentor Technical ManagerCommented:
>>'502 or Specialty = 130'
the above line clearly states that the string is not correct because i think you actually want '502' or Specialty = '130'

try the line below (i am not sure what the session variable contains but if you can share hwo you are creating the dynamic query it might be better)
Session["specialty_multiselect"] = Session["specialty_multiselect"].ToString() + "' or Specialty = '" +  itemValue + "'";
orcicConnect With a Mentor Commented:
try this (added ' character after Specialty = ") according to output string you should fix the previous command too that sets '502 -> you should add ' char to the end
Session["specialty_multiselect"] = Session["specialty_multiselect"].ToString() + " or Specialty = '" +  itemValue + "'";
Daniel WilsonConnect With a Mentor Commented:
Building up a part of your WHERE clause in your C# is not the best practice if you can avoid it.

If you can't avoid it, dynamic SQL involves making your SQL create a string and execute it as SQL.  I usually do it in a stored procedure ... but believe this would work.

String SQL = "Declare @SQL nvarchar(4000);                           Set @SQL = 'SELECT     *FROM         dbo.tbl1Where Specialty =' + @WhereClause ;Exec (@SQL)";

Then when you set your SqlCommand's parameters, @WhereClause is the parameter to set.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Mr_ShawAuthor Commented:
I ran sql profile for both of your suggestions
for ragi0017: I got  

N' ''130'''' or Specialty = ''320'''

For orcic: I got

 N'''130'' or Specialty = ''320'''
Anurag ThakurTechnical ManagerCommented:
share you dynamic where clause creation part
it will be much better to have a look there and then we might be able to help quickly
Mr_ShawAuthor Commented:
hi DanielWilson,

My sql is in a stored procedure, but I need to make my where clause more dynamic so that I can vary the number of 'OR' statements. This Coincides  with the number of selections a user makes from a asp listbox.  

Mr_ShawAuthor Commented:
Hi ragi0017,

My sql is semi-dynamic. The rest is hardcoded in an stored procedure. I only need to make my where clause more dynamic so that I can vary the number of 'OR' statements. This Coincides  with the number of selections a user makes from a asp listbox.  
Anurag ThakurTechnical ManagerCommented:
there is something wrong here - Session["specialty_multiselect"].ToString()

because if i say that i need to make a dynamic where clause then i will do it something like this

int i, j, k;
string sql = string.empty;
sql += "speciality = '" + i.ToString() + "' or Speciality = '" + j.ToString() + "' or Speciality = '" + k.tostring() + "'";

somewhere your quotes are getting messed up
Mr_ShawAuthor Commented:

Here is my stored procedure

SELECT  * FROM  dbo.tbl1 Where Specialty = @Specialty;

I have a listbox on my website which allows for multiselect.

The stored procedure parameter @Specialty is based on the selection from the user on the web site.

Therefiore I was trying to write c# code which would allow me to dynamically pass in values to build up a 'OR' statement.

The sessiom variable is a long story. But in short it helps me with a foreach loop which I have as part of the multiselect.
Anurag ThakurTechnical ManagerCommented:
the logic you are trying to build will not work
i just tried that in sql and it didnt

here are what i tried in sql

the following did not work
declare @sql varchar(200)
select @sql = '''10001''' + 'or locaitionid = ''10002'''
select * from m_location where locationid = @sql

this one worked
declare @sql varchar(200)
select @sql = 'select * from m_location where locationid = '
select @sql = @sql + '''10001''' + 'or locationid = ''10002'''
execute (@sql)
Anthony PerkinsConnect With a Mentor Commented:
>>My sql is semi-dynamic.<<
That is like saying you are semi-pregnant.  You either are using Dynamic SQL in which case you are subject to bad performance and lousy security (read SQL Injection) or not.
Mr_ShawAuthor Commented:
I have a stored procedure which is

Select * from table1 where spec = '103 or spec = @spec;

where the parameter is I intend to extend the SQL by injecting extra OR's

for example @spec could equal '107' or Spec = '302'

Anurag ThakurTechnical ManagerCommented:
you wont be able to make it work the way you are trying to
try that in sql first
Anurag ThakurTechnical ManagerCommented:
Mr_ShawAuthor Commented:
yes it is simlar.

I posed it as another question using different expression since I felt i might be confusing people.

Mr_ShawAuthor Commented:
Thanks for everydoys help. The problem was solved by creating a function SQL server. The function takes the string, for example, '1,2,3' and seperates the values from the commas and puts them into a temp table. The I use the contents of the temp table to as part of my query for example where in  (select * from #temp).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.