Solved

How do edit stored procedure and save

Posted on 2006-11-27
3
236 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 45
MS SQL 2016 from Database to Datawarehouse 6 36
How to convert JSON file to csv? 7 53
Mssql SQL query 14 27
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now