Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    27

    Unanswered: Stored procedures in SQL Server 2005

    Hi. When I create and save a stored procedure in sql server 2005 express it's saved as .sql file but though i can locate file in Projects folder, it is not displayed in Stored Procedures list. I cannot see within sql server which database contains which stored procedures. How to achieve this?

    Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A .sql file is a SQL Server script file. The content of the script is irrelevant - it might be a script to create a procedure, run a query, modify a function, do nothing etc.. It is not the stored procedure.
    A stored procedure is within the database. In order to create one you execute a CREATE PROCEDURE script. The procedure will be created in the database you are connected to at the time you execute the script.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    27
    oh i see. it's different than sql server 2000 i used to work with. thank you for help!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope, exactly the same.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    27
    Code:
    USE [restaurant]
    GO
    /****** Object:  StoredProcedure [dbo].[UpdateRestaurant]    Script Date: 06/10/2009 16:46:06 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Name
    -- Create date: 
    -- Description:	
    -- =============================================
    ALTER PROCEDURE [dbo].[UpdateRestaurant] 
    	-- Add the parameters for the stored procedure here
    	@Name nvarchar, 
    	@DisplayName nvarchar,
    	@VOEN int,
    	@Address nvarchar,
    	@Phone nvarchar,
    	@Url nvarchar
    AS
    UPDATE Restaurant
    SET [Name] = @Name, DisplayName = @DisplayName, VOEN = @VOEN, [Address] = @Address, Phone = @Phone, Url = @Url
    this is the code which sql server 2005 displays when i click Modify on a stored procedure. The problem is that it inserts only first characters of nvarchar parameters...

  6. #6
    Join Date
    Mar 2009
    Posts
    27
    yes but not exactly... it created a procedure by "create procedure" as well, but executed automatically when i pressed OK button and didn't ask to save as a sql script file.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since the default length for a VARCHAR column is 1 character unless you specify otherwise like VARCHAR(80), I'm guessing that the procedure did exactly what it should... You declared your procedure parameters to be one character long.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2009
    Posts
    27
    yes, exactly. thank you so much!

  9. #9
    Join Date
    Mar 2009
    Posts
    27
    yes, exactly. thank you so much!
    Last edited by emilh; 06-11-09 at 08:30.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by emilh
    Code:
    USE [restaurant]
    GO
    /****** Object:  StoredProcedure [dbo].[UpdateRestaurant]    Script Date: 06/10/2009 16:46:06 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Name
    -- Create date: 
    -- Description:	
    -- =============================================
    ALTER PROCEDURE [dbo].[UpdateRestaurant] 
    	-- Add the parameters for the stored procedure here
    	@Name nvarchar, 
    	@DisplayName nvarchar,
    	@VOEN int,
    	@Address nvarchar,
    	@Phone nvarchar,
    	@Url nvarchar
    AS
    UPDATE Restaurant
    SET [Name] = @Name, DisplayName = @DisplayName, VOEN = @VOEN, [Address] = @Address, Phone = @Phone, Url = @Url
    this is the code which sql server 2005 displays when i click Modify on a stored procedure. The problem is that it inserts only first characters of nvarchar parameters...
    Is this the entire procedure ? That update statement will update EVERY row in the table with those parameters supplied (no where clause).

  11. #11
    Join Date
    Mar 2009
    Posts
    27
    Pat Phelan was right. I specified the length of the variables and it worked.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    They are both right.
    One is a syntax error (as Pat spotted), the other is a logical error (i.e. it looks like your query is wrong).

    Please check the data in your table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •