conditionnal select

[mas_oyama]

i work on an access projet. i have to make a screen that will be used to execute some queries and that will show the results properly. Now i have to integrate another screen that was used to execute 3 specific queries. these queries had 3 view each, and now i have to incorporate 2 display types for each of these, so i'm at 18 queries(3 main queries * 3 views * 2 display types = 18 queries), and that makes no sense since all the queries would be practically the same. the way it was done in the past is that there was 3 queries, that searched for more than what the were to show, and then, depending on the view, some fields wer hidden or shown.but now its a whole another story: i dont work with the same type of control. Its not a subform like it was. now its a JanusGrid(an ActiveX control that the company i work for bought), and i dont think i can just go and hide some fields from that. and also, my screen my be exempted of any hard coding! For example, my controls are called txt1, txt2, cbo1, cbo2, txtDate1, txtDate2, etc. so that way, i can refer to these fields in my configuration table so i can associate them with the parameters of the queries and all. Anyway, its a great way to do what I have to do, and it works really really well.but like i said, now its a whole another story, cause since i cant just go and make a combobox that were specifically made for these specific queries, and i cant just hardcode what is supposed to happen when i change the selected item in my cboBox containing the views and the display types, i assume that all this would need to be done in my queries. so, on to the point. I would to do something like this, but in TSQL rather than in VBA:

dim strSQL as stringstrSQL = "Select"if cbo1 = view1 then 'cbo1 would be the combobox containing the views list for example   strSQL = strSQL & ...   ' some fieldselseif cbo1 = view2   strSQL = strSQL & ...   ' some other filedsend if ' and then execute the query

So i would like to do that, but in TSQL... is it even possible?

[pulpfiction]

Try this structure.....DECLARE @var datatypeIF @var=condition1BEGIN....... sql statement 1.........ENDIF @var=condition1BEGIN....... sql statement 2.........END

[mas_oyama]

Try this structure.....DECLARE @var datatypeIF @var=condition1BEGIN....... sql statement 1.........ENDIF @var=condition1BEGIN....... sql statement 2.........END

yeah, but the Select statement is pretty huge. also, if i that, i would have 3 select statements into 1 query and the 3 statements would be soo much similar... actually, doing that would be the same thing as splitting the 3 existing queries into the 18 queries i was talking about... what i want is to do some dynamic SQL( i just learned it does exists, but i dont know how... qnd sadly the tutorials on w3schools.com dont have any dynamic sql lessons)

[mas_oyama]

Ok thats alright. i think i found a front end solution... i'll try this out and i'll post again this afternoon to say if it worked or not

[Yahweh]

yeah, but the Select statement is pretty huge. also, if i that, i would have 3 select statements into 1 query and the 3 statements would be soo much similar... actually, doing that would be the same thing as splitting the 3 existing queries into the 18 queries i was talking about... what i want is to do some dynamic SQL( i just learned it does exists, but i dont know how... qnd sadly the tutorials on w3schools.com dont have any dynamic sql lessons)

Dynamic SQL isn't a T-SQL trick, and it can't be created with stored procedures. "Dynamic SQL" is just a fancy way of saying ad hoc SQL written in server side code on your website.The real advantage of server-side SQL come when you need to use a lot of user-selected options to create an SQL statement, specifically when you don't know what the WHERE condition will look like ahead of time, such as in a site search where a user has the option to search many different optional fields and sort the fields in any order.Here is an example of a site search in VB.Net which uses dynamically generated SQL queries:Backend code:

Private Sub Search (sender as object, e as eventargs) Handles Button.Click	Dim strSQL as String = _		String.Format(_					  "Select * from {0} [searchString] ORDER BY {1} {2};", _					  dropDownTable.Replace("'", "''"), _					  dropDownORDERBY.Replace("'", "''"), _					  dropDownSortOrder.Replace("'", "''") _					  )		'I'm using string.Replace("'", "'') to escape my SQL string, because		'otherwise its vulnerable to SQL injection	if txtSearchString.Length > 0 then		strSQL = Regex.Replace(strSQL, _							   Regex.Escape("[searchString]"), _							   String.Format("WHERE Title = '%{0}%', _											 txtSearchString.Replace("'", "''")), _							   RegexOptions.IgnoreCase)	else		strSQL = Regex.Replace(strSQL, _							   Regex.Escape("[searchString]"), _							   "", _							   RegexOptions.IgnoreCase)			end if	'Resulting string is a valid SQL string	Dim sqlComm As New SqlCommand(strSQL, sqlConn)	Dim r As SqlDataReader = sqlComm.ExecuteReader()	While r.Read()		'***		'***		'output all of your fields here, or bind it something on the page		'***		'***	End While 	r.Close()End Sub

Front end HTML:

<script runat="server">	<p>	Select a table to search:<br>	<asp:DropDownList ID="dropDownTable" runat="server">		<asp:ListItem Selected="True">Orders</asp:ListItem>		<asp:ListItem>Customers</asp:ListItem>		<asp:ListItem>Cats</asp:ListItem>	</asp:DropDownList>	</p>	<p>	Find a record containing:<br>	<asp:Textbox ID="txtSearchString" runat="server">	</p>	<p>	Sort results by:<br>	<asp:DropDownList ID="dropDownORDERBY" runat="server">		<asp:ListItem Selected="True">Title</asp:ListItem>		<asp:ListItem>Last Updated</asp:ListItem>		<asp:ListItem>ID</asp:ListItem>	</asp:DropDownList>	<asp:DropDownList ID="dropDownSortOrder" runat="server">		<asp:ListItem Selected="True">Ascending</asp:ListItem>		<asp:ListItem>Descending</asp:ListItem>	</asp:DropDownList>	</p>	<asp:Button runat="server" name="Button" text="Search"></script>

[mas_oyama]

oh well. you guys will have to wait until tomorrow to know if my front end solution is gonna work! I've been slowed down by something else(the way we are going to implement this, i had to convert the way i fill my treeview control with the procedures to a recursive procedure, it wasnt alredy recursive, and to do that, i had to change the stucture of 2 tables, so i took more than just 5 minutes!) and i we went to play badminton during lunch so i actully lost an hour and a half of work today! lol so i'll finish this tomorrow instead!