none
store peocedures

    Question

  • i have a table which is documnetinfo and another approvetype

    in documnetinfo table there is a uploaded documnet

    this is approvetype table

    and this is documnetinfo table

    now i want set a peding status in documnetinfo table when any user upload document then its status automatically set as a pending

    this is store prcedure which i try

    create procedure sppendingfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,DocType.DocType,Department.         DepType,ApproveType.ApproveType
        from DocumentInfo inner join DocType on   
             DocumentInfo.DocTypeID=DocType.DocTypeID       
        inner join Department on DocumentInfo.DepID=Department.DepID
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveType=3)

    when i excute it show me like this

    and it can not show me any documents when i execute query ...

    where is problem???

    plz any one tell me ..

    Wednesday, October 16, 2013 8:25 AM

Answers

  • i create this final new sp and it works for me

    CREATE PROCEDURE e
    AS
        SELECT  di.DocID, 
                di.DocName, 
                di.Uploadfile, 
                dt.DocType,
                d.DepType, 
                at.ApproveType
        FROM    DocumentInfo di
            LEFT OUTER JOIN
                DocType dt ON dt.DocTypeID = di.DocTypeID
            LEFT OUTER JOIN 
                Department d ON d.DepID = di.DepID
            LEFT OUTER JOIN
                ApproveType at ON at.ApproveID = di.ApproveID 
       AND  at.ApproveID = 3

    but it show me approvetype null how i show when upload document then pedning and when approve then it changes to approve/reject

    Wednesday, October 16, 2013 1:02 PM
  • i create this final new sp and it works for me

    CREATE PROCEDURE e
    AS
        SELECT  di.DocID, 
                di.DocName, 
                di.Uploadfile, 
                dt.DocType,
                d.DepType, 
                at.ApproveType
        FROM    DocumentInfo di
            LEFT OUTER JOIN
                DocType dt ON dt.DocTypeID = di.DocTypeID
            LEFT OUTER JOIN 
                Department d ON d.DepID = di.DepID
            LEFT OUTER JOIN
                ApproveType at ON at.ApproveID = di.ApproveID 
       AND  at.ApproveID = 3

    but it show me approvetype null how i show when upload document then pedning and when approve then it changes to approve/reject

    Try the below: Here it fetches all records irrespective of the approval status. (Make sure you have records in all the tables.)

    create procedure spfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,DocType.DocType,Department.         DepType,ApproveType.ApproveType
        from DocumentInfo inner join DocType on   
             DocumentInfo.DocTypeID=DocType.DocTypeID       
        inner join Department on DocumentInfo.DepID=Department.DepID
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 17, 2013 4:47 AM

