Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 issue with parameters in stored procedure

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)
as
begin
select
select * from table1
where 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 code

execute procedurename
@startrollno = '51', @endrollno = '100'


or

execute 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 23:47:56
you can ass multiple values like that
you 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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'
Go to Top of Page

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 questionthough
if you pass csv list how should condition be?

take every value and do between startvalue and @endrollno?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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)
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-12 : 13:14:38
yes
Go to Top of Page

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)
as
begin

select * from table1
where field1 = 'xyz' and field2 = '3'
and ((rollno <= @endrollnow)
or ',' + @startrollno + ',' like '%,' + cast(rollno as varchar(10)) + ',%'))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-12 : 16:54:06
Thanks visakh16
but when i execute as below, it still asks for @endrollno.
Here i expect the data shall display for roll no. 33, 57 and 65

execute 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.
Go to Top of Page

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)= null
as
begin

select * from table1
where 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 17:20:13
welcome.

there's a small modofication needed though


create procedure procedurename
@startrollno varchar(max),
@endrollno varchar(10)= null
as
begin

select * from table1
where 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-05-17 : 06:44:18
You may also need to read this http://www.sommarskog.se/arrays-in-sql-2005.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-05-17 : 16:25:48
Thanks Visakh16 and Madhivanan.
@Visakh
Actually 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 ON
set QUOTED_IDENTIFIER ON
go
alter procedure dbo.test
@srno as varchar(max)
as
begin
select * from table1
where ',' + @srno + ',' like '%,' + convert(varchar(10),srno) + ',%'
end
go

execute 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 01:11:18
try

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter procedure dbo.test
@srno as varchar(max)
as
begin
select * from table1
where ',' + @srno + ',' like '%,' + convert(varchar(10),RTRIM(srno)) + ',%'
end
go



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -