Solved

How do edit stored procedure and save

Posted on 2006-11-27
3
239 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
3 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

828 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