All replies

  • You've not yet set status in documentinfo table. You need to first set AprroveID of all documents to 3 in documentinfo table

    ie use logic like

    UPDATE di
    SET di.ApproveID = (SELECT ApproveID FROM ApproveType WHERE ApproveType = 'Pending')


    Then use query like below

    create procedure sppendingfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,DocType.DocType,Department.         DepType,ApproveType.ApproveType
        from DocumentInfo inner join DocType on   
             DocumentInfo.DocTypeID=DocType.DocTypeID       
        inner join Department on DocumentInfo.DepID=Department.DepID
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveType='Pending')

    If you want this to be automatically done each time user creates a new document then create a default constraint on ApproveID field in documentinfo table to make it 3 

    Please mark this as answer if this helps you to solve the problem

    Wednesday, October 16, 2013 8:35 AM
  • The column "DocumentInfo.ApproveID" is null. Please fill it.

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Wednesday, October 16, 2013 8:38 AM
  • May be the below:

      select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,DocType.DocType,
      Department.DepType,ApproveType.ApproveType
        from DocumentInfo inner join DocType on   
             DocumentInfo.DocTypeID=DocType.DocTypeID       
        inner join Department on DocumentInfo.DepID=Department.DepID
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveID=3 and ApproveType.Status='True')


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 8:39 AM
  • it still can not show my any documents
    Wednesday, October 16, 2013 10:25 AM
  • Please post DDL and DML.

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Wednesday, October 16, 2013 10:26 AM
  • it still can not show my any documents

    Could you please try the below and let us know if that returns some results:

    (Make sure you have data in both tables to meet the criteria)

     select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,ApproveType.ApproveType
        from DocumentInfo 
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveID=3 and ApproveType.Status='True')


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 10:27 AM
  • how i use this..first i create a function like this

     public DataTable getdocall()
            {
                return db.ExecuteDataSet("sppendingfilesss").Tables[0];
    
            }

    then i use this in page load

            protected void Page_Load(object sender, EventArgs e)
            {
                GridView1.DataSource = dw.getdocall();
                GridView1.DataBind();
    
            }

    how i use updateee

    Wednesday, October 16, 2013 10:28 AM
  • No, you do not want to do anything in your application code.

    First make sure, your procedure is returning some data. You can very well check in SSMS. Once you confirmed, the data is returning, then you can call from the application.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 10:30 AM
  • Please post DDL and DML.

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    i posted below
    Wednesday, October 16, 2013 10:44 AM
  • Can you check the below:

    Select * From documentinfo where approveid=3


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 10:45 AM
  • Can you check the below:

    Select * From documentinfo where approveid=3


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    yes i also check this but it show me same result ..:(

    Wednesday, October 16, 2013 10:49 AM
  • Where?

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Wednesday, October 16, 2013 10:52 AM
  • That means, you do not have any records in documentinfo table which has Pending status in approvetype table. Hence you are not getting result. It looks like a data issue. Please correct the data.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 10:53 AM
  •  i create another table which is approval in this

    this

    but i want to show when user upload documents then status show pending and when approve status change to pending to approve/reject..


    Wednesday, October 16, 2013 11:09 AM
  • That means, you do not have any records in documentinfo table which has Pending status in approvetype table. Hence you are not getting result. It looks like a data issue. Please correct the data.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    now above i posted whole problem please help

    Wednesday, October 16, 2013 11:25 AM
  • As long as you created a new table for documentinfo with some data approveid 3, you would be able to use your proc now.

    create procedure sppendingfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,DocType.DocType,Department.         DepType,ApproveType.ApproveType
        from DocumentInfo inner join DocType on   
             DocumentInfo.DocTypeID=DocType.DocTypeID       
        inner join Department on DocumentInfo.DepID=Department.DepID
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveType=3)

    To change the status, you need to create another proc to update the vlaue of approveid in documentinfo table and call the sp from your application layer. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 11:29 AM
  • As long as you created a new table for documentinfo with some data approveid 3, you would be able to use your proc now.

    create procedure sppendingfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,DocType.DocType,Department.         DepType,ApproveType.ApproveType
        from DocumentInfo inner join DocType on   
             DocumentInfo.DocTypeID=DocType.DocTypeID       
        inner join Department on DocumentInfo.DepID=Department.DepID
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveType=3)

    To change the status, you need to create another proc to update the vlaue of approveid in documentinfo table and call the sp from your application layer. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    but this sp can not show me any data?? :/
    Wednesday, October 16, 2013 11:36 AM
  • Hello rebmaamberrebmaamber,

    If you corrected your data in documentinfo (you said it already), you will be able to see data from your procedure. Please give table structures of all your tables(part of the query) with some sample data. We would be able to help you better. (Please do not post any images, we need script)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Latheesh NKMVP Wednesday, October 16, 2013 11:43 AM asking for script
    Wednesday, October 16, 2013 11:42 AM
  • Hello rebmaamberrebmaamber,

    If you corrected your data in documentinfo (you said it already), you will be able to see data from your procedure. Please give table structures of all your tables(part of the query) with some sample data. We would be able to help you better. (Please do not post any images, we need script)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    how i send u my database script..or i posted here?
    @latheesh NK

    Wednesday, October 16, 2013 11:59 AM
  • You may post here...you can very well mask your actual table names and column names with dummy names. 

    I feel this would be appropriate way to help you. (after 23 posts, we are nowhere....Lets see how it works out)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, October 16, 2013 12:05 PM
  • You may post here...you can very well mask your actual table names and column names with dummy names. 

    I feel this would be appropriate way to help you. (after 23 posts, we are nowhere....Lets see how it works out)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    /****** Object:  Table [dbo].[ApproveType]    Script Date: 10/16/2013 16:59:23 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[ApproveType](
        [ApproveID] [int] IDENTITY(1,1) NOT NULL,
        [ApproveType] [nvarchar](50) NULL,
        [Status] [nvarchar](50) NULL,
     CONSTRAINT [PK_ApproveType] PRIMARY KEY CLUSTERED
    (
        [ApproveID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    /****** Object:  Table [dbo].[DocumentInfo]    Script Date: 10/16/2013 16:59:23 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[DocumentInfo](
        [DocID] [int] IDENTITY(1,1) NOT NULL,
        [DocDesciption] [nvarchar](50) NULL,
        [DocName] [nvarchar](50) NULL,
        [UploadedDate] [datetime] NULL,
        [Uploadfile] [nvarchar](50) NULL,
        [DocTypeID] [int] NULL,
        [DepID] [int] NULL,
        [ApproveID] [int] NULL,
        [UploadedBy] [nvarchar](50) NULL,
        [UserID] [int] NULL,
     CONSTRAINT [PK_DocumentInfo] PRIMARY KEY CLUSTERED
    (
        [DocID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    /****** Object:  StoredProcedure [dbo].[approvtype]    Script Date: 10/16/2013 16:59:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    create procedure [dbo].[approvtype]
    as
    select * from ApproveType
    GO

    /****** Object:  Table [dbo].[Approval]    Script Date: 10/16/2013 16:59:23 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Approval](
        [SeqNo] [int] IDENTITY(1,1) NOT NULL,
        [DocID] [int] NULL,
        [ApproveID] [int] NULL,
        [AppoveBy] [nvarchar](50) NULL,
     CONSTRAINT [PK_Approval] PRIMARY KEY CLUSTERED
    (
        [SeqNo] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    /****** Object:  StoredProcedure [dbo].[approveddd]    Script Date: 10/16/2013 16:59:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE procedure [dbo].[approveddd]
    @DocID int,
    @ApproveID int,        
    @ApproveBy nvarchar(50)
    as
    insert Approval (DocID,ApproveID,AppoveBy)
    values(@DocID,@ApproveID,@ApproveBy)
    GO

    /****** Object:  ForeignKey [FK_Approval_ApproveType1]    Script Date: 10/16/2013 16:59:23 ******/
    ALTER TABLE [dbo].[Approval]  WITH CHECK ADD  CONSTRAINT [FK_Approval_ApproveType1] FOREIGN KEY([ApproveID])
    REFERENCES [dbo].[ApproveType] ([ApproveID])
    GO
    ALTER TABLE [dbo].[Approval] CHECK CONSTRAINT [FK_Approval_ApproveType1]
    GO
    /****** Object:  ForeignKey [FK_Approval_DocumentInfo]    Script Date: 10/16/2013 16:59:23 ******/
    ALTER TABLE [dbo].[Approval]  WITH CHECK ADD  CONSTRAINT [FK_Approval_DocumentInfo] FOREIGN KEY([DocID])
    REFERENCES [dbo].[DocumentInfo] ([DocID])
    GO
    ALTER TABLE [dbo].[Approval] CHECK CONSTRAINT [FK_Approval_DocumentInfo]
    GO

    Wednesday, October 16, 2013 12:15 PM
  • Try the below:(It works for me)(Note: I removed doctype and other tables from the procedure definition as you have not provided the table structure for the same. But you can use in your actual scenario, having said, there should be data to meet the join condition.)

    EDIT: I think you need to change the pending item proc to check non existence of docid in APPROVAL table.

    CREATE TABLE [dbo].[ApproveType](
        [ApproveID] [int] IDENTITY(1,1) NOT NULL,
        [ApproveType] [nvarchar](50) NULL,
        [Status] [nvarchar](50) NULL,
     CONSTRAINT [PK_ApproveType] PRIMARY KEY CLUSTERED 
    (
        [ApproveID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    Insert into ApproveType Select 'Approve','True'
    Insert into ApproveType Select 'Reject','True'
    Insert into ApproveType Select 'Pending','True'
    
    CREATE TABLE [dbo].[DocumentInfo](
        [DocID] [int] IDENTITY(1,1) NOT NULL,
        [DocDesciption] [nvarchar](50) NULL,
        [DocName] [nvarchar](50) NULL,
        [UploadedDate] [datetime] NULL,
        [Uploadfile] [nvarchar](50) NULL,
        [DocTypeID] [int] NULL,
        [DepID] [int] NULL,
        [ApproveID] [int] NULL,
        [UploadedBy] [nvarchar](50) NULL,
        [UserID] [int] NULL,
     CONSTRAINT [PK_DocumentInfo] PRIMARY KEY CLUSTERED 
    (
        [DocID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    Insert into DocumentInfo Select 'DocDescription','Docname',GETDATE(),'asd.asd',100,1000,3,'self','234234'
    
    CREATE TABLE [dbo].[Approval](
        [SeqNo] [int] IDENTITY(1,1) NOT NULL,
        [DocID] [int] NULL,
        [ApproveID] [int] NULL,
        [AppoveBy] [nvarchar](50) NULL,
     CONSTRAINT [PK_Approval] PRIMARY KEY CLUSTERED 
    (
        [SeqNo] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    CREATE procedure [dbo].[approveddd]
    @DocID int,
    @ApproveID int,        
    @ApproveBy nvarchar(50)
    as
    insert Approval (DocID,ApproveID,AppoveBy)
    values(@DocID,@ApproveID,@ApproveBy)
    
    
    
    Alter procedure sppendingfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile
        from DocumentInfo 
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveID=3)
             where not exists (Select 1 from Approval where DocID = DocumentInfo.DocID)
    
    exec sppendingfilesss
    
    exec [approveddd] 2,2,'self'
    
    Select * From approval
    

    Wednesday, October 16, 2013 12:28 PM
  • Try the below:(It works for me)(Note: I removed doctype and other tables from the procedure definition as you have not provided the table structure for the same. But you can use in your actual scenario, having said, there should be data to meet the join condition.)

    EDIT: I think you need to change the pending item proc to check non existence of docid in APPROVAL table.

    CREATE TABLE [dbo].[ApproveType](
        [ApproveID] [int] IDENTITY(1,1) NOT NULL,
        [ApproveType] [nvarchar](50) NULL,
        [Status] [nvarchar](50) NULL,
     CONSTRAINT [PK_ApproveType] PRIMARY KEY CLUSTERED 
    (
        [ApproveID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    Insert into ApproveType Select 'Approve','True'
    Insert into ApproveType Select 'Reject','True'
    Insert into ApproveType Select 'Pending','True'
    
    CREATE TABLE [dbo].[DocumentInfo](
        [DocID] [int] IDENTITY(1,1) NOT NULL,
        [DocDesciption] [nvarchar](50) NULL,
        [DocName] [nvarchar](50) NULL,
        [UploadedDate] [datetime] NULL,
        [Uploadfile] [nvarchar](50) NULL,
        [DocTypeID] [int] NULL,
        [DepID] [int] NULL,
        [ApproveID] [int] NULL,
        [UploadedBy] [nvarchar](50) NULL,
        [UserID] [int] NULL,
     CONSTRAINT [PK_DocumentInfo] PRIMARY KEY CLUSTERED 
    (
        [DocID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    Insert into DocumentInfo Select 'DocDescription','Docname',GETDATE(),'asd.asd',100,1000,3,'self','234234'
    
    CREATE TABLE [dbo].[Approval](
        [SeqNo] [int] IDENTITY(1,1) NOT NULL,
        [DocID] [int] NULL,
        [ApproveID] [int] NULL,
        [AppoveBy] [nvarchar](50) NULL,
     CONSTRAINT [PK_Approval] PRIMARY KEY CLUSTERED 
    (
        [SeqNo] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    CREATE procedure [dbo].[approveddd]
    @DocID int,
    @ApproveID int,        
    @ApproveBy nvarchar(50)
    as
    insert Approval (DocID,ApproveID,AppoveBy)
    values(@DocID,@ApproveID,@ApproveBy)
    
    
    
    Alter procedure sppendingfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile
        from DocumentInfo 
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveID=3)
             where not exists (Select 1 from Approval where DocID = DocumentInfo.DocID)
    
    exec sppendingfilesss
    
    exec [approveddd] 2,2,'self'
    
    Select * From approval

    u mean that this procedure i used??

    Alter procedure sppendingfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile
        from DocumentInfo 
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID  and    
             (ApproveType.ApproveID=3)
             where not exists (Select 1 from Approval where DocID = DocumentInfo.DocID)
    
    


    Wednesday, October 16, 2013 12:53 PM
  • i create this final new sp and it works for me

    CREATE PROCEDURE e
    AS
        SELECT  di.DocID, 
                di.DocName, 
                di.Uploadfile, 
                dt.DocType,
                d.DepType, 
                at.ApproveType
        FROM    DocumentInfo di
            LEFT OUTER JOIN
                DocType dt ON dt.DocTypeID = di.DocTypeID
            LEFT OUTER JOIN 
                Department d ON d.DepID = di.DepID
            LEFT OUTER JOIN
                ApproveType at ON at.ApproveID = di.ApproveID 
       AND  at.ApproveID = 3

    but it show me approvetype null how i show when upload document then pedning and when approve then it changes to approve/reject

    Wednesday, October 16, 2013 1:02 PM
  • @ Latheesh NK
    Wednesday, October 16, 2013 1:31 PM
  • i create this final new sp and it works for me

    CREATE PROCEDURE e
    AS
        SELECT  di.DocID, 
                di.DocName, 
                di.Uploadfile, 
                dt.DocType,
                d.DepType, 
                at.ApproveType
        FROM    DocumentInfo di
            LEFT OUTER JOIN
                DocType dt ON dt.DocTypeID = di.DocTypeID
            LEFT OUTER JOIN 
                Department d ON d.DepID = di.DepID
            LEFT OUTER JOIN
                ApproveType at ON at.ApproveID = di.ApproveID 
       AND  at.ApproveID = 3

    but it show me approvetype null how i show when upload document then pedning and when approve then it changes to approve/reject

    Try the below: Here it fetches all records irrespective of the approval status. (Make sure you have records in all the tables.)

    create procedure spfilesss
    as
        select  DocumentInfo.DepID,DocumentInfo.DocName,DocumentInfo.Uploadfile,DocType.DocType,Department.         DepType,ApproveType.ApproveType
        from DocumentInfo inner join DocType on   
             DocumentInfo.DocTypeID=DocType.DocTypeID       
        inner join Department on DocumentInfo.DepID=Department.DepID
        inner join ApproveType  on DocumentInfo.ApproveID=ApproveType.ApproveID


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 17, 2013 4:47 AM