October 17, 2013 at 3:40 am
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".
October 17, 2013 at 3:45 am
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.
October 17, 2013 at 3:49 am
Hi,
I am new to SQL and where should I declare that. I have no idea. If possible can you correct my query.
October 17, 2013 at 3:53 am
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)
October 17, 2013 at 4:06 am
Thanks a lot Lynn, it worked great.
October 17, 2013 at 4:34 am
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)
October 17, 2013 at 4:37 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2013 at 4:43 am
Thanks a lot, it worked!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply