October 20, 2007 at 11:26 pm
Hi everyone,
Any help will be really appreciated regarding this post.
I need to create a stored procedure which would accept 1 single character as input and output is the following:
If I type 'u' it should display all user defined database
If I type 'v' it should display all views
If I type 'w' it should display all stored procedures
I was told to seek help with sys.objects -- system database
I am using SQL Server 2005 and need to deploy this logic in T-SQL
Hope a pro can really advice!!
Thank You
Rizwan
October 21, 2007 at 6:00 am
You can do something like this in your SP definition....
DECLARE @sInputType VARCHAR(1)
DECLARE @sOutputType VARCHAR(1)
SET @sInputType ='u'
SET @sOutputType = ( CASE @sInputType WHEN 'U' THEN 'U' WHEN 'V' THEN 'V' WHEN 'W' THEN 'P' ELSE '' END )
IF ( @sOutputType = 'U' )
BEGIN
SELECT * FROM sys.databases WHERE name NOT IN( 'master', 'msdb', 'tempdb', 'model' )
END
ELSE
BEGIN
SELECT * FROM sys.objects WHERE type = @sOutputType
END
--Ramesh
October 22, 2007 at 4:27 pm
Hi Ramesh
Really appreciate your help.. helped me with the exact logic I was searching for...
Thank You
October 22, 2007 at 9:24 pm
I'm curious... what did you need this for?
--Jeff Moden
October 23, 2007 at 2:49 am
Hi Jeff ,
Nice hearing from you. Well about your concern, I am training with IT professional firm on Sql Server 2005 and though somewhat irrelevant, they wanted me to do this as one of the assignments.
Nothing particular :hehe:
Catch you'll later
October 23, 2007 at 10:45 pm
I JUST COMPLETE THE PROC GIVE BY RAMESH
CREATE PROC PRCSYSOBLECT @sOUTputType VARCHAR(1)
AS BEGIN
IF ( (@sOutputType = 'U') OR (@sOutputType = 'u'))
BEGIN
SELECT * FROM sys.databases WHERE database_id>6
END
ELSE if( (@sOutputType = 'V') OR (@sOutputType = 'v') )
BEGIN
SELECT * FROM sys.all_views
END
ELSE if( (@sOutputType = 'W') OR (@sOutputType = 'w') )
BEGIN
SELECT * FROM sys.procedures
END
ELSE
PRINT 'INSERT VALUE DOES NOT MEET THE cONDITIONS'
END
October 24, 2007 at 5:32 am
Hi Varun,
I already developed the same logic with Ramesh's code. However I would like to thank you for taking time and adding more to the logic.
Thank You
October 24, 2007 at 7:35 pm
By default, SQL Server is not case senstive... you would have had to setup case sensitity when you setup the server or made a change to the who server after setup. Also, no need for BEGIN/END (most consider it a good practice to use them, though) if only one statment follows IF, ELSE IF, or ELSE... so, you could boil the code down a bit...
[font="Courier New"]CREATE PROC PRCSYSOBLECT @sOUTputType VARCHAR(1)
AS BEGIN
IF @sOutputType = 'U' SELECT * FROM sys.databases WHERE database_id>6
ELSE IF @sOutputType = 'V' SELECT * FROM sys.all_views
ELSE IF @sOutputType = 'W' SELECT * FROM sys.procedures
ELSE PRINT 'INSERT VALUE DOES NOT MEET THE CONDITIONS'
END[/font]
... or not... both work fine...
--Jeff Moden
October 25, 2007 at 6:19 am
Hi Jeff,
U really truncate unnecessary stuff,,,,
Thanks for the effort
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply