Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do edit stored procedure and save

Posted on 2006-11-27
3
Medium Priority
?
250 Views
Last Modified: 2008-02-01
I am running sql 2005 server.  I edited a stored procedure by Right clicking  dbo.usp_SelectAllProduct_NP  and selected "modify"

This allowed me to paste the additional code into the right hand window/pane, however when I try and save this it wants to save it a seperate file /query.    Is there something I don't understand?

am I not able to edit the original stored procedure? There must be a way to save my following stored procedure:

orginal stored procedure ====
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_SelectAllProduct_NP]
AS
SELECT
     [P_ProductID],
     [P_ProductName],
     [P_QtyPerUnit],
     [P_UnitPrice],
     [P_UnitsInStock],
     [P_UnitsOnOrder],
     [P_ReorderLevel],
     [P_WareHouseNo],
     [P_ModifiedDate],
     [P_MEMO],
     [P_Desc]
FROM
     [dbo].[Products]


My modified stored procedure =====
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


--region [dbo].[usp_SelectProductsAll]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   pateln using CodeSmith 3.2.6.887
-- Template:       StoredProcedures.cst
-- Procedure Name: [dbo].[usp_SelectProductsAll]
-- Date Generated: Thursday, June 29, 2006
------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[usp_SelectProductsAll]
         
          @direction as nchar(20)
       
AS
     
DECLARE @SQL VARCHAR(4000)
     

SET @SQL = '
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT
     [P_ProductID],
     [P_ProductName],
     [P_QtyPerUnit],
     [P_UnitPrice],
     [P_UnitsInStock],
     [P_UnitsOnOrder],
     [P_ReorderLevel],
     [P_WareHouseNo],
     [P_ModifiedDate],
     [P_MEMO],
     [P_Desc]
FROM
     [dbo].[Products]
     order by ' + @direction

EXEC(@SQL)

--endregion

thanks
Nick

0
Comment
Question by:countyprob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 2000 total points
ID: 18022723
After you have modified the stored procedure, simply run the query against the database - that will update it. press F5 to run the query
0
 
LVL 29

Expert Comment

by:QPR
ID: 18022811
When you click save you are saving the code as a text file that you can run at a later date.
When you run the query (as Nightman says) you are running the script to ALTER the procedure. You aren't actually running the stored procedure at this stage.
0
 
LVL 1

Author Comment

by:countyprob
ID: 18022852
I guess they must do it that way to check syntax, right?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question