Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Must declare the scalar variable "@User". Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 3:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Hi,

I ran the below SQL query and got this error. Can anyone help me fix this.



DECLARE @CCSG bit

SET @CCSG = CASE WHEN EXISTS(SELECT 1
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD
WHERE BSP_LOB_GRP = 'CCSG Group'
AND up.UserName=@User
)
THEN 1 ELSE 0 END


SELECT *
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD
WHERE (up.UserName=@User AND @CCSG = 0)
OR (BSP_LOB_GRP IN ('LOB Group','Site Group','Sales Group','CCSG Group') AND @CCSG = 1)




Msg 137, Level 15, State 2, Line 11

Must declare the scalar variable "@User".

Msg 137, Level 15, State 2, Line 22

Must declare the scalar variable "@User".
Post #1505555
Posted Thursday, October 17, 2013 3:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 22,992, Visits: 31,472
The error message is telling you exactly what you need to do. You need to declare the variable @User before you use it in your query.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1505556
Posted Thursday, October 17, 2013 3:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Hi,

I am new to SQL and where should I declare that. I have no idea. If possible can you correct my query.
Post #1505558
Posted Thursday, October 17, 2013 3:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 22,992, Visits: 31,472
vigneshlagoons (10/17/2013)
Hi,

I am new to SQL and where should I declare that. I have no idea. If possible can you correct my query.



DECLARE @CCSG bit
DECLARE @User ?? -- (?? = whatever data type this should be)

SET @User = ?? -- (?? whatever value that should be used, if a string surrounded by single quotes)

SET @CCSG = CASE WHEN EXISTS(SELECT 1
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD
WHERE BSP_LOB_GRP = 'CCSG Group'
AND up.UserName=@User
)
THEN 1 ELSE 0 END


SELECT *
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD
WHERE (up.UserName=@User AND @CCSG = 0)
OR (BSP_LOB_GRP IN ('LOB Group','Site Group','Sales Group','CCSG Group') AND @CCSG = 1)





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1505562
Posted Thursday, October 17, 2013 4:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Thanks a lot Lynn, it worked great.
Post #1505570
Posted Thursday, October 17, 2013 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Hi Lynn,

Is it possible to sort the User_Group column in this query.

For Instance:User_Group column has values like this
Hi
Hey
Hey
Hi

Expected Output should be like this
Hi
Hi
Hey
Hey


DECLARE @CCSG varchar
DECLARE @User1 varchar
SET @CCSG = CASE WHEN EXISTS(SELECT 1
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD
WHERE BSP_LOB_GRP = 'Sales Group'
AND up.User_Id='Russell'
)
THEN 1 ELSE 0 END

SELECT *
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD
WHERE (up.User_Id='Russell' AND @CCSG = 0)
OR (BSP_LOB_GRP IN ('LOB Group','Site Group','Sales Group','CCSG Group') AND @CCSG = 1)
Post #1505580
Posted Thursday, October 17, 2013 4:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
Add an ORDER BY clause to the end of the query, specifying the column that it needs to be ordered by?
Check Books Online if you're unsure of the syntax of an Order By



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1505582
Posted Thursday, October 17, 2013 4:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:34 PM
Points: 33, Visits: 151
Thanks a lot, it worked!
Post #1505587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse