[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

New Stored Procedure not STORING?

I want to create a new Stored Procedure for use in a SQL Agent Job. I would like my new Stored Procedure to appear in the list of Stored Procedures which is visible in SSMS (Databases >> the Database Name >> Progammability >> Stored Procedures >> My new Stored Procudure Name).

I have tried choosing New Stored Procedure and creating my new procedure. When i Save that new procedure, I am prompted to save it as a .sql file on the hard drive. When I do this it does not appear in SSMS.

How do I get a New Stored Procedure to appear in the list of stored procedures in SSMS? My ultimate goal is to use the new Stored Procedure in a SQL Agent Job.

Thanks,
pat
0
mpdillon
Asked:
mpdillon
  • 5
  • 4
1 Solution
 
VipulKadiaCommented:
When you create a new stored procedure, there are lines of code like...
CREATE PROCEDURE <PROCEDURE_NAME>

So after writing your stored procedure, just press 'F5'(Execute) button. It will be stored in database you selected.
0
 
mpdillonAuthor Commented:
Thank you. I need to test this before I close the question. Currently, I am unable to access that computer. As soon as I have verified this, I will award points and close the question.
Thank you for the quick response.
0
 
VipulKadiaCommented:
I think after writing a stored procedure, you were pressing 'CTRL+S' or 'Save' button. That's why it was prompting for saving as a .sql file. Just follow the steps I mentioned in my former reply, you will definitely get the solution.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
mpdillonAuthor Commented:
VipulKadia,
This is the first time I had tried doing this. Please bear with me.
I am trying to duplicate an existing stored procedure. I have modified the existing procedure in a Text editor and I have attached it.
 MO-Stored-Procedure.txt

Below you will see what I am working with during the Create Stored Procedure script. I have modified the Save As name. I have deleted the parameters.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, calculateOrderTax_mo> 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

END
GO

Open in new window


But I do not know how or where to put the code shown in the attached text file.
Could you please explain this to me.
Thanks,
pat
0
 
DrewKjellCommented:
When you are in SSMS you will create a new query with the Stored Procedure code.  As VipulKadia said once you have the stored procedure as you want it, select 'Execute' or hit 'F5' in SSMS.  This will run the SQL statement CREATE PROCEDURE method which will create the procedure in the database you are running your query against.

For Instance:

USE <Database name where you want the Stored Proc>

CREATE PROCEDURE spSelectStuff

AS
BEGIN

SELECT * from <My Table>

END
GO

With this text in Management studio select 'Execute' to create the stored procedure in the database referenced in the 'Use' statement.

Drew
0
 
VipulKadiaCommented:
Hi mpdillon,
I saw your 'MO-Stored-Procedure.txt' file.
Now in SSMS, move upto your database. Then open new query window. Paste contents of this text file into this newly opened query window and then press 'F5' from keyboard or 'Execute' command.
As you are modifying your existing stored procedure, you should use 'ALTER PROCEDURE' instead of 'CREATE PROCEDURE' and in your text file 'ALTER PROCEDURE' is written. So just paste contents of the text file into query window and press 'F5' button.
0
 
mpdillonAuthor Commented:
VipulKadia,
Thank you for staying with me on this question. I need to create a new Stored Procedure. The procedure in the text file is an existing Stored procedure that I have modified. I want to save this modification with a new name. I think what I need to do is change Alter Procedure to Create Procedure. I have done that in the code below.
I open a new Query window. I paste this code in place and press EXECUTE. I receive and error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.

What does this mean

USE [DATA_02]
GO
/****** Object:  StoredProcedure [dbo].[calculateOrderTax_mo]    Script Date: 10/14/2011 05:50:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  Stored Procedure dbo.calculateOrderTax    Script Date: 10/14/2009 7:44:48 PM ******/

CREATE PROCEDURE <Procedure_Name, sysname, calculateOrderTax_mo> 
as
declare @ordNo char(8)
declare @state char(2)
declare @rate numeric(9,6)
declare taxCur cursor for
select ord_no, State, max(r.C_ComSalesRate) C_ComSalesRate
from ShipAddressmo s 
inner join TaxTables.dbo.moZIP t on (s.ZipCode = t.Zip_ZipCode)
inner join TaxTables.dbo.moRate r on (t.Zip_SignatureCode = r.C_SignatureCode and s.City = r.C_CityName) 
where r.C_StateCode = 'mo'
group by ord_no, State,Zip_ZipCode

open taxCur
fetch taxCur into @ordNo, @state, @rate

while @@FETCH_STATUS = 0
begin
print  @ordNo + ' ' + @state + ' ' + cast(@rate as varchar(10))
update oeordhdr_sql set tax_cd = @state, Tax_pct = (@rate*100) where ord_no = @ordNo and (tax_cd = 'mo'  or tax_cd = 'OST') and status <> '9' and ship_to_addr_3 like '% mo %' 
fetch taxCur into @ordNo, @state, @rate
end

close taxCur
deallocate taxCur

Open in new window


Thanks,
pat
0
 
VipulKadiaCommented:
Your line no. 10 is :
CREATE PROCEDURE <Procedure_Name, sysname, calculateOrderTax_mo>

Change it to :
CREATE PROCEDURE calculateOrderTax_mo
0
 
mpdillonAuthor Commented:
Thank you that worked. I really do appreciate your assistance.

After I EXECUTEd the procedure and it was created, I modified the procedure by changing CREATE to ALTER. This way it is exactly like the procedure I was trying to duplicate. If this is wrong, please let me know.
Thanks,
pat
0
 
VipulKadiaCommented:
mpdillon,
keep in mind one rule :
Whenever you create any NEW stored procedure, use CREATE PROCEDURE statement.
and whenever you modify any EXISTING stored procedure, use ALTER PROCEDURE statement.

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now