[dba-SQLServer] Viewing and Modifying stored procedures

Hi Ron,

Thanks for this.  In SQL Server 2005 Management Studio right click
Properties on a SP brings up a different kind of window - similar to the
Properties window of the database itself, showing permissions,
encryption info, etc.  It is the right click > Modify option that brings
up the SP with an ALTER key word - an example is a right click > Modify
on the SP dbo.addUserQueue will bring up a query editor window
containing the following:

ALTER  PROC [dbo].[addUserQueue] @userid varchar(12),
  @Queueuserid varchar(12)
  as

    if (select count(UserID) from UserQueues where userID        
           = @UserID and QueueUserID = @QueueUserID) = 0
	Begin
  	 Insert into UserQueues (UserID,  QueueUserID)
  		  values (@UserID, @QueueUserID)
	End

I may be wrong, but I don't think that ALTER is a part of this SP, but
is there so that you can make any modifications to the SP and then
Execute the script (if that is the right word for it), in order to make
the changes necessary to the SP.

It is a conceptual shift that I need to make.  I was not looking for a
QBE grid, but more of how a module is edited in Access - in an editing
window with a Save command.  I now think I've got it that for SQL Server
SPs I need to Execute an ALTER command to modify the stored procedure,
and if I just want to take a look at the SP's functionality I have to
right click > Modify which brings up the SP along with this ALTER key
word, ready to be modified and executed.  When I am done looking I just
close the window without saving.

Thanks for the help,

Jennifer



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ron
Allen
Sent: Wednesday, June 27, 2007 12:49 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Viewing and Modifying stored procedures


Hi Jennifer,

I am still on SQL Server 2000, using Enterprise Manager and Query
Analyzer, 
but understand that all of that functionality is built into 2005 just
named 
differently.

You are correct that the way to edit stored procedures in SQL server is 
through T-SQL using the CREATE or ALTER syntax. There is no Access-like
query 
builder that lets you build a query visually. Whether this is good or
bad or 
right or wrong can be debated endlessly, and there are what I consider
valid 
arguments on both sides. 

That said, in SQL Server 2000 there are at least two ways to simply see
the SP 
code. You can edit the SP in Query Analyzer or you can select
"properties" for 
the SP in Enterprise Manager. In either case, you will also see the
"ALTER" 
or "CREATE" portion of the syntax because that is a part of the syntax
of the 
SP. It isn't a "template", which I'm thinking may be one of the places
you 
need to make a conceptual shift.

Don't get discouraged, because a vast number of things that you learned
in 
Access will be useful when working with SQL Server, but usually only in
that 
it makes things familiar not because there is a direct correlation on
the 
nitty gritty details. 

Ron




> That makes complete sense, but it doesn't work :(  I read somewhere 
> that the Query Analyzer in previous versions of SQL Server is no 
> longer a part of SQL Server 2005.  I am not sure, but have a feeling, 
> that this missing Query Analyzer is the reason I can't do what I want 
> to do. Apparently, unless you use T-SQL to edit stored procedures, the

> only other way they can be edited is through Visual Studio.  It can't 
> be done directly in what is now called SQL Server Management Studio.  
> It appears I need to learn how to navigate Visual Studio 2005 as well 
> as SQL Server 2005.
> 

> >
> >Thanks Robert.  It is sinking in that this is the way it works in SQL
> >Server.  Even if I just want to see the stored procedure so that I
can 
> >understand what it does (since I inherited this database), I have to 
> >generate this ALTER PROC template and then close the window without 
> >saving.  Seems strange that I just can't take a look at the stored 
> >procedure code, but I guess that's the way it is.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com