none
how to use stored procedure in sharepoint external list

    Question

  • I have a table "spstrial" which consists of 2 parameters "Name"(varchar 20) and "gender"(varchar 6). I have made a stored procedure names "spspstrial" 
    create procedure spspstrial
    @name varchar(20),@gender varchar(6)
    as
    begin
    insert into spstrial values(@name,@gender)
    select * from spstrial
    end
    then i compile it and it compiled successfully. then i execute the stored procedure as exec spspstrial 'sidhanta','male'.Finally it executed sucessfully. Then in ect i create all type of operation for that stored procedure.When i create external list from that external stored procedure it gives error "The query against the database caused an error".From event viewer i come to know that " Cannot insert the value NULL into column 'Name', table 
    'SidhantaDB.dbo.spstrial'; column does not allow nulls. INSERT fails." Can anyone please share their ideas with me.
    With Thanks & Regards
    Sidhanta Tripathy

    Sidhanta Tripathy

    • Edited by Sidhanta Tuesday, December 20, 2011 10:00 AM
    Tuesday, December 20, 2011 9:54 AM

Answers

  •  

    Hi,

    Your stored procedure can't be used for Read List and Read Item operations. Usually one stored procedure should be associated with one operation. Look at this example. There are five stored procedures that are related to all CRUD operations.

    Read List

    CREATE PROCEDURE [dbo].[GetAllCustomers]
    AS
    BEGIN
    	SELECT * from CustomerTable
    END
    

     

    Read Item

    CREATE PROCEDURE [dbo].[GetCustomerByID]	
    	@customerID int
    AS
    BEGIN
    	SELECT * from CustomerTable where ID=@customerID
    END
    

     

    Create

    CREATE PROCEDURE [dbo].[CreateCustomer]	
    	@customerID int,
    	@fn varchar(50),
    	@s char,
    	@a int
    AS
    BEGIN
    	insert into CustomerTable(ID, FirstName, Sex, Age) values(@customerID, @fn, @s, @a)
    END
    

     

    Update

    CREATE PROCEDURE [dbo].[UpdateCustomer]
    	@customerID int,
    	@fn varchar(80),
    	@s char = null,
    	@a int = null
    AS
    BEGIN
    	UPDATE CustomerTable
         SET    
        [FirstName] = @fn,
        [Sex] = @s,
        [Age] = @a    
        WHERE
        ID = @customerID
    END
    

     

    Delete

    CREATE PROCEDURE [dbo].[DeleteCustomer] 
    	@customerID int
    AS
    BEGIN
    	delete from CustomerTable where ID=@customerID
    END
    



    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts
    • Marked as answer by Sidhanta Tuesday, December 27, 2011 7:09 AM
    Wednesday, December 21, 2011 8:48 AM
  • NP Sidhanta, you're welcome.

    Please mark as answer if it helped solve your issue.

     

    Best regards

     


    Thomas Balkeståhl - Technical Specialist - SharePoint - http://blksthl.wordpress.com
    • Marked as answer by Sidhanta Monday, December 26, 2011 9:12 AM
    Thursday, December 22, 2011 6:05 AM

All replies

  • This seems to be the answer to all of your questions:

    http://blogs.msdn.com/b/jysaikia/archive/2010/12/15/a-step-by-step-guide-in-designing-bcs-entities-by-using-a-sql-stored-procedure.aspx

     

    Regards

     


    Thomas Balkeståhl - Technical Specialist - SharePoint - http://blksthl.wordpress.com
    Tuesday, December 20, 2011 10:00 AM
  •  

    Hi,

    Your stored procedure can't be used for Read List and Read Item operations. Usually one stored procedure should be associated with one operation. Look at this example. There are five stored procedures that are related to all CRUD operations.

    Read List

    CREATE PROCEDURE [dbo].[GetAllCustomers]
    AS
    BEGIN
    	SELECT * from CustomerTable
    END
    

     

    Read Item

    CREATE PROCEDURE [dbo].[GetCustomerByID]	
    	@customerID int
    AS
    BEGIN
    	SELECT * from CustomerTable where ID=@customerID
    END
    

     

    Create

    CREATE PROCEDURE [dbo].[CreateCustomer]	
    	@customerID int,
    	@fn varchar(50),
    	@s char,
    	@a int
    AS
    BEGIN
    	insert into CustomerTable(ID, FirstName, Sex, Age) values(@customerID, @fn, @s, @a)
    END
    

     

    Update

    CREATE PROCEDURE [dbo].[UpdateCustomer]
    	@customerID int,
    	@fn varchar(80),
    	@s char = null,
    	@a int = null
    AS
    BEGIN
    	UPDATE CustomerTable
         SET    
        [FirstName] = @fn,
        [Sex] = @s,
        [Age] = @a    
        WHERE
        ID = @customerID
    END
    

     

    Delete

    CREATE PROCEDURE [dbo].[DeleteCustomer] 
    	@customerID int
    AS
    BEGIN
    	delete from CustomerTable where ID=@customerID
    END
    



    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts
    • Marked as answer by Sidhanta Tuesday, December 27, 2011 7:09 AM
    Wednesday, December 21, 2011 8:48 AM
  • Thanks  Thomas Balkeståhl
    Sidhanta Tripathy
    • Proposed as answer by sidhan Tuesday, December 27, 2011 11:16 AM
    Thursday, December 22, 2011 5:23 AM
  • NP Sidhanta, you're welcome.

    Please mark as answer if it helped solve your issue.

     

    Best regards

     


    Thomas Balkeståhl - Technical Specialist - SharePoint - http://blksthl.wordpress.com
    • Marked as answer by Sidhanta Monday, December 26, 2011 9:12 AM
    Thursday, December 22, 2011 6:05 AM
  • Thanks Mr.  Thomas Balkeståhl for the guide provided to me
    Sidhanta Tripathy
    • Proposed as answer by sidhan Tuesday, December 27, 2011 11:16 AM
    Monday, December 26, 2011 9:13 AM
  • Thanks Mr.  Dmitry for the solution provided by you.


    Sidhanta Tripathy
    Tuesday, December 27, 2011 7:11 AM