getting value from the variable--error

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)

getting value from the variable--error

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2008-12-08 : 05:54:56

hi,

i have written a query in the sp as below later am using that var..
here am getting more than 1 sbd_item_code then it was giving error and i tried like this it is also giving error.

DECLARE @Review_Defects INT
SELECT @Review_Defects in (select (SBD_ITEM_CODE)
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review'))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 05:57:39

DECLARE @Review_Defects INT
SELECT @Review_Defects = SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review')

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 05:57:44

you need a table for storing more than 1 values

DECLARE @Review_Defects table
(value int)
INSERT @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL 
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review'))

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-08 : 05:58:48

Try this

DECLARE @Review_Defects TABLE
(
R_Defects INT
)
INSERT INTO @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review')

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-08 : 06:04:37

quote:

Originally posted by visakh16

you need a table for storing more than 1 values

DECLARE @Review_Defects table
(value int)
INSERT @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL 
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review'))

Remove ')' which is extra closing bracket

shm
Yak Posting Veteran

86 Posts

Posted - 2008-12-08 : 06:12:21

thank you for all..

i tried, but when i executed this, i want to use that var..how it shld be taken?last select statment i had given it is coming as error :Must declare the scalar variable "@Review_Defects".
how i shld take that?

DECLARE @Review_Defects table
(value int)
INSERT @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review')

select @Review_Defects.r_defect

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-08 : 06:16:22

quote:
Originally posted by shm

thank you for all..

i tried, but when i executed this, i want to use that var..how it shld be taken?last select statment i had given it is coming as error :Must declare the scalar variable "@Review_Defects".
how i shld take that?

DECLARE @Review_Defects table
(value int)
INSERT @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review')

select @Review_Defects.r_defect

select r_defect from @Review_Defects

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-08 : 06:19:33

use either anyone of the below but not both

--block 1:
DECLARE @Review_Defects TABLE
(
R_Defects INT
)
INSERT INTO @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review')

select R_Defects from @Review_Defects

--Block 2:

DECLARE @Review_Defects table
(value int)
INSERT @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review')

select value from @Review_Defects

shm
Yak Posting Veteran

86 Posts

Posted - 2008-12-08 : 06:48:16

i forgeted that as a table,ok i got that now
but wt am trying is ,in the query i shld take that variable for the value pbg_phase_item_code,for ex:select value from @review_defects has 3 rows means 3 value am getting..if i place this am getting error...this is sp query am using the sp to the reporting services...

select
SUM(CASE WHEN ((PBG_STATUS_ITEM_CODE <> @NotABugCode) AND (PBG_PHASE_ITEM_CODE = ? ))THEN 1 ELSE 0 END) As Review_Defects

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 08:36:40

quote:
Originally posted by shm

i forgeted that as a table,ok i got that now
but wt am trying is ,in the query i shld take that variable for the value pbg_phase_item_code,for ex:select value from @review_defects has 3 rows means 3 value am getting..if i place this am getting error...this is sp query am using the sp to the reporting services...

select
SUM(CASE WHEN ((PBG_STATUS_ITEM_CODE <> @NotABugCode) AND (PBG_PHASE_ITEM_CODE = ? ))THEN 1 ELSE 0 END) As Review_Defects

you need to join your query with the table variable you created and then take values from it. would you mind explaining us what exactly are you trying to do in main query and what was table variable you created used for?

shm
Yak Posting Veteran

86 Posts

Posted - 2008-12-15 : 07:48:42

hi

in the query i dnt want to hard code the values..
in the query wt ever am getting it is more than one row as i mentioned earlier.

DECLARE @Review_Defects table
(value int)
INSERT @Review_Defects
SELECT SBD_ITEM_CODE
FROM SYS_BUSINESS_CODE_DETAIL
WHERE SBM_TYPE_CODE = 'PH'
AND SBD_ITEM_DESC IN ('Code Review','Design Review','Requirements','Test Case Review')

more than 1 row from this query
and

select
SUM(CASE WHEN ((PBG_STATUS_ITEM_CODE <> @NotABugCode) AND (PBG_PHASE_ITEM_CODE = ? ))THEN 1 ELSE 0 END) As Review_Defects

in the above query i should take those rows value into this PBG_PHASE_ITEM_CODE .if i use = only one value il pass if i use in it il give error..for that i want a query how i can take that in the select statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 09:17:18

seem like this is what you want

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources