?
Solved

dynamic  sql

Posted on 2009-04-28
16
Medium Priority
?
194 Views
Last Modified: 2013-12-17
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 + "'";
0
Comment
Question by:Mr_Shaw
16 Comments
 
LVL 7

Assisted Solution

by:orcic
orcic earned 225 total points
ID: 24250771
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 + "'";
0
 
LVL 26

Accepted Solution

by:
Anurag Thakur earned 825 total points
ID: 24250787
>>'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 + "'";
0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 225 total points
ID: 24250819
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.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:Mr_Shaw
ID: 24250837
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'''
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24250861
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
0
 

Author Comment

by:Mr_Shaw
ID: 24250873
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.  
 

0
 

Author Comment

by:Mr_Shaw
ID: 24250897
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.  
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24250942
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;
i=500;
j=100;
k=200;
string sql = string.empty;
sql += "speciality = '" + i.ToString() + "' or Speciality = '" + j.ToString() + "' or Speciality = '" + k.tostring() + "'";

somewhere your quotes are getting messed up
0
 

Author Comment

by:Mr_Shaw
ID: 24251002

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.
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24251063
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)
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 225 total points
ID: 24256710
>>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.
0
 

Author Comment

by:Mr_Shaw
ID: 24258417
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'

0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24259219
you wont be able to make it work the way you are trying to
try that in sql first
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24259222
0
 

Author Comment

by:Mr_Shaw
ID: 24259412
yes it is simlar.

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

0
 

Author Closing Comment

by:Mr_Shaw
ID: 31575451
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).
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question