Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: Store Procedure Help

    Hi guys,

    I have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.

    Does anyone can help me on this, I have never used store procedures.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to understand. To create a stored procedure, you use a SQL script such as:
    Code:
    USE <YourDatabaseName>
    GO
    CREATE PROCEDURE <YourProcedureName>
    (
    	<Optional list of parameters>
    )
    AS
    BEGIN
        <Your code goes here>
    END
    Ex:
    Code:
    USE [Sales]
    GO
    CREATE PROCEDURE [dbo].[DeleteOrderRecord]
    (
    	@LCF_DataPointer INT 
    )
    AS
    BEGIN
    	DECLARE @CF_SysCounter bigINT
    	DECLARE @CF_NumLCFLines bigINT
    	SET @CF_SysCounter = (SELECT TOP 1 CF_Datum FROM dbo.LCF_Data
    		WHERE SysCounter = @LCF_DataPointer)
    	DELETE FROM dbo.LCF_Data
    		WHERE SysCounter = @LCF_DataPointer
    	SET @CF_NumLCFLines = (SELECT TOP 1 SysCounter FROM LCF_Data
    		WHERE CF_Datum = @CF_SysCounter)
    	IF @CF_NumLCFLines IS NULL
    		DELETE FROM dbo.CF_Data WHERE SysCounter = @CF_SysCounter
    	RETURN @CF_NumLCFLines
    END
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    This is what I have,

    Code:
    INSERT INTO EXP_DATA_2
    SELECT A.PERIOD, A.ACTUALITY, A.COMPANY, A.CURRENCY, B.CC_ACCOUNT,(B.SPLIT * A.AMOUNT)AMOUNT ,A.EXTDIM1,A.EXTDIM2,    A.EXTDIM3,A.EXTDIM4,A.JOURNAL_TYPE,A.C_COMPANY,A.C_DIM,A.TRANAMOUNT,A.TRANCURR,A.REGION,
               A.TRANSF_DATE
            FROM EXP_DATA AS A,
                    CC_SPLIT AS B
    WHERE A.COMPANY = B.COMPANY AND A.ACCOUNT = B.FUNC_ACCOUNT
    AND A.PERIOD IN ('1102', '1103', '1104')
    So to make this as a store procedure will it be like this???

    Code:
    USE [Database]
    GO
    CREATE PROCEDURE [dbo].[Procedurename]
    (
    	@LCF_DataPointer INT 
    )
    AS
    BEGIN
    	SELECT A.PERIOD, A.ACTUALITY, A.COMPANY, A.CURRENCY, B.CC_ACCOUNT,(B.SPLIT * A.AMOUNT)AMOUNT ,A.EXTDIM1,A.EXTDIM2,    A.EXTDIM3,A.EXTDIM4,A.JOURNAL_TYPE,A.C_COMPANY,A.C_DIM,A.TRANAMOUNT,A.TRANCURR,A.REGION,
               A.TRANSF_DATE
            FROM EXP_DATA AS A,
                    CC_SPLIT AS B
    WHERE A.COMPANY = B.COMPANY AND A.ACCOUNT = B.FUNC_ACCOUNT
    AND A.PERIOD IN ('1102', '1103', '1104')
    END

    Thanks in advance

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not exactly.

    1. You need to replace several parameters in my example:
    Code:
    USE [Database]
    Database must be replaced by the name of the database where the procedure will be stored.

    Code:
    CREATE PROCEDURE [dbo].[Procedurename]
    (
    	@LCF_DataPointer INT 
    )
    AS
    Procedurename must be replaced by the name you want to give to the stored procedure. Moreover, as you don't use any parameter in it, you must remove the parameters declaration part:
    Code:
    CREATE PROCEDURE [dbo].[YourProcedureName]
    
    AS
    3. This:
    Code:
    (B.SPLIT * A.AMOUNT)AMOUNT ,
    should be:
    Code:
    (B.SPLIT * A.AMOUNT) AS AMOUNT ,
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    52
    Sindho, many thanks for your help I will try this out tomorrow and let you know. Have a nice day

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Jan 2012
    Posts
    52
    Hi, I run the store procedure, based on your answers, I get no errors when running the code, however it does not do anything. I mean I want to run this procedure so it inserts the data into exp_Data2 as the code do, but when I run this procedure, this does not happen.

    Do you happen to know why?

  8. #8
    Join Date
    Jan 2012
    Posts
    52
    Stupid question of mine I run the procedure but never executed. Thanks anyway

Posting Permissions

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