Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Mumbai
    Posts
    2

    Unanswered: Problem Using Stored Proc

    Hi Folks.. I'm really new to SQL server

    I'm writing a stored procedure as:

    ALTER PROCEDURE usp_CDE_IssueProcedure1 AS

    DELETE FROM tbl_CDE_IssueTable1
    INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

    Now when I run this stored procedure, the table just does not get affected.
    But on the other hand when I run the query alone that is:
    INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

    The above query runs absolutely fine.. So can anyone pls tell me whats wrong... I have to get this query in the stored procedure..PLsssssssssssss HELP

    Thanks,
    Shruti Majithia,
    Quinnox Consultancy Services.

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    12
    Hi Shruti,

    'ALTER PROC' is used to modify the existing stored procedure without changing the permissions and without affecting the dependent PROCs in the database. So the procedure should be already there in the database. You can use the following check:

    if exists (select name from sysobjects where type = 'P' and name = 'usp_CDE_IssueProcedure1')
    BEGIN
    ALTER PROCEDURE usp_CDE_IssueProcedure1 AS
    DELETE FROM tbl_CDE_IssueTable1
    INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus
    END
    else
    BEGIN
    CREATE PROCEDURE usp_CDE_IssueProcedure1 AS
    DELETE FROM tbl_CDE_IssueTable1
    INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus
    END
    go
    Last edited by pathakpr; 02-25-04 at 05:51.

  3. #3
    Join Date
    Feb 2004
    Posts
    1

    Re: Problem Using Stored Proc

    I have the exactly same problem i think..
    i have made an sp to update a table.
    and everything works with no errors.
    exept from that nothing happens.
    i the SQL Profiler it says:

    exec Content_update @ContentID = 1, @Content = N'some text'

    but nothing happens

    thx in advance


    Originally posted by shrutimajithia
    Hi Folks.. I'm really new to SQL server

    I'm writing a stored procedure as:

    ALTER PROCEDURE usp_CDE_IssueProcedure1 AS

    DELETE FROM tbl_CDE_IssueTable1
    INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

    Now when I run this stored procedure, the table just does not get affected.
    But on the other hand when I run the query alone that is:
    INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

    The above query runs absolutely fine.. So can anyone pls tell me whats wrong... I have to get this query in the stored procedure..PLsssssssssssss HELP

    Thanks,
    Shruti Majithia,
    Quinnox Consultancy Services.

Posting Permissions

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