how to save stored procedures on sql express server

Hi Guys

I have visual web developer and sqlexpress 2005 installed on my windows XP pro.

I am creating stored procedures through VWD and works fine for me. However today I realize I do not know how to create stored procedures through sqlexpress server managment.

When I try it it wants to save it as file. And if I do that I am not able to see them until manually open each .sql file.

so, could you enlighten me little please.

thanks
Cemal

hi Cemal,

Cemal wrote:

Hi Guys

I have visual web developer and sqlexpress 2005 installed on my windows XP pro.

I am creating stored procedures through VWD and works fine for me. However today I realize I do not know how to create stored procedures through sqlexpress server managment.

When I try it it wants to save it as file. And if I do that I am not able to see them until manually open each .sql file.

the "disk" button states to actually save the text inside the query window... you can type (or cut&paste) some text, whatever thext in whatever language, and you can later save that text into a file...

when you have to create a stored procedure, you have to execute the data definition language statements defining that object.. so, say you have a text like

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_my_stored_procedure]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_my_stored_procedure]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_my_stored_procedure] AS BEGIN /**/ /*Author:xxx yyy zzz*/ /*Date:14/07/2007*/ /*Modified:__/__/_*/ /**/ /*Please report suggestions/comments/bugs/feedback to:*/ /*me@.me.com*/ /**/ /* - */ /*this procedure perform these tasks...*/ /**/ DECLARE @.msg varchar(1000); BEGIN TRY SET NOCOUNT ON; SELECT col_list FROM [dbo].[my_table]; RETURN 0; END TRY BEGIN CATCH -- returns the occured exception DECLARE @.ErrorMSG varchar(2000); SET @.ErrorMSG = ERROR_MESSAGE() RAISERROR (@.ErrorMSG, 16, 1); RETURN -100 END CATCH END; GO

you have to execute it in order to "save" it within the database it belongs... to execute it, press the F5 key or the toolbarbutton with the "! Execute" mark...

regards

|||As this is a common misunderstanding, I once did a screencast for that, available on my site:

How to alter a stored procedure within SSMS: Difference between saving and executing a modified stored procedure

Jens K. Suessmeyer.

http://www.sqlserver2005.de