How to declare parameters inside "select"?

  • Hi, here is my problem:

    As the following codes shown, I want to retrieve some data from several tables to a temprary table "#tmp_437", the problem is "asset_name" could not store two or more "asset.asset_name" under one condition:

    select

    uo.policy_number,

    uo.ulp_order_id,

    case when charindex('(', asset.asset_name) > 0 then

    left(asset.asset_name, charindex('(', asset.asset_name) - 1)

    else

    asset.asset_name

    end

    asset_name,

    ......................

    into #tmp_437

    from

    ulp_order uo,

    asset,

    ............

    where

    ........

    For one "ulp_order_id", there may be several "asset_name" assigned. I want to get all the "asset.asset_name" and store them into "asset_name":

    declare policy_product_cur cursor for

    select uo.policy_number,

    case when charindex('(', a.asset_name) > 0 then

    left(a.asset_name, charindex('(', a.asset_name) - 1)

    else

    a.asset_name

    end

    asset_name

    from ulp_order uo, asset a

    where uo.product_id = a.asset_id and

    uo.order_type = 'R'

    order by ulp_order_id

    for read only

    open policy_product_cur

    fetch policy_product_cur

    into @tmp_policy_number, @tmp_asset_name

    while @@sqlstatus != 2

    begin

    if @@sqlstatus = 1

    begin

    return

    end

    update #tmp_437

    set asset_name = asset_name + '#' + @tmp_asset_name

    where #tmp_437.policy_number = @tmp_policy_number

    fetch policy_product_cur

    into @tmp_policy_number, @tmp_asset_name

    end

    I add a "#" between two "asset_name" for late use in Crystal Report. The problem is "asset_name" is not longer enough to store all the "asset.asset_name". For example, for a certain ulp_order_id, the "asset_name" should be "Premier Life Critical Illness Protector#Hospital Income Benefit", however, the result I get is "Premier Life Critical Illness Protector#Hospital Income Bene", it could not store all the characters.

    In table "asset", the "asset_name" is varchar(60), I'm afraid when I do select at the beginning, "asset_name" is also defaultly declared as varchar(60), so it could not store variables longer than 60 characters.

    So is there any way to declare "asset_name" as varchar(100) during "select"? Or is there any simple way to solve this problem?

    I tried to "create table #tmp_437" before "select" and decalre "asset_name" as varchar(100), however, error message always shown as "Attempt to insert NULL value into column 8 in work table (table id -xxxxxx)"...There will be a lot of things need to be modified...

    So I ask for some simple methods to store all the "asset.asset_name" without using "create table...".

    Thanks a lot!

  • I would be inclined to use a function. Something like:

    SET QUOTED_IDENTIFIER, ANSI_NULLS ON

    GO

    CREATE FUNCTION dbo.GetAssetNames

    (

    &nbsp&nbsp&nbsp&nbsp@policy_number varchar(20) -- or whatever the datatype of policy_number is.

    )

    RETURNS varchar(8000)

    AS

    BEGIN

    &nbsp&nbsp&nbsp&nbspDECLARE @AssetNames varchar(8000)

    &nbsp&nbsp&nbsp&nbspSET @AssetNames = ''

    &nbsp&nbsp&nbsp&nbspSELECT @AssetNames = @AssetNames

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+ CASE

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHEN CHARINDEX('(', asset_name) > 0

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspTHEN LEFT(asset_name, CHARINDEX('(', asset_name) - 1)

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspELSE asset_name

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEND

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+ '#'

    &nbsp&nbsp&nbsp&nbspFROM asset

    &nbsp&nbsp&nbsp&nbspWHERE policy_number = @policy_number

    &nbsp&nbsp&nbsp&nbspRETURN LEFT(@AssetNames, LEN(@AssetNames) - 1)

    END

    GO

    SELECT

    &nbsp&nbsp&nbsp&nbsppolicy_number

    &nbsp&nbsp&nbsp&nbsp,ulp_order_id

    &nbsp&nbsp&nbsp&nbsp,dbo.GetAssetNames(policy_number) AS asset_name

    FROM ulp_order

  • Thanks, Ken

    Someone else suggests a much simpler way:

    change

    case when charindex('(', asset.asset_name) > 0 then

    left(asset.asset_name, charindex('(', asset.asset_name) - 1)

    else

    asset.asset_name

    end

    asset_name,

    to

    CAST(CASE WHEN charindex('(', asset.asset_name) > 0 THEN

    LEFT (asset.asset_name, charindex('(', asset.asset_name) - 1)

    ELSE asset.asset_name

    END AS VARCHAR(1000)) AS asset_name,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply