March 4, 2014 at 10:44 am
Hai All
I have written a stroed procedure where I pass a parameter called category. If the category does not exits it returns 0 result set i.e it returns only the columns but not values.
I want to show " There is no such category exists "
How can I achieve this.
Thank you in Advance.
Kind Regards
Shaik Mussarath
March 4, 2014 at 11:10 am
Here is a possible option
USE tempdb;
GO
DECLARE @reccount INT = 0
CREATE TABLE #sometab (someid INT IDENTITY(1,1),someval VARCHAR(500),Category VARCHAR(20))
INSERT INTO #sometab (someval,Category)
VALUES (
'Charlie McDaniels', -- someval - varchar(500)
'RandomNames' -- Category - varchar(20)
)
SELECT @reccount = COUNT(*)
FROM #sometab
WHERE Category = 'Unknown'
IF @reccount = 0
BEGIN
SELECT 'Unknown Category'
END
ELSE
BEGIN
SELECT *
FROM #sometab
WHERE Category = 'Unknown'
END
DROP TABLE #sometab;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2014 at 12:39 pm
Or you could use shortcut this a little bit using aggregates.
select min(someval) as someval,
isnull(min(Category), 'No such Category') as Category
from #sometab
where Category = 'Unknown'
It kind of depends on if you need to have the same columns or not.
_______________________________________________________________
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/
March 4, 2014 at 12:42 pm
That query would work well.
I have seldom seen this kind of requirement. When I have, it is something we throw in the presentation layer. A common place for this type of thing is in SSRS reports.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2014 at 12:49 pm
SQLRNNR (3/4/2014)
That query would work well.I have seldom seen this kind of requirement. When I have, it is something we throw in the presentation layer. A common place for this type of thing is in SSRS reports.
Yeah me too. This sounds like when a datagrid has no rows or in a report. Either way it would generally be done in the front end instead of in sql.
_______________________________________________________________
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/
March 4, 2014 at 6:41 pm
Thanks for the set up data Jason.
I believe you can also do something like this:
CREATE TABLE #sometab
(
someid INT IDENTITY(1,1)
,someval VARCHAR(500)
,Category VARCHAR(20)
)
DECLARE @Category VARCHAR(20) = 'RandomNames';
INSERT INTO #sometab (someval,Category)
VALUES (
'Charlie McDaniels', -- someval - varchar(500)
'RandomNames' -- Category - varchar(20)
)
SELECT someval = ISNULL(b.someval, NullVal)
FROM (SELECT 'No such category exists') a (NullVal)
LEFT JOIN #sometab b ON b.Category = @Category;
GO
DROP TABLE #sometab;
[O-O-BOX]No charge for grammar correction on the message.[/O-O-BOX]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 5, 2014 at 2:21 am
Hi All
Thank you for your immediate reply. Let me try with all above examples.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply