cimscims
asked on
Building WHERE clause in Stored Procedure
I have 3 parameters for a stored procedure.
Desc1 Year Desc2
08-100 2008 PERM-1
09-110 2009 PERM-2
10-289 2010 PERM-3
I want to construct a WHERE clause like below
(Desc1 = ’08-100’ AND Year=’2008’ AND ‘Desc2’ = ‘PERM-1’) AND
(Desc1 = ’09-110’ AND Year=’2009’ AND ‘Desc2’ = ‘PERM-2’) AND
(Desc1 = ’10-289’ AND Year=’2010’ AND ‘Desc2’ = ‘PERM-3’)
Would someone please help me on this
Desc1 Year Desc2
08-100 2008 PERM-1
09-110 2009 PERM-2
10-289 2010 PERM-3
I want to construct a WHERE clause like below
(Desc1 = ’08-100’ AND Year=’2008’ AND ‘Desc2’ = ‘PERM-1’) AND
(Desc1 = ’09-110’ AND Year=’2009’ AND ‘Desc2’ = ‘PERM-2’) AND
(Desc1 = ’10-289’ AND Year=’2010’ AND ‘Desc2’ = ‘PERM-3’)
Would someone please help me on this
ASKER
The three parameters for the SP that have values as below
Desc1 --> '08-100,09-110,10-289'
Year --> '2008','2009','2010'
Desc2 --> 'PERM-1,PERM-2,PERM-3'
I have the stored procedure as below
CREATE PROCEDURE usp_XXXXX(Desc1 varchar(2000),Year varchar(2000),Desc2 varchar(2000)
AS
BEGIN
SELECT col1,col2...
FROM table1 A
outer join table2 B ON A.Id = B.Id
outer join table3 C ON A.Id = C.Id
WHERE
(Desc1 = ’08-100’ AND Year=’2008’ AND ‘Desc2’ = ‘PERM-1’) AND
(Desc1 = ’09-110’ AND Year=’2009’ AND ‘Desc2’ = ‘PERM-2’) AND
(Desc1 = ’10-289’ AND Year=’2010’ AND ‘Desc2’ = ‘PERM-3’)
END
As you see in the above procedure, i want to construct a WHERE clause.
Let me know if i am not clear. Thanks
Desc1 --> '08-100,09-110,10-289'
Year --> '2008','2009','2010'
Desc2 --> 'PERM-1,PERM-2,PERM-3'
I have the stored procedure as below
CREATE PROCEDURE usp_XXXXX(Desc1 varchar(2000),Year varchar(2000),Desc2 varchar(2000)
AS
BEGIN
SELECT col1,col2...
FROM table1 A
outer join table2 B ON A.Id = B.Id
outer join table3 C ON A.Id = C.Id
WHERE
(Desc1 = ’08-100’ AND Year=’2008’ AND ‘Desc2’ = ‘PERM-1’) AND
(Desc1 = ’09-110’ AND Year=’2009’ AND ‘Desc2’ = ‘PERM-2’) AND
(Desc1 = ’10-289’ AND Year=’2010’ AND ‘Desc2’ = ‘PERM-3’)
END
As you see in the above procedure, i want to construct a WHERE clause.
Let me know if i am not clear. Thanks
I understood. Good Question!
You need to use split function or use that code in your stored procedure.
I will try to help. Or by that time some other experts may post before me
Raj
You need to use split function or use that code in your stored procedure.
I will try to help. Or by that time some other experts may post before me
Raj
Would the count of values alway three (as in your example) or it could vary ?
eg:- '08-100,09-110,10-289' - have three data
Raj
eg:- '08-100,09-110,10-289' - have three data
Raj
ASKER
There is no specific count. It could vary from 1 to any number (may be in hundreds).
ASKER
I have used Split and tried to customize but i am getting a syntax error at SET @where statement. Would you pls help me out.
SP.txt
SP.txt
So you are trying to create a dynamic query
The error is that - in your query acts as substract operator - which is not valid for varchar
Raj
The error is that - in your query acts as substract operator - which is not valid for varchar
Raj
This is the WHERE part you are trying to create dynamically, right ?
I.Desc1 = @sliceDesc1
AND I.Year = @sliceYear
AND I.Desc2 = @sliceDesc2
Check it with your current WHERE part, it have some issues
It should be simply
SET @where = 'I.Desc1 = ' + @sliceDesc1 + ' AND I.Year = ' + @sliceYear + ' AND I.Desc2 = ' + @sliceDesc2
SET @where = 'I.Desc1 = ' + @sliceDesc1 + ' AND I.Year = ' + @sliceYear + ' AND I.Desc2 = ' + @sliceDesc2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am correcting the WHERE clause. But the Where clause is fecthing the first row three times. I will look into this and post back.
ASKER
I will replace AND with OR
ASKER
Thanks for your time Raj.
Glad I could assist you.
I am posting another solution that I have. I am not sure this will more better than your current solution.
Anyway posting
1. Create this function
2. Then use this query inside your stored procedure - Uncomment Collate if any collate error
Instead of '08-100,09-110,10-289' use the parameter - @Desc1 etc
I am posting another solution that I have. I am not sure this will more better than your current solution.
Anyway posting
1. Create this function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION ConvertCSDataToTable
(
@Desc1 VARCHAR(8000),
@Year VARCHAR(8000),
@Desc2 VARCHAR(8000)
)
RETURNS @Items table
(
Desc1 VARCHAR(8000),
Year VARCHAR(8000),
Desc2 VARCHAR(8000))
AS
BEGIN
DECLARE @Desc1Temp As varchar(8000), @YearTemp AS VARCHAR(8000), @Desc2Temp AS VARCHAR(8000)
DECLARE @Pos1 As int, @Pos2 As int, @Pos3 As int
WHILE DATALENGTH(@Desc1)>0
BEGIN
SET @Pos1=CHARINDEX(',',@Desc1)
IF @Pos1=0 SET @Pos1=DATALENGTH(@Desc1)+1
SET @Desc1Temp = LTRIM(RTRIM(LEFT(@Desc1,@Pos1-1)))
SET @Desc1=SUBSTRING(@Desc1,@Pos1+DATALENGTH(','),8000)
SET @Pos2=CHARINDEX(',',@Desc2)
IF @Pos2=0 SET @Pos2=DATALENGTH(@Desc2)+1
SET @Desc2Temp = LTRIM(RTRIM(LEFT(@Desc2,@Pos2-1)))
SET @Desc2=SUBSTRING(@Desc2,@Pos2+DATALENGTH(','),8000)
SET @Pos3=CHARINDEX(',',@Year)
IF @Pos3=0 SET @Pos3=DATALENGTH(@Year)+1
SET @YearTemp = LTRIM(RTRIM(LEFT(@Year,@Pos3-1)))
SET @Year=SUBSTRING(@Year,@Pos3+DATALENGTH(','),8000)
INSERT INTO @Items VALUES (@Desc1Temp, @YearTemp, @Desc2Temp)
END
RETURN
END
2. Then use this query inside your stored procedure - Uncomment Collate if any collate error
Instead of '08-100,09-110,10-289' use the parameter - @Desc1 etc
SELECT a.*
FROM #table A,
abaxuser.ConvertCSDataToTable( '08-100,09-110,10-289', '2008,2009,2010', 'PERM-1,PERM-2,PERM-3') B
WHERE a.Desc1 = b.Desc1 -- COLLATE SQL_Latin1_General_CP1_CI_AS
AND a.Year = b.Year -- COLLATE SQL_Latin1_General_CP1_CI_AS
AND a.Desc2 = b.Desc2 -- COLLATE SQL_Latin1_General_CP1_CI_AS
Instead of 'abaxuser', use 'dbo'
This is the way to create a stored procedure using those three parameters
CREATE PROCEDURE usp_YourStoredProcedure
(
@Desc1 VARCHAR(50),
@Year INT,
@Desc2 VARCHAR(50)
)
AS
BEGIN
SELECT * FROM YourTable WHERE Desc1 = @Desc1 AND Year = @Year AND Desc2 = @Desc2
END
GO