Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44

    Unanswered: Insert/Update statements or Stored Procs

    When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

    thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Stored procs...but who's going to write them?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Use ADO from VB for insert and update

  4. #4
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    Originally posted by Brett Kaiser
    Stored procs...but who's going to write them?
    wouldn't i just code the Insert/Update statement within the stored proc, then pass the value's to the stored proc. That sounds like alot of parameters to be dealing with for larger tables.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

    Also you isolate all of the buseness rules to the server, not the code...

    More control that way.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    Originally posted by Brett Kaiser
    Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

    Also you isolate all of the buseness rules to the server, not the code...

    More control that way.
    Thanks Brett, one more quick question. Within the stored proc, i need to check if the record already exists before inserting or updating. Can you paste a small code sample to give me an idea of how i would ideally do that.

    thanks alot

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
    GO
    
    INSERT INTO myTable99(Col1,Col2)
    SELECT 1,'A' UNION ALL
    SELECT 2,'B' UNION ALL
    SELECT 3,'C' UNION ALL
    SELECT 4,'D'
    GO
    
    CREATE PROC mySproc99
    	  @Action Char(1)
    	, @Col1 int
    	, @Col2 char(1) = Null
    AS
    --           File:      {\\tsstrv03\ESolutions}:
    --           Date:      May 1st, 2002
    --         Author:      Brett Kaiser
    --         Server:      
    --       Database:      TaxReconDB
    --          Login:      sa
    --    Description:      myTable99 Maint sproc
    --
    --
    --                      The stream will do the following:
    --
    --				1. 
    --
    --    Tables Used:	myTable99
    --
    -- Tables Created:      None
    --
    --
    --  Row Estimates:
    --  		name                 		rows        reserved           data               index_size         unused
    --              -------------------- 		----------- ------------------ ------------------ ------------------ ------------------
    --		Ledger_Detail			76779       17160 KB		17040 KB	     64 KB	        56 KB
    --		ATS_SignOff_Entity		 3316         512 KB              504 KB             16 KB              -8 KB
    --		tblAcct_LedgerBalance		11691        3848 KB             3792 KB              8 KB              48 KB
    --
    --Change Log
    --
    -- UserId   	Date        	Description
    -- -----------  --------------  ------------------------------------------------------------------------------------------
    -- x002548  	05/23/2002  	1. Initial release
    --
    --
    --
    
    Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @RC int
    
    SET NOCOUNT ON
    
    SELECT @rc = 0
    
    BEGIN TRAN
    
    	IF @Action NOT IN ('S','I','U','D')
    	  BEGIN
    		SELECT @Error_Loc = 1
    		SELECT @Error_Message = 'Incorrect Request.  Must be S,I,U or D.  Paramter was: "' +  @Action + '"'
    		SELECT @Error_Type = 50002
    		GOTO mySproc99_Error
    	  END
    
    	IF @Action = 'S'
    	  BEGIN
    		SELECT Col1, Col2 FROM myTable99 WHERE Col1 = @Col1
    	
    		SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error	
    	
    		If @Error_Out <> 0
    		  BEGIN
    			Select @Error_Loc = 2
    			Select @Error_Type = 50001
    			GOTO mySproc99_Error
    		  END
    	
    		If @Result_Count = 0
    		  BEGIN
    			SELECT @Error_Loc = 2
    			SELECT @Error_Message = 'myTable99 Returned zero rows'
    			SELECT @Error_Type = 50002
    			GOTO mySproc99_Error
    		  END
    	  END
    
    	IF @Action = 'D'
    	  BEGIN
    		DELETE FROM myTable99 WHERE Col1 = @Col1
    	
    		SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error	
    	
    		If @Error_Out <> 0
    		  BEGIN
    			Select @Error_Loc = 3
    			Select @Error_Type = 50001
    			GOTO mySproc99_Error
    		  END
    	
    		If @Result_Count = 0
    		  BEGIN
    			SELECT @Error_Loc = 3
    			SELECT @Error_Message = 'An Attempted DELETE from myTable99 affected zero rows'
    			SELECT @Error_Type = 50002
    			GOTO mySproc99_Error
    		  END
    	  END
    
    	IF @Action = 'I'
    	  BEGIN
    		INSERT INTO myTable99(Col1,Col2) SELECT @Col1, @Col2
    	
    		SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error	
    	
    		If @Error_Out <> 0
    		  BEGIN
    			Select @Error_Loc = 4
    			Select @Error_Type = 50001
    			GOTO mySproc99_Error
    		  END
    	
    		If @Result_Count = 0
    		  BEGIN
    			SELECT @Error_Loc = 4
    			SELECT @Error_Message = 'An Attempted INSERT to myTable99 did not insert anything'
    			SELECT @Error_Type = 50002
    			GOTO mySproc99_Error
    		  END
    	  END
    
    	IF @Action = 'U'
    	  BEGIN
    		UPDATE myTable99 SET Col2=@Col2 WHERE Col1 = @Col1
    	
    		SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error	
    	
    		If @Error_Out <> 0
    		  BEGIN
    			Select @Error_Loc = 5
    			Select @Error_Type = 50001
    			GOTO mySproc99_Error
    		  END
    	
    		If @Result_Count = 0
    		  BEGIN
    			SELECT @Error_Loc = 5
    			SELECT @Error_Message = 'An Attempted UPDATE of myTable99 Affected zero rows'
    			SELECT @Error_Type = 50002
    			GOTO mySproc99_Error
    		  END
    	  END
    
    COMMIT TRAN
    	
    mySproc99_Exit:
    
    SET NOCOUNT OFF
    
    RETURN @rc
    
    mySproc99_Error:
    
    ROLLBACK TRAN
    
    IF @Error_Type = 50001
      BEGIN
    	Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))  
    		     + ',"' + '  @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
    		     + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
    		     + ',"' + '  Message: ' + ',"' + RTrim(description)
    	 From master..sysmessages
    	Where error = @error_out)
      END
    IF @Error_Type = 50002
    
      BEGIN
    	Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc)) 
    		              + ',"' + ' Severity:  UserLevel ' 
    			      + ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
      END
    
    SELECT @rc = -1
    
    RAISERROR @Error_Type @Error_Message
    
    GOTO mySproc99_Exit
    GO
    
    DECLARE @RC int
    
    EXEC @RC = mySproc99 'X',1,'A'
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'S',4
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'I',5,'E'
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'S',5
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'U',5,'F'
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'S',5
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'D',5
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'S',5
    
    SELECT @RC
    
    EXEC @RC = mySproc99 'I',4,'F'
    
    SELECT @RC
    GO
    
    
    DROP PROC mySproc99
    GO
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Pat Phelan
    I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

    -PatP
    Actually, any communication with the server should be done through stored procedure, including SELECT.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by rdjabarov
    Actually, any communication with the server should be done through stored procedure, including SELECT.
    I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

    While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

    -PatP

  11. #11
    Join Date
    Sep 2003
    Posts
    522
    here's the man of so many virtues

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ms_sql_dba
    here's the man of so many virtues
    You sure s/he's a man?

    Pat, you lost me...

    we're talking about an app right? Not ad-hoc/dba maint issues? right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by ms_sql_dba
    here's the man of so many virtues
    Are you accusing me of having virtues ??? I may wear many hats, but that is due to having a huge head. It has nothing to do with virtues of any kind!

    -PatP

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rdjabarov
    Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)
    suave is the only word I can think of....

    You must be a ladies man.....



    Does anyone use anything like the template posted..or is it 1 sproc per operation?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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