March 10, 2009 at 3:52 pm
CREATE PROCEDURE [dbo].[ABCCDE]
(@inputparam varchar(3))
AS
SELECT DISTINCT
desc as depdesc,
Table1.col1,
OPPSDT,
Table1.col2,
Table2.col3,
Table3.col4,
Table1.col5,
Table3.col6,
Table3.col7,
Table2.col8,
Table3.col9,
Table3.col10,
Table3.col11,
Table3.col12,
Table2.col13,
Table3.col14,
Table1.col15,
Table1.col16,
Table1.col17,
Table2.col18,
Table1.col19
FROM
(Table3
INNER JOIN Table2 ON
Table3.col6 = Table2.col6)
INNER JOIN Table1 ON
(Table2.col3 = Table1.col3)
AND (Table2.col20 = Table1.col20)
AND (Table2.col6 = Table1.col6)
INNER JOIN Table4 ON
Table4.dept = Table1.col5
WHERE
(
(Table3.col14)<>0)
AND
((Table1.col17) Not In
(select col17 from Table10)) AND ((Table2.col18)='0')
AND
Table3.col11 IN('A','B','C','D','E','F')
ORDER BY Table1.col5, col10, Table3.col9, col19,
Table1.col15;
GO
when I try running the select statement it gives me proper results.
For example.If in the IN operator I use only A or B or C or D or E then it gives me rows which satisfy that condition.(rows with only A or B or C or D or E or F in the col11 of Table3.
But when I try creating the stored procedure and pass the parameter(A or B or C or D or E or F) from sql server reporting services/visual studio after creating the report it does not give me the appropriate rows . It always returns all rows irrespective of what the condition is in the IN operator.
Thanks
March 10, 2009 at 4:39 pm
The code you show here isn't using the parameter at all. Am I correct in thinking that you want to use the input parameter to see if Table3.col1 = 'A' or 'B' or 'C' or 'D'? If that's what you want, replace the IN with
Table3.col11 = @inputparam
Greg
March 10, 2009 at 4:59 pm
Yes , thats exactly what I am looking for . I need to search for values which I will pass as a parameter in a list of values. I tried your suggestion , it says incorrect syntax
Thanks
March 10, 2009 at 6:05 pm
Hello,
I just want to make things clearer:
You want to pass A, B, C or D in your parameter and after it to use in your IN clause?
If it is YES, you can not do it on that way without Dynamic SQL, that means creating SQL string with concatenation and after that executing it at the end of your stored procedure.
You should do something like this:
DECLARE @sqlSTM nvarchar(4000)
SET @sqlStm = 'SELECT DISTINCT
desc as depdesc,
Table1.col1,
OPPSDT,
Table1.col2,
Table2.col3,
Table3.col4,
Table1.col5,
Table3.col6,
Table3.col7,
Table2.col8,
Table3.col9,
Table3.col10,
Table3.col11,
Table3.col12,
Table2.col13,
Table3.col14,
Table1.col15,
Table1.col16,
Table1.col17,
Table2.col18,
Table1.col19
FROM
(Table3
INNER JOIN Table2 ON
Table3.col6 = Table2.col6)
INNER JOIN Table1 ON
(Table2.col3 = Table1.col3)
AND (Table2.col20 = Table1.col20)
AND (Table2.col6 = Table1.col6)
INNER JOIN Table4 ON
Table4.dept = Table1.col5
WHERE
(
(Table3.col14)<>0)
AND
((Table1.col17) Not In
(select col17 from Table10)) AND ((Table2.col18)='0')
AND
Table3.col11 IN(' + @inputparam +')
ORDER BY Table1.col5, col10, Table3.col9, col19,
Table1.col15'
exec @sqlStm
But remember to make your parameter larger, and pass this format to it: 'A','B','C','D','E','F'
Only in that case your dynamic SQL will work correctly
If I'm wrong please explain your problem more specificly.
Regards,
latek
March 10, 2009 at 6:36 pm
Here is an alternative to dynamic SQL. The code at the bottom will quickly parse a delimited list of values out of an input parameter for you. Let me know if you have any questions about the parsing code.
You can reference the CTE "ParsedList" like a table, like so:
AND
Table3.col11 IN(select * from ParsedList)
declare @inputString varchar(7900)
declare @sepChar varchar(50)
declare @element varchar(4)
declare @input varchar(8000)
set @inputString = '1/2/3/a/b/c/delta/bravo/#/@'
set @sepchar = '/'
set @element = 7
set @input = @sepChar+@inputString+@sepChar
select @input
;WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),--2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows "L0 as A, L0 as B" is just shorthand way to code a cross join
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
Tally AS (SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4),
ParsedList AS
(select Row_Number() over (order by N) as E,substring(@input,N+1,charindex(@sepChar,@input,N+1)-(N+1)) as element
from tally
where substring(@input,N,1) = @sepChar
and N < len(@input)
)
select * from ParsedList
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 10, 2009 at 7:46 pm
I have a list of distinct values in Table3.col11 which are part of the IN operator list
IN('A','B','C'....)
From sql server reporting services I will be passing one of values in the IN(...) parameter list and that would retrieve the resulting report.This is exactly what I am trying to do.
Thanks
March 10, 2009 at 8:36 pm
Then why are you using IN? All you need is equals.
AND
Table3.col11 = @inputParam
At this point it would be very nice to have some data. Also, please add a statement to you stored proc that says
SELECT @inputParam as [@inputParam]
to be sure that we are getting the value we expect.
I notice that your character lengths are all one, but your input parameter is length 3. If you are wrapping the value in single quotes before putting it into the input parameter, stop. If the SELECT statement above gives us output like the example below, we've found your problem.
@inputParam
'A'
To work correctly it should display
@inputParam
A
You only have to put single quotes around values to designate CONSTANTS. They are not necessary within variables, just as they are not necessary within columns.
DECLARE @inputParam varchar(3)
SET @inputParam = 'A'
SELECT @inputParam as [@inputParam]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 10, 2009 at 10:13 pm
Thanks for the response but I am kind of confused now .
Can you edit this procedure and tell me exactly what you mean.
CREATE PROCEDURE [dbo].[ABCCDE]
(@inputparam varchar(3))
AS
SELECT DISTINCT
desc as depdesc,
Table1.col1,
OPPSDT,
Table1.col2,
Table2.col3,
Table3.col4,
Table1.col5,
Table3.col6,
Table3.col7,
Table2.col8,
Table3.col9,
Table3.col10,
Table3.col11,
Table3.col12,
Table2.col13,
Table3.col14,
Table1.col15,
Table1.col16,
Table1.col17,
Table2.col18,
Table1.col19
FROM
(Table3
INNER JOIN Table2 ON
Table3.col6 = Table2.col6)
INNER JOIN Table1 ON
(Table2.col3 = Table1.col3)
AND (Table2.col20 = Table1.col20)
AND (Table2.col6 = Table1.col6)
INNER JOIN Table4 ON
Table4.dept = Table1.col5
WHERE
(
(Table3.col14)<>0)
AND
((Table1.col17) Not In
(select col17 from Table10)) AND ((Table2.col18)='0')
AND
Table3.col11 IN('ABC','BC','CFRT','DY','EM','FOP')
ORDER BY Table1.col5, col10, Table3.col9, col19,
Table1.col15;
GO
The IN operator list available is a constant i.e I will be searching only for those values mentioned in the list by passing them as parameters from sql server reporting services . And I will be passing only 1 parameter.
Ex:search for data where Table3.col11 could have a value of ABC then a report is generated based on the written sql statement . Hope I made myself clear.
Thanks
March 11, 2009 at 2:39 am
as I understand the second post from Greg Charles was enough to you.
You are passing one parameter that contains only one value ('A' or 'B' or 'ABC' or whatever). To make clear: you don't pass series of values.
In that case you should replace IN operator with = , because table3.col1 IN ('A') is equal to table3.col1 = 'A', and everything will be ok, or just to repost your whole corrected Stored Procedure:
CREATE PROCEDURE [dbo].[ABCCDE] ( @inputparam varchar(3) )
AS
SELECT DISTINCT
[desc] as depdesc,
Table1.col1,
OPPSDT,
Table1.col2,
Table2.col3,
Table3.col4,
Table1.col5,
Table3.col6,
Table3.col7,
Table2.col8,
Table3.col9,
Table3.col10,
Table3.col11,
Table3.col12,
Table2.col13,
Table3.col14,
Table1.col15,
Table1.col16,
Table1.col17,
Table2.col18,
Table1.col19
FROM ( Table3
INNER JOIN Table2 ON Table3.col6 = Table2.col6)
INNER JOIN Table1 ON ( Table2.col3 = Table1.col3 )
AND ( Table2.col20 = Table1.col20 )
AND ( Table2.col6 = Table1.col6 )
INNER JOIN Table4 ON Table4.dept = Table1.col5
WHERE ( ( Table3.col14 ) <> 0 )
AND ( ( Table1.col17 ) Not In ( select col17
from Table10 ) )
AND ( ( Table2.col18 ) = '0' )
AND Table3.col11 = @inputparam
ORDER BY Table1.col5,
col10,
Table3.col9,
col19,
Table1.col15 ;
GO
March 11, 2009 at 7:51 am
Metro, you need to put forth a little more effort, please. Work with us. Did you even TRY to do what I asked you to do? There is no point in posting and reposting the same block of code. We all saw it the first time. 😉
I'm going to make it easy for you.
1. Add the line of code.
2. Run the procedure by entering
EXEC dbo.ABBCCDE 'A'
3. Post the results back here.
4. Run the procedure by entering
EXEC dbo.ABBCCDE ''A''
5. Post the results back here.
After you tell me the results, I will explain it in more detail for you.
CREATE PROCEDURE [dbo].[ABCCDE]
(@inputparam varchar(3))
AS
SELECT @inputParam as [@inputParam] --- ADD THIS LINE TO YOUR PROCEDURE
SELECT DISTINCT
.
.
.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 11, 2009 at 11:23 am
Appreciate your response.
I cant replace IN with = since I am searching for a value in a list of values.
The exact scenario is I search for a value from sql server reporting services .
Ex: ABC
The IN list has ('ABC','DEF','DD','DFGH')
So it should return the rows which have ABC in Table3.col11.
Thanks
March 11, 2009 at 1:40 pm
I will be passing one
Make up your mind, Metro.
First I give you the parser code. Then you said one value.
I said use equal Then you say your're passing a list of values.
I notice one of your strings has 'DD' in it. Why repeat a code?
So, I'm assuming, you want to pass up to three letters and find any of those codes. You could have saved us all a lot of time by making each of those letters a separate input parameter.
I'll give you a simple alternative to the parser.
WHERE
.
.
.
AND
Table3.col11 = substring(@inputParam,1,1) OR
Table3.col11 = substring(@inputParam,2,1) OR
Table3.col11 = substring(@inputParam,3,1)
The morale of this story is read BOL and understand the IN function, or just use OR.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 11, 2009 at 1:41 pm
I will be passing one
Make up your mind, Metro.
First I give you the parser code. Then you said one value.
I said use equal Then you say your're passing a list of values.
I notice one of your strings has 'DD' in it. Why repeat a code?
So, I'm assuming, you want to pass up to three letters and find any of those codes. You could have saved us all some time by just making each of those letters a separate input parameter. I'll give you a simple alternative to the parser.
SET @inputParam = left(@inputParam+'~~~',3) -- ensures you always have three characters in there
SELECT
.
.
.
WHERE
.
.
.
AND
Table3.col11 = substring(@inputParameter,1,1) OR
Table3.col11 = substring(@inputParameter,2,1) OR
Table3.col11 = substring(@inputParameter,3,1)
and if absolutely MUST use IN....
AND
Table3.col11 IN (SELECT substring(@inputParameter,1,1) UNION ALL
substring(@inputParameter,2,1) UNION ALL
substring(@inputParameter,3,1)
)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply