Problem with select variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with select variable

Author

Topic

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-10-17 : 11:23:16

Can someone help me with a SQL Server 2000 coding issue.

I run the following code:
select @rpi = fgr.report_group_identifier
FROM fee_grouping_report_v fgr (NOLOCK)

WHERE fgr.tran_type in (462)--,463)
AND fgr.report_id in (100,148)
select @rpi

This code should output:
report_group_identifier
----------------------------------------
DMV fee
dmv_fee

But it is only outputing:
report_group_identifier
----------------------------------------
DMV fee

Can anyone tell me why it doesn't output the "dmv_fee"? Is there an issue with the underscore character here?

Thanks!
Dustin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 11:26:41

because a vraible can hold only a single value. you need to use a table variable for this

declare @rpi table
(
idval varchar(100)
)
insert @rpi (idval)
select fgr.report_group_identifier
FROM fee_grouping_report_v fgr (NOLOCK)
WHERE fgr.tran_type in (462)--,463)
AND fgr.report_id in (100,148)

select idval from @rpi

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-10-17 : 11:37:23

But when I add this to the end of the query, the result is NULL.

select @rpi = fgr.report_group_identifier
FROM fee_grouping_report_v fgr (NOLOCK)

WHERE fgr.tran_type in (462)--,463)
AND fgr.report_id in (100,148)
AND fgr.report_group_identifier = 'dmv_fee'

select @rpi

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-10-17 : 11:39:32

But if I run this select directly:
select report_group_identifier from fee_grouping_report where tran_type in (462) and report_id in (100,148) and report_group_identifier = 'dmv_fee'

It returns the following:
report_group_identifier
----------------------------------------
dmv_fee

Oh, I forgot to mention that the @rpi variable is declared as varchar(40)

Thanks!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-17 : 11:58:54

Did you read Viaskh's post?

When you do a


SELECT
    @variable = [col]
FROM
    x

What actually happens is that @variable gets assigned the values in the col one after another until the last (ordered in no guaranteed way) row. The variable will then hold the value of one of the rows in the column.

I think you should post what you want to accomplish in the end. I doubt that your code is doing as you want it to.

Visakh suggested you could do something like this.


DECLARE @rpi TABLE (
	[report_group_identifier] VARCHAR(255)
	)

INSERT @rpi
SELECT
	fgr.report_group_identifier
FROM
	fee_grouping_report_v fgr (NOLOCK)
WHERE
	fgr.tran_type in (462)--,463)
	AND fgr.report_id in (100,148)

SELECT * FROM @rpi

Post some sample data and explain what you need to generate from it.

Regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources