July 15, 2011 at 5:36 am
Hello.
I'm trying to do a function that returns a table, but I'd like to be able to send a parameter with a null or 0 value and when the function get this null parameter it doesn't filter by it.
For example:
CREATE TABLE test ( id INT, date DATETIME, company VARCHAR(100))
INSERT INTO test ( id, date, company)
VALUES ( 1 ,'2010/01/01', 'Company 1' )
INSERT INTO test ( id, date, company)
VALUES ( 2 ,'2011/01/01', 'Company 2' )
INSERT INTO test ( id, date, company)
VALUES ( 3 ,'2011/02/01', 'Company 3' )
INSERT INTO test ( id, date, company)
VALUES ( 4 ,'2012/01/01', 'Company 4' )
INSERT INTO test ( id, date, company)
VALUES ( 5 ,'2013/01/03', 'Company 5' )
INSERT INTO test ( id, date, company)
VALUES ( 6 ,'2011/01/01', 'Company 6' )
CREATE FUNCTION fnTest ( @year INT, @month INT, @company VARCHAR (100))
RETURNS @table TABLE
(
ID INT,
DATE DATETIME,
COMPANY VARCHAR(100)
)
AS BEGIN
INSERT INTO @table
SELECT * FROM test WHERE YEAR(date) = @year AND MONTH(date) = @month AND company LIKE @company
RETURN
END
If I use this command:
SELECT * FROM fnTest(2011,1,'Company 2') It'll bring one register, but if I want all registers from that year and month and I try to send SELECT * FROM fnTest(2011,1,null) or SELECT * FROM fnTest(2011,1,'') It doesn't bring any registers, because It'll try to filter by the Company's name as well.
I was able to "workaround" this by doing that.
SELECT * FROM fnTest(2011,1,'%%')
But I would like to be able to do this with INT fields and to know if is there any better way to do this.
Thanks a LOT!
July 15, 2011 at 8:02 am
You could just change your select line like this.
SELECT * FROM test WHERE YEAR(date) = @year AND MONTH(date) = @month AND company LIKE '%' + isnull(@company, '') + '%'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2011 at 8:05 am
err...that won't actually get what you need. :blush:
something like this should work though.
if @company is null
SELECT * FROM test WHERE YEAR(date) = @year AND MONTH(date) = @month
else
SELECT * FROM test WHERE YEAR(date) = @year AND MONTH(date) = @month AND company LIKE '%' + isnull(@company, '') + '%'
This is getting close to a "catch all" query. If you find that you need to extend this further you should read Gail's blog post here[/url] about catch all queries.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2011 at 10:03 am
Thanks a lot for your answer.
The idea of putting the IF is nice, but my SQL is gigantic than it'll be hard to do this.
The blog's idea it's very nice, I can't use the improved catch-all idea (sp_executesql doesn't work inside functions, but the typical example appears to solve my problem).
CREATE PROCEDURE SearchHistory
(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)
AS
SELECT ProductID, ReferenceOrderID, TransactionType, Quantity,
TransactionDate, ActualCost from Production.TransactionHistory
WHERE (ProductID = @product Or @product IS NULL)
AND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)
AND (TransactionType = @TransactionType OR @TransactionType Is NULL)
AND (Quantity = @Qty Or @Qty is null)
GO
Thanks a LOT!
July 15, 2011 at 11:50 am
we have a database with queries almost identical to the above. it's a nightmare a few times a year because when there is a big change in data all the indexes go to hell and have to be recreated.
i asked the developers about all the null values in the tables and they said they did it on purpose to serve as data
July 15, 2011 at 11:59 am
alen teplitsky (7/15/2011)
we have a database with queries almost identical to the above. it's a nightmare a few times a year because when there is a big change in data all the indexes go to hell and have to be recreated.i asked the developers about all the null values in the tables and they said they did it on purpose to serve as data
I've worked on some systems where the idea that null indicates something was used and it is nothing but a complete pain. both on the db side and the code to have to fiddle around with stuff being null meaning something. Such a bad concept. I feel your pain.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply