How to declare parameters inside "select"?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » How to declare parameters inside "select"?

How to declare parameters inside "select"?

Rate Topic

Display Mode

Topic Options

Author

Message

pockeyfan

Posted Friday, August 08, 2008 2:06 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, September 01, 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

Ken McKelvey

Posted Friday, August 08, 2008 4:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:46 AM
Points: 822, Visits: 5,100

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

pockeyfan

Posted Monday, August 11, 2008 1:35 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, September 01, 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,

	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 »

Permissions