|
Can anyone comment on the performance pros/cons of using table-valued functions vs. Stored Procedures in SQLServer? Specifically:
1) are the execution plans for the functions stored as they are in stored procedures?
2) When using table variables inside the function, does the entire result set automatically wind up in memory, or can it be streamed using a reader?
Thanks for any input.
|
|
|
|
|
Does anyone know if there is an opposite to the SQL "IN" statement?
I have a string of values that I want to check the database for.
However if I use an IN statement it uses "OR". I want to be able to use an IN statement that uses "AND"..
Is there anything out there?
Thanks and sorry for the trivial question.
Dave
|
|
|
|
|
The opposite of "IN" would be "NOT IN", but I don't think that is what you are after in this case.
I don't understand your question wrt. "AND". If I query a column called "NAME", I can have returned all names IN ("MIKE", "CHRIS", "DON"). But there is no way any data item will ever be equal to all three of those values, which is what you are asking for when you say you want an "AND" condition.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
It sounds like "IN" is what you want. If I have a list of values, i.e. ('Apple', 'Orange')
and I want to know if they are in the FRUIT table, I would write:
SELECT Name
FROM FRUIT
WHERE Name IN ('Apple, 'Orange').
This would pull back all values from the fruit table that are in my list.
However, there is no logical way to use this with an "AND", because a string can only match one value.
If you're looking for the value 'AppleOrange' you might use the LIKE operator:
SELECT Name
FROM FRUIT
WHERE Name LIKE '%Apple%'
This would pull back values "Apple" and "AppleOrange"
I don't think there's a way to use the LIKE operator with a list though.
Hope that helps a bit.
|
|
|
|
|
Hi Dave,
I think u want to select the rows if table contains all the values in your IN statement otherwise no row should be returned. If this is the case then unluckily there is no direct way to cope with this but fortunately you can accomplish this by SP.
for example (pseudoCode)
1. variable boolean allValues
2. loop for all value in your IN statment
3. Variable rowcount = select count(*) from table where column = 'your value'
4. if rowcount = 0 then allvalue = false and exit loop else continue with the loop
5. end loop
6. if allValues = false returned no row else returned allrows for the values in your IN statement.
Hope that works if this is what you are trying to explain
regards
Saud
saud
|
|
|
|
|
If someone could give me some guidance with the following problem. I have a table; tblItems; which fields fldItem(nvarchar 75), fldNSN(nvarchar 16), fldPartNum, fldVendor, fldPrice... I would like to have a stored procedure that can search and return a dataset based upon which parameter the user enters. I thought if I created a procedure like this, it would work but I keep getting some error in building the procedure Incorrect Syntax Near the Keyword 'Select' or Invalid operator for datatype. Operator equals modulo, type equals varchar;
Create Proc usp_SearchItems @pName nvarchar(75), @pNSN nvarchar(16), @pTPart nvarchar(20), @pVPart nvarchar(50) AS Declare @pQuery nvarchar(1024)
if @pName<>''
set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldItem Like '%' + @pName + '%'; Order By fldItem
if @pNSN<>''
Set @pQuery=Select * From tblItems Where (fldInactive=0) and (fldNSN Like '%' + @pNSN + '%';
exec @pQuery
I don't have a problem if I use Create Proc usp_SearchItem @pName nvarchar(75) As Select * From tblItems Where (fldInactive=0) and fldItem Like '%' + @pName + '%'
I'm trying to use one procedure instead of four.
Any assistance would be greatly appreciated.
|
|
|
|
|
Try the following:
CREATE PROCEDURE usp_SearchItems
@pName NVARCHAR(75),
@pNSN NVARCHAR(16),
@pTPart NVARCHAR(20),
@pVPart NVARCHAR(50)
AS BEGIN
DECLARE @pQuery NVARCHAR(1024)
SET @pQuery = 'SELECT * FROM tblItems WHERE (fldInactive = 0)'
IF (@pName<>'') BEGIN
SET @pQuery = @pQuery + ' AND (fldItem LIKE ''%' + REPLACE(@pName, '''', '''''') + '%'')'
END
IF (@pNSN<>'') BEGIN
SET @pQuery = @pQuery + ' AND (fldNSN LIKE ''%' + REPLACE(@pNSN, '''', '''''') + '%'')'
END
SET @pQuery = @pQuery + ' ORDER BY fldItem'
--PRINT @pQuery
EXEC(@pQuery)
END
If you have any problems then uncomment the "PRINT" statement to find out what the final contructed query was.
The "REPLACE" function is being used to protect the stored procedure against quote characters. Do a search for "SQL Injection Attacks" to find out more about this problem.
Regards
Andy
|
|
|
|
|
You could use something like this. Default the parameters to null then coalesce or inull the parameter. The example proc below returns one result set but if you wanted to you could just as easily return one result set per parameter passed in.
Create Proc usp_SearchItems
@pName nvarchar(75) = null,
@pNSN nvarchar(16)= null,
@pTPart nvarchar(20)=null,
@pVPart nvarchar(50) =null
AS
Select *
From tblItems
Where (fldInactive=0)
and fldItem Like '%' + coalesce( @pName , fldItem) + '%'
and fldNSN Like '%' + coalesce( @pNSN , fldNSN)+ '%'
....
|
|
|
|
|
Can anyone help me why "N" is used before any property (eg. "IsUserTable")in OBJECTPROPERTY in Sql Server ?
|
|
|
|
|
It converts the string into a Unicode string.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Strictly speaking, th N' doesn't itself "convert" the string, but rather specifies that it should be converted (if needed) and stored/retrieved as a Unicode string by the server...
|
|
|
|
|
True. I should be more careful with my grammar.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
|
Hello,
I was hoping someone could give me a bit of information on the way I should be creating / handling my table adapters.
I have a persistant business object that is managing a number of database calls using a lot of different table adapters. The question I have is what the best practice is for creation of these table adapters. Is it better to persist a reference to each of my different table adapters over the life of my business object (i.e. create them all once, store them in class variables, then dispose of them all at the end) or is it better to create them as they are required (i.e a new table adapter created for each function call).
If someone could give a bit of an insight into what a table adapter is doing in terms of the connections created for it during instantiation that would be most helpful in helping me decide the most efficient approach.
Thanks for your help,
Adam
|
|
|
|
|
Since each creation of a new table adapter will require one or more network round trips to populate the date, it is better to persist a reference as long as the data contained is not likely to become stale rapidly. The trade off is that keeping populated table adapters around can consume a lot of memory if the tables are large.
|
|
|
|
|
You seem to be inferring that each instance of a table adapter stores the data from the table in the application. Are you certain this is correct as I assumed that data was only retrieved once the Select function of a table adapter was called in order to fill a data table object? (It is possible that my orginal message was not clear enough and you thought I was referring to data table objects rather than table adapters)
I always assumed that table adapters stored connection details for the appropriate database object - these are the resources I was hoping to find out about so that I can ensure my application is not leaving connections open inappropriately or unnecessarily hammering the database.
|
|
|
|
|
I'm not going to comment on Table Adapters as I don't use (like) them very much. However, you may want to look at using Query Analyzer to see what is happening with your connections (and queries). This tool is invaluable.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
NamelessParanoia wrote: ou thought I was referring to data table objects
Yes, I was confused...
Each call to Myadapter.Fill or Myadapter.Update will cause a network round trip. If you open the connection before calling Fill, then the connection will remain open, otherwise it is opened and closed for you. The data (and schema information) gets stored in the datatable/dataset.
|
|
|
|
|
 Hi Guy's
i am searching for the details about 'Fine Tuning of SQL-Query' can any one give me any good site or other details about this. I mean what steps are carried out to increase the performance of SQL Query? so the data will flow fluently please help me...
Thanks in advance
Sasmi
|
|
|
|
|
In SQL Server, you can use the Query Profiler. Also, you will want to look at the execution plan for your query.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi.can someone help me in writing code for retrieving records from database table.i should get the list of records when the page of my web aplication is loaded.
thanks in advance
|
|
|
|
|
Too vague. Have you attempted to search for a tutorial on ADO.NET?
|
|
|
|
|
How to Integrate SQL server 2005 Reporting Services into asp.net Application
|
|
|
|
|
hi,
what is the differance between connected and unconnected layer?
I read a book that tried to explain that but
I saw in the examples that in both layers
the commands are the same and you can retrieve data insert data
and so on.
so what is the differance?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)