HELP Only one expression can be specified in the s

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 2005 Forums

Transact-SQL (2005)

HELP Only one expression can be specified in the s

Author

Topic

clint_pow
Starting Member

1 Post

Posted - 2008-07-13 : 21:44:40

Hi,

Can anyone help me regarding the error above heres my SQL below :
The SELECT statement works fine but when I set DECLARE problem exist what may cause of this.
Thank you very much. Clint

DECLARE @CtrRef AS Int

SET @CtrRef = (SELECT BaseRef,
COUNT(DocEntry)
FROM
(SELECT
dbo.RIN1.BaseRef,
dbo.RIN1.DocEntry
FROM
dbo.RIN1
GROUP BY
dbo.RIN1.BaseRef,
dbo.RIN1.DocEntry) CountBaseRef

GROUP BY
BaseRef)

SELECT @CtrRef

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-13 : 21:48:53

Because you need Dynamic SQL for that

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-13 : 22:27:11

You only declared 1 parameter, how can you put 2 numbers in it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 00:38:19

Probably you need this if you've only single row returned in result set:-

DECLARE @CtrRef AS Int,@CountValue as int

SELECT @CtrRef =  BaseRef,
@CountValue=COUNT(DocEntry)
FROM
(SELECT 
dbo.RIN1.BaseRef,
dbo.RIN1.DocEntry
FROM
dbo.RIN1
GROUP BY
dbo.RIN1.BaseRef,
dbo.RIN1.DocEntry) CountBaseRef

GROUP BY
BaseRef

or put it in a temporary table if you've more than 1 row in result set

DECLARE @temp table
(
CtrRef  int,
CountValue int)

INSERT INTO @temp
SELECT BaseRef,
COUNT(DocEntry)
FROM
(SELECT 
dbo.RIN1.BaseRef,
dbo.RIN1.DocEntry
FROM
dbo.RIN1
GROUP BY
dbo.RIN1.BaseRef,
dbo.RIN1.DocEntry) CountBaseRef

GROUP BY
BaseRef

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources