TSQL query with stored procedure

Question

Post reply

TSQL query with stored procedure

rizwan_usa

Old Hand

Points: 334
More actions
October 20, 2007 at 11:26 pm

#176403

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

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

rizwan_usa Old Hand Points: 334 More actions · Answer 2

Hi Ramesh

Really appreciate your help.. helped me with the exact logic I was searching for...

Thank You

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

I'm curious... what did you need this for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rizwan_usa Old Hand Points: 334 More actions · Answer 4

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

varun-jha SSC Veteran Points: 214 More actions · Answer 5

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

rizwan_usa Old Hand Points: 334 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rizwan_usa Old Hand Points: 334 More actions · Answer 8

Hi Jeff,

U really truncate unnecessary stuff,,,,

Thanks for the effort