parameter with the In function
-
Example:
select * from tbl_Name where id in(1,2,3,4)
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
thank you.. i know this :) my question was how to do that with a parameter
When you get mad...THINK twice that the only advice Tamimi - Code
-
thank you.. i know this :) my question was how to do that with a parameter
When you get mad...THINK twice that the only advice Tamimi - Code
Then you have to generate that numbers from select query.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
-
hi can we user parameters with the In function ?? example: select * from tbl_Name where id in(par) // par is a parameter pass to a stored procedure i want to do this because i dont know how many ids i will get. thank you
When you get mad...THINK twice that the only advice Tamimi - Code
I guess your parameter might be comprising of a delimited string, say, "1,2,4,8" or even "{Guid1},{Guid2},...". If such is your requirement, you can always use some little tweaks. Like Declare a temporary table to store the Ids, like declare @Ids table(Id int) Then, use some string functions to split the string and insert the ids into the table. (Like, CHARINDEX, SUBSTRING, etc) After that, you can then use the temporary table on your WHERE ... IN ... clause Like select * from tbl_Name where id in (select Id from @Ids)
-
Then you have to generate that numbers from select query.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
mmmmmm suppose you have a check box list that contains 50 items(id) and the user can select as many as he wish from the list.. then how you can pass the selected ids to a stored procedure ?? giving that you don't know how many ids you will pass?? got me??
When you get mad...THINK twice that the only advice Tamimi - Code
-
I guess your parameter might be comprising of a delimited string, say, "1,2,4,8" or even "{Guid1},{Guid2},...". If such is your requirement, you can always use some little tweaks. Like Declare a temporary table to store the Ids, like declare @Ids table(Id int) Then, use some string functions to split the string and insert the ids into the table. (Like, CHARINDEX, SUBSTRING, etc) After that, you can then use the temporary table on your WHERE ... IN ... clause Like select * from tbl_Name where id in (select Id from @Ids)
thank you this is nice. i will give it a try. or simply :-D string strIds = "1,2,3,4"; SqlCommand com="select * from tbl_name where id in(" + strIds + ")";
When you get mad...THINK twice that the only advice Tamimi - Code
-
hi can we user parameters with the In function ?? example: select * from tbl_Name where id in(par) // par is a parameter pass to a stored procedure i want to do this because i dont know how many ids i will get. thank you
When you get mad...THINK twice that the only advice Tamimi - Code
Use Table valued parameter[^] and try google for more example.
-
Use Table valued parameter[^] and try google for more example.
this is new to me :) thank you... will read about it
When you get mad...THINK twice that the only advice Tamimi - Code
-
I guess your parameter might be comprising of a delimited string, say, "1,2,4,8" or even "{Guid1},{Guid2},...". If such is your requirement, you can always use some little tweaks. Like Declare a temporary table to store the Ids, like declare @Ids table(Id int) Then, use some string functions to split the string and insert the ids into the table. (Like, CHARINDEX, SUBSTRING, etc) After that, you can then use the temporary table on your WHERE ... IN ... clause Like select * from tbl_Name where id in (select Id from @Ids)
Note: I'm not sure about MS-SQL, but in Oracle there is a limit to the number of values you can have in an "IN" clause. Back in Oracle 9, I believe the max was 1000. for example: select * from myTable where ID IN (1,2,3,....,1000,1001) would fail because there was more than 1000 values listed. I'm not sure if this is still the case, but you might want to consider it in your design. Good luck. :thumbsup:
-
mmmmmm suppose you have a check box list that contains 50 items(id) and the user can select as many as he wish from the list.. then how you can pass the selected ids to a stored procedure ?? giving that you don't know how many ids you will pass?? got me??
When you get mad...THINK twice that the only advice Tamimi - Code
Tamimi - Code wrote:
then how you can pass the selected ids to a stored procedure ??
Presuming that you really did mean stored procedure then... First step, determine based on to create the stored procedure in the language supported by the database. Options that I have used for variable argument lists. - Arrays - Varchar with values as a comma separated values in that list. - Proc with up to X args and of which can be null. - Several procs each one with an increasing number of parameters: first has 5, second has 10, etc. - dynamic SQL, run in a proc (only suitable for situations where input is known to be secure.) Once you do in fact have a proc then you write code which populates the parameters dependent on the type of proc that actually exists. Conversely without a proc, and just using SQL, one creates the SQL from scratch using code (for loops, string concatenation, etc) with the appropriate number of bind variables. Then one populates the bind variables. Then you run it. To my mind the last option is easier than any solution with procs.
-
Note: I'm not sure about MS-SQL, but in Oracle there is a limit to the number of values you can have in an "IN" clause. Back in Oracle 9, I believe the max was 1000. for example: select * from myTable where ID IN (1,2,3,....,1000,1001) would fail because there was more than 1000 values listed. I'm not sure if this is still the case, but you might want to consider it in your design. Good luck. :thumbsup: