October 18, 2013 at 3:25 am
Hi All,
I want this query to be used as a stored procedure. Can anyone suggest or help me on this. As I need to remove, DECLARE bits and add them as parameters in the procedure. And I am not sure how to change them.
DECLARE @ReportingViews varchar
DECLARE @User varchar
SET @ReportingViews= 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 = 'Site Group'
AND up.User_Id= USER_ID
)
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= USER_ID AND @ReportingViews = 0)
OR (BSP_LOB_GRP IN ('LOB Group','Site Group','CCSG Group') AND @ReportingViews = 1)
ORDER BY USER_GROUP
Is something like this is fine?
CREATE PROC ProcName
@LOBName varchar(100)
AS
DECLARE @ReportingViews bit
SET @ReportingViews= 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 = @LOBName
AND up.User_Id= USER_ID
)
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 @ReportingViews = 0
OR BSP_LOB_GRP IN ('LOB Group','Site Group','CCSG Group')
ORDER BY USER_GROUP
GO
October 18, 2013 at 3:44 am
CREATE PROC dbo.myProc
@ReportingViews varchar(50)
@User varchar(50)
AS
SET @ReportingViews = CASEWHEN 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 = 'Site Group' AND up.User_Id= USER_ID
)
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= USER_ID AND @ReportingViews = 0)
OR(BSP_LOB_GRP IN ('LOB Group','Site Group','CCSG Group') AND @ReportingViews = 1
)
ORDER BY USER_GROUP;
ps: I'd assign a size to your varchar variables. Do you know what the default size is?
pps: if you only want 1 chararcter, use char instead of varchar
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 3:55 am
And don't forget to give execution permissions if needed.
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 18, 2013 at 5:51 am
Hi Jony,
I use this query and I get to see duplicate rows showing up for each row.
CREATE PROC Rep
AS
DECLARE @ReportingViews varchar
DECLARE @User varchar
SET @ReportingViews= 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 = 'Site Group'
AND up.User_Id= USER_ID
)
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= USER_ID AND @ReportingViews = 0)
OR (BSP_LOB_GRP IN ('LOB Group','Site Group','CCSG Group') AND @ReportingViews = 1)
ORDER BY USER_GROUP
October 18, 2013 at 5:55 am
vigneshlagoons (10/18/2013)
Hi Jony,I use this query and I get to see duplicate rows showing up for each row.
CREATE PROC Rep
AS
DECLARE @ReportingViews varchar
DECLARE @User varchar
SET @ReportingViews= 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 = 'Site Group'
AND up.User_Id= USER_ID
)
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= USER_ID AND @ReportingViews = 0)
OR (BSP_LOB_GRP IN ('LOB Group','Site Group','CCSG Group') AND @ReportingViews = 1)
ORDER BY USER_GROUP
Without the table DDL of the tables involved, sample data and some sort of business logic, it will be very very hard to help you.
Please read the first link in my signature on how to post questions.
By the way, why are you using a varchar for @ReportingViews. Wouldn't a BIT make more sense?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 5:56 am
Use the distinct clause :
SELECT distinct *
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= USER_ID AND @ReportingViews = 0)
OR(BSP_LOB_GRP IN ('LOB Group','Site Group','CCSG Group') AND @ReportingViews = 1
)
ORDER BY USER_GROUP;
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply