[dba-SQLServer] Viewing and Modifying stored procedures

Jennifer,

What you are seeing is the script that will allow you to make modifications
and when you run it, it will save your mods to the existing sproc. When you
create a new sproc it will say CREATE instead of ALTER. 

The general format is..

CREATE objectype objectname objectparameters
AS

<everything in between is your SQL code>

END 

If you simply want to look at the code that's how you do it. If you don't
click the execute icon it will simply stay as it is. SQL server's user
interface takes a while getting used to as it was the case when Microsoft
Access first came out. A paradigm shift into object-oriented, event-driven,
point and click from the usual UI was necessary. In essence, don't count too
much on point and click operations in SQL server because you will be
expected to know and learn SQL syntax.

Just ask John Colby...he knows what's involved...LOL.

Eric

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jennifer
Gross
Sent: Wednesday, June 27, 2007 1:13 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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.
_______________________________________________