SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to declare parameters inside "select"?


How to declare parameters inside "select"?

Author
Message
pockeyfan
pockeyfan
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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!
Ken McKelvey
Ken McKelvey
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 7914
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


pockeyfan
pockeyfan
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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,


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search