Author |
Topic |
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-11 : 16:59:24
|
I've created a stored procedure as below create procedure procedurename@startrollno varchar(10),@endrollno varchar(10)asbeginselect select * from table1where field1 = 'xyz' and field2 = '3' and ((rollno between @startrollno and @endrollnow)or rollno in @startrollno)) I'm trying to execute the above code using the below two codeexecute procedurename @startrollno = '51', @endrollno = '100' orexecute procedurename @startrollno in (51,53,55) The first execution works and it displays list of data between the two rollnos but the second execution code gets error (parameter @endrollno is missing) |
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-11 : 17:32:02
|
That is not valid syntax-Chad |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-11 : 23:47:56
|
you can ass multiple values like thatyou need to either change stored procedure to accept csv list or you need to execute sp in a loop for each of values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-12 : 02:13:16
|
@visakh16. again thanks.if possible, can you tell me how can I do it in a loop? or csv list?Otherwise, i will create two different stored procedures for them. One for 'between' and another one for 'in' |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-12 : 11:19:35
|
no need for two procedures. you can have same proc do both the queries.one questionthoughif you pass csv list how should condition be?take every value and do between startvalue and @endrollno?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-12 : 12:44:09
|
Thanks visakh.(1) want to find some random roll no details from the table so the condition will be like ('33', '57', '89', '95')(2) also may need to find details from the table for roll nos between start and rollnos (e.g. between 1 to 50) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-12 : 12:56:24
|
so is it like both conditions have to satisfies?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-12 : 13:14:38
|
yes |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-12 : 13:17:32
|
[code]create procedure procedurename@startrollno varchar(max),@endrollno varchar(10)asbeginselect * from table1where field1 = 'xyz' and field2 = '3' and ((rollno <= @endrollnow)or ',' + @startrollno + ',' like '%,' + cast(rollno as varchar(10)) + ',%'))[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-12 : 16:54:06
|
Thanks visakh16but when i execute as below, it still asks for @endrollno.Here i expect the data shall display for roll no. 33, 57 and 65execute procedurename@startrollno = '33,57,65' I expect when i use the code below it should display all data with roll no between 1 and 50.execute procedurename@startrollno = '1', @endrollno = '50' I would like to execute using any one of them. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-12 : 16:59:19
|
[code]create procedure procedurename@startrollno varchar(max),@endrollno varchar(10)= nullasbeginselect * from table1where field1 = 'xyz' and field2 = '3' and ((rollno between @startrollno AND @endrollnow )or ',' + @startrollno + ',' like '%,' + cast(rollno as varchar(10)) + ',%' and @endrollno is null)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-12 : 17:15:03
|
It works now as I want. Thank you very much Visakh. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-12 : 17:20:13
|
welcome.there's a small modofication needed thoughcreate procedure procedurename@startrollno varchar(max),@endrollno varchar(10)= nullasbeginselect * from table1where field1 = 'xyz' and field2 = '3' and ((rollno between @startrollno AND @endrollnow )or (',' + @startrollno + ',' like '%,' + cast(rollno as varchar(10)) + ',%' and @endrollno is null)) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-12 : 17:41:15
|
Yes I did the correction in my code to make it work..Anyway thanks..great |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-12 : 17:49:37
|
quote: Originally posted by learning_grsql Yes I did the correction in my code to make it work..Anyway thanks..great
you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-05-16 : 06:15:41
|
@visakh16,I have been using this procedure for a long time now and I just today I'm revising the code however I still can't make out what exactly the line does ( ',' + @startrollno + ',' like '%,' + cast(rollno as varchar(10)) + ',%') to make it work. Can you explain it to me? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 06:44:28
|
its basically trying to see whether it can find out pattern containing ,rollno, value within the passed string @startrollno. This will in effect filter the resultset to give you only those records with rollno value which is one among passed values in @startrollno parameter------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-05-17 : 16:25:48
|
Thanks Visakh16 and Madhivanan.@VisakhActually I have one more table with a column called "srno" with datatype nchar(10). When I use the same code, it shows blank result.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoalter procedure dbo.test@srno as varchar(max)asbeginselect * from table1where ',' + @srno + ',' like '%,' + convert(varchar(10),srno) + ',%'endgoexecute test @srno = '353,858' However, when I write the code "select * from table1 where srno in ('353,'858'), it produces many results which confirm there is data available in the table for those two values. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-20 : 01:11:18
|
tryset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoalter procedure dbo.test@srno as varchar(max)asbeginselect * from table1where ',' + @srno + ',' like '%,' + convert(varchar(10),RTRIM(srno)) + ',%'endgo ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|