|
Please excuse my ignorance but I'm new to T-SQL and am having a problem. I have a proc that I want to send three parameters too, but only have it search on the paramters if they are valid (above 0).
In the following example I am filtering on County, Town and Type. But only want to filter if the value of the parameter is included. So if they only pass in county, then thats all I want to filter on.
Any ideas?
<br />
CREATE PROCEDURE FeatureBusinessByTown @County int, @Town int, @Type int AS<br />
<br />
Select Top 5 BusinessID<br />
FROM SELECT TOP 5 fbisBusinessLookup.BusinessID<br />
FROM fbisBusinessLookup INNER JOIN<br />
DIRECTORY ON fbisBusinessLookup.BusinessID = directory.[id]<br />
WHERE <br />
<br />
IF (@county > 0) THEN "(fbisBusinessLookup.countyid = @County)"<br />
IF (@Town > 0) THEN " AND fbisBusinessLookup.TownID = @Town"<br />
IF (@Type>0) THEN " AND fbisBusinessLookup.TypeID = @Type"<br />
<br />
ORDER BY NEWID()) DERIVEDTBL<br />
GO<br />
Definitely a PEBCAK! (Problem Exists Between Keyboard And Chair)
www.FruitBatInShades.com
|
|
|
|
|
You can do this with clever use of AND/OR clauses
Select Top 5 BusinessID
FROM SELECT TOP 5 fbisBusinessLookup.BusinessID
FROM fbisBusinessLookup INNER JOIN
DIRECTORY ON fbisBusinessLookup.BusinessID = directory.[id]
WHERE ((@country <= 0) OR (@country > 0 AND fbisBusinessLookup.countyid = @County))
AND ((@Town <= 0) OR (@Town > 0 AND fbisBusinessLookup.TownID = @Town))
AND ((@Type <= 0) OR (@Type > 0 AND fbisBusinessLookup.TypeID = @Type))
ORDER BY NEWID()) DERIVEDTBL
Note, the above doesn account for nulls in @country, @Town or @Type.
You can also declare a large enough varchar and concatenate a SQL command together then use EXEC(@sqlString) However, if you can do without that then I'd recommend it because there is a security risk in building a SQL command and executing it dynamically.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
You can't use IF or CASE statements in a WHERE clause with SQL Server 2000.
I would follow the advice posted below and store the query contents in a varchar, @SQL_STATEMENT. When you get the the WHERE clause, you can concatenate the onto the WHERE clause your criterion. To get around having to include logic to decide wether to include 'AND', build the query with ... WHERE 1=1. Then concatenate 'AND X = @X' IF @X > 0.
After this, you can do an exec(@SQL_STATEMENT). I don't see anything wrong with this as the query optimizer will still be able to optimize the query. THe only thing you have to be careful of with these is that the calling code doesn't allow any SQL to make it into the dynamic stored proc. Asp.net will do this for you unless you turn it off. It's an @Page directive called ValidateRequest I think...
Good luck
|
|
|
|
|
Hello all,
I'm a newbie in C#. For testing I use access as database.
I have defined a dataset with a table. This table is used as datasource for my datagrid. On the table I have defined two eventhandlers for RowDeleted and RowChanged. Every time a row in the grid is added, deleted or changed the event is triggered.
In the Eventhandlers I create the Insert-, Update- and Delete-Commands for my DataAdapter and then I call the DataAdapter.Update(dataTable)-method.
The deletes and updates work fine. But Inserts have not an effect to the database. When I ask my dataset for Changes after an insert with(dataset.hasChanges()). I don't get any changes.
Can anybody help me with my problem?
Thanks in advance,
Frank
|
|
|
|
|
I have two tables : "tblCustomers" and "tblAddresses". Between these two tables there excists a one to many relation, based on the the field "CustomerId", which is a primary key in the table "tblCustomers" and a foreign key in the table "tblAddresses"
Both of them are loaded in a dataset "ds" when loading my Data Entry Form "Customers". The Entry form has besides the neccessary textboxes to enter the data for the table "tblCustomers" also a datagrid "grdAddresses" . This table must contain all the addresses from the customers. Some customers can have more then one addresses ( e.g. one home-address and two or more delivery-addresses.) There is a button "Addresses" on the form, when clicked, popup another data entry form, into which i must can enter the addresses for the current customer. Once a address is entered, these data must be shown in the datagrid "grdAddresses" on the main form "Customers". However, these datagrid may not display all the fields that excists in the table "tblAddresses".( e.g. "customerID" , because this data is already shown in the main form. I can't get it done to realize this.
It is possible if i make the following settings for the datagrid:
grdAddresses.Datasource = "tblAddresses", and then make the neccesary settings in the property window.
But in order to have a synchronisation between de data in the main form and the data in the datagrid i must have the following settings:
grdAddresses.DataSource : ds
grdAddresses.DataMember = "tblCustomers.tblAddresses"
With such a settings i can't make it done.
Can somehelp me ?,
|
|
|
|
|
I'm not exactly sure on this as I haven't done datagrid binding for some time.
You probably can create a DataView based on your DataSet which contains only specific columns you want, and then bind the grid to the DataView.
As far as I know, this will work for reading purposes only (not if you want to add a new record to the DataSet).
You'll have to edit the DataSet through code (which seems to be what you're doing) and once it's edited the changes will be reflected to the DataView and therefore the grid.
Does it help?
Edbert P.
Sydney, Australia
|
|
|
|
|
Hi All
I am going mental trying to insert some data from a webform thru ado.net by use of a sproc.
Heres my code:
dim sqlConn as new sqlConnection(data......)
dim sqlda as new sqldataadapter("storedprocedure", sqlconn)
dim sqlds as new dataset
sqlda.insertcommand.commandtype=commandtype.storedprocedure
sqlda.insertcommand.parameters.add("@EmpId", control.text)
sqlda.Insertcommand.executenonquery()
sqlconn.close()
---------------->
The code compiles with no errors I run the project seems like it does something but does not write to the database. I have verified the stored procedure works via query analyser so I tried google but amazingly there is not alot..
Please help me im in programmer hell..
Thanks
-- modified at 5:47 Wednesday 26th October, 2005
|
|
|
|
|
You do not need to use SqlDataAdapter if you are not updating your data through a DataSet.
Create an SqlCommand instead, specify the connection, the sql string, and the parameters.
Then execute the SqlCommand and you'll have your data updated.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
Thanks Ed. Just did it now... Much simpler and it worked better yet...
Thanks
Dom
|
|
|
|
|
In Microsoft Access there are so-called "Modules" that contain VBA code.
In the Microsoft Access application this is easy to do; simple click the "New" button and the module is created, launching the VBA-editor.
Now I want to add a new module to thousands of MDB files that are already shipped to our customers. (In my example: a simple function to enable Regular Expressions from whithin SQL queries).
For adding new tables, the task is simple: I add the CREATE TABLE SQL statments to my C++ app, deployed it to our customers and executed the SQL statments when they first start the application.
Question: Is anyone aware of a way to add new modules programmatically?
Thanks
Uwe
--
Affordable Windows-based CMS for only 99 €: try www.zeta-producer.com for free!
|
|
|
|
|
Note to myself: This was a stupid stupid stupid question. Because the "Modules" are only available from within a query when executing that query inside the Microsoft Access application.
Otherwise the modules are not available (i.e. when executing the query via ADO from your own application).
I do not delete the original question, because maybe it will help someone someday 
--
Affordable Windows-based CMS for only 99 €: try www.zeta-producer.com for free!
|
|
|
|
|
Is there any way to query the Fiels name using T-Sql under SQA?
Note: DB base on SQL 2000
Thanks;)
|
|
|
|
|
using SQA type> EXEC sp_columns
Definition: :-DReturns column information for the specified tables or views that can be queried in the current environment.
|
|
|
|
|
Use INFORMATION_SCHEMA.COLUMNS, for e.g.
Select * from INFORMATION_SCHEMA.COLUMNS
Also, see INFORMATION_SCHEMA.* in BOL.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
I am trying to run a query that sums up all the quantities in a table. Brief over view of how it works
1.) select all pertinant account ids into a temp table.
2.) select sum(quantity), fn_userFunc(account) from orders where account in temp table.
This produces the correct answer but it calls the fn_userFunc almost 100 times when I want it called once. Is there a way to run this in SQL without calling fn_userFunc every time. I can split the query into parts and combine the results but I wondered if there was a better way to do this.
Mark Jackson
|
|
|
|
|
By definition, UDFs are called for as many times as there are rows in your query. What exactly are you trying to compute in your UDF? If your function works on fields that are from 'orders' there is no luck.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
the function doesn't actually depend on the value of any row in the query but rather on the sum. It sounds like I should split the work into a query summing the rows and another calling the function then returning the two fields.
Thx
Mark Jackson
|
|
|
|
|
Use nested query. Something like,
Select sum_of_data, your_function(sum_of_data)
From (Select sum(...) as sum_of_data from orders Where ....) as Temp_Results
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hii
i need to get a list of all the database in the sql server 2000.
Thanks
Utsav
|
|
|
|
|
Connect to the master database and run the following:
SELECT name FROM sysdatabases
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I'm developing a multi-threaded application that communicates with SQL Server via ADO. The application is written in MFC and is being tested on WindowsXP SP2. The problem I'm having is that the following function call will hang the application intermittently and I'm not sure why: Recordset15::Open (part of the ADO API). More specifically, it seems to happen deep within the raw_Open call. Although there are multiple threads running, I've made it so that only the one thread is actually communicating with the ADO database. And, besides, I was under the assumption that the ADO was a thread-safe API. Also, I'm not sharing connections across different threads.
Any ideas?
Thanks,
Karim Shehadeh
http://www.iwonderdesigns.com
|
|
|
|
|
I need to retrieve the last inserted row for unique set of columns
Got a table with
AutoIncrement , Position , status , dateTime
where position is a nr form 1 to 20 and status a nr from 1 to 50
The table get updated continuously and I need to now the last status for every position
|
|
|
|
|
select * from ctm group by AutoIncrement having Position = max(Position) and Status = Max(Status)
should do it.
|
|
|
|
|
select * from mytable where myColumn = @@IDENTITY
see here[^] for more details
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
Imagine two table
One Table (ForeignCurrency)
FC_ID FC_TYPE
1 USD
2 EUR
3 YTL
Other table (Rates)
FCV_ID FCV_RateIN FCV_RateOUT FCV_DATE
1 1 1 10/13/2005
2 1.345 1.346 10/13/2005
3 1.651 1.654 10/13/2005
I need to join two table in a one query. I do this so
Select FC.FC_TYPE,ISNULL(FCV_RateIN,1) FCV_RateIN,ISNULL(FCV_RateOut,1) FCV_RateOut from ForeignCurrency FC LEFT OUTER JOIN Rates R ON FC.FC_ID=R.FCV_ID WHERE FCV_Date='10/13/2005'
This give me result as i want. But when i need 10/14/2005 rates it give me empty result set , but i expect
FCV_TYPE FCV_RateIn FCV_RateOUT
YTL 1 1
USD 1 1
EUR 1 1
How can i re-write code ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
