Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to declare parameters inside "select"? Expand / Collapse
Author
Message
Posted Friday, August 8, 2008 2:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 1, 2008 12:50 AM
Points: 17, Visits: 46
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!
Post #548922
Posted Friday, August 8, 2008 4:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 848, Visits: 5,523
I would be inclined to use a function. Something like:

SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO
CREATE FUNCTION dbo.GetAssetNames
(
    @policy_number varchar(20) -- or whatever the datatype of policy_number is.
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @AssetNames varchar(8000)
    SET @AssetNames = ''

    SELECT @AssetNames = @AssetNames
        + CASE
            WHEN CHARINDEX('(', asset_name) > 0
            THEN LEFT(asset_name, CHARINDEX('(', asset_name) - 1)
            ELSE asset_name
        END
        + '#'
    FROM asset
    WHERE policy_number = @policy_number

    RETURN LEFT(@AssetNames, LEN(@AssetNames) - 1)
END
GO

SELECT
    policy_number
    ,ulp_order_id
    ,dbo.GetAssetNames(policy_number) AS asset_name
FROM ulp_order

Post #548982
Posted Monday, August 11, 2008 1:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 1, 2008 12:50 AM
Points: 17, Visits: 46
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,

Post #550060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse