Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    156

    Unanswered: Stored Proc help - Basic

    Hi there,

    I'm very new to this and I need your advice. I am looking to create a stored procedure, but maybe it's not the best way, maybe there is another way, your advice is appreciated.

    I am creating crystal reports for an application and they are displayed through a web portal. The limitation on the print functionality of this portal is that it cannot deal with sub reports. I have been told to create a stored proc and to query it for the results.

    The scenario is simple. There are 3 tables namely controls, account_controls and accounts. The objective is to return all accounts linked to one control (each control has one or many accounts)

    So i thought, create a stored procedure based on the following query...

    SELECT ACCOUNT.DESCRIPTION, CONTROLS.CONTROL_ID, ACCOUNT.TITLE
    FROM (ACCOUNT_CONTROL ACCOUNT_CONTROL
    INNER JOIN ACCOUNT ACCOUNT
    ON (ACCOUNT_CONTROL.ACCOUNT_ID=ACCOUNT.ACCOUNT_ID)
    AND (ACCOUNT_CONTROL.LABEL_ID=ACCOUNT.LABEL_ID))
    INNER JOIN CONTROLS CONTROLS
    ON (ACCOUNT_CONTROL.CONTROL_ID=CONTROLS.CONTROL_ID)
    AND (ACCOUNT_CONTROL.LABEL_ID=CONTROLS.LABEL_ID)

    This would return something like...

    Description ¦ Contol_id ¦ Title
    --------------------------------
    NULL ¦ id_1 ¦salaries
    NULL ¦ id_1 ¦bonuses
    NULL ¦ id_1 ¦benifits

    So... all i need to do is for each control_id return the accounts, but I need to be able to concatenate them eg.

    salaries, bonuses, benifits

    Should I populate a temp table for example?

    Please advise,

    kind regards.
    rgs,

    Ghostman

  2. #2
    Join Date
    May 2006
    Posts
    16
    If you want to concatenate your results, you have to use Stored Procedure.

    You need create a temp table and create the columns dynamically through the accounts item, because maybe the different control_ID can have different account_items.

    If I do, I will Select distinct from "you items" table to get all account items, and then do loop to create a temp table, after that populate the value.

  3. #3
    Join Date
    Sep 2003
    Posts
    156
    Thanks for the reply. I spoke to someone else yesterday who suggested the same thing. I will try this and let you know of the results.

    Regards,
    rgs,

    Ghostman

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    or use Pivot + concatenation... but it's quite complex...
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    or a function
    Code:
    CREATE FUNCTION dbo.ConcatFields
     (
     @Control_id AS VarChar(8)
     )
     RETURNS VarChar(100)
    AS
     BEGIN
     DECLARE @Return AS VarChar(100)
     
     SELECT @Return = COALESCE(@Return, '') + ', ' + Title
     FROM ACCOUNT_CONTROL  
       INNER JOIN ACCOUNT ON 
      ACCOUNT_CONTROL.ACCOUNT_ID=ACCOUNT.ACCOUNT_ID
      AND ACCOUNT_CONTROL.LABEL_ID=ACCOUNT.LABEL_ID 
     WHERE ACCOUNT_CONTROL.CONTROL_ID = @Control_id
      
     RETURN SUBSTRING(@Return, 3, LEN(@Return)-2)
    END
    Called like this:
    Code:
    SELECT ACCOUNT.DESCRIPTION, CONTROLS.CONTROL_ID, dbo.ConcatValues(CONTROLS.CONTROL_ID)
    FROM (ACCOUNT_CONTROL ACCOUNT_CONTROL 
    INNER JOIN ACCOUNT ACCOUNT 
    ON (ACCOUNT_CONTROL.ACCOUNT_ID=ACCOUNT.ACCOUNT_ID)
    AND (ACCOUNT_CONTROL.LABEL_ID=ACCOUNT.LABEL_ID)) 
    INNER JOIN CONTROLS CONTROLS
    ON (ACCOUNT_CONTROL.CONTROL_ID=CONTROLS.CONTROL_ID) 
    AND (ACCOUNT_CONTROL.LABEL_ID=CONTROLS.LABEL_ID)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Posts
    156
    Fadace - isn't pivot natve to SQLServer 2005?

    Pootleflump - I have it working.... what I did was I created a function (that does not make use of a cursor)

    ------------------------------------------------------------------

    CREATE FUNCTION dbo.f_linked_accounts (@control_did varchar(256))
    RETURNS varchar(1000) AS

    BEGIN
    DECLARE @RowCnt int
    DECLARE @MaxRows int
    DECLARE @linkedaccounts varchar(256)
    DECLARE @global_control_id int
    DECLARE @accountstr varchar (1000)

    SELECT @RowCnt = 1
    SET @accountstr=''

    DECLARE @Import TABLE ( rownum int IDENTITY (1, 1) Primary key NOT NULL,
    Control_id int,global_control_id int, Title varchar(256) )

    INSERT INTO @Import
    SELECT dbo.NTH_RPT_CONTROLS.CONTROL_ID, dbo.NTH_RPT_CONTROLS.GLOBAL_CONTROL_ID, dbo.NTH_RPT_ACCOUNT.TITLE
    FROM dbo.NTH_RPT_ACCOUNT_CONTROL INNER JOIN dbo.NTH_RPT_ACCOUNT ON dbo.NTH_RPT_ACCOUNT_CONTROL.ACCOUNT_ID=dbo.NTH_RPT _ACCOUNT.ACCOUNT_ID
    AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_A CCOUNT.LABEL_ID
    INNER JOIN dbo.NTH_RPT_CONTROLS
    ON dbo.NTH_RPT_ACCOUNT_CONTROL.CONTROL_ID=dbo.NTH_RPT _CONTROLS.CONTROL_ID
    AND dbo.NTH_RPT_ACCOUNT_CONTROL.LABEL_ID=dbo.NTH_RPT_C ONTROLS.LABEL_ID
    WHERE dbo.NTH_RPT_CONTROLS.CONTROL_DID = @control_did

    SELECT @MaxRows=count(*) from @Import

    WHILE @RowCnt <= @MaxRows

    BEGIN

    SELECT @linkedaccounts = title, @global_control_id = global_control_id
    FROM @Import
    WHERE rownum = @RowCnt

    SELECT @RowCnt = @RowCnt + 1
    SET @accountstr = @accountstr + @linkedaccounts +', '

    END
    RETURN @accountstr
    END

    ------------------------------------------------------------------

    The I created a view...

    ------------------------------------------------------------------

    CREATE VIEW dbo.linked_accounts
    AS
    SELECT control_did, dbo.f_linked_accounts(dbo.nth_rpt_controls.control _did) AS account_names
    FROM dbo.nth_rpt_controls

    ------------------------------------------------------------------


    I can then drag and drop the account_names onto my crystal report fmr the view (which receives the control_did)

    It all works :¬)

    Thanks to everyone for your ideas.

    Regards,
    rgs,

    Ghostman

Posting Permissions

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