SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 issue with parameters in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/11/2012 :  16:59:24  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 09/11/2012 :  17:32:02  Show Profile  Visit chadmat's Homepage  Reply with Quote
That is not valid syntax

-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/11/2012 :  23:47:56  Show Profile  Reply with Quote
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 - 09/12/2012 :  02:13:16  Show Profile  Reply with Quote
@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

India
52317 Posts

Posted - 09/12/2012 :  11:19:35  Show Profile  Reply with Quote
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 - 09/12/2012 :  12:44:09  Show Profile  Reply with Quote
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)

Edited by - learning_grsql on 09/12/2012 12:46:28
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2012 :  12:56:24  Show Profile  Reply with Quote
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 - 09/12/2012 :  13:14:38  Show Profile  Reply with Quote
yes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2012 :  13:17:32  Show Profile  Reply with Quote

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)) + ',%'))


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

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/12/2012 :  16:54:06  Show Profile  Reply with Quote
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.

Edited by - learning_grsql on 09/12/2012 16:56:41
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2012 :  16:59:19  Show Profile  Reply with Quote

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 - 09/12/2012 :  17:15:03  Show Profile  Reply with Quote
It works now as I want. Thank you very much Visakh.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2012 :  17:20:13  Show Profile  Reply with Quote
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 - 09/12/2012 :  17:41:15  Show Profile  Reply with Quote
Yes I did the correction in my code to make it work..Anyway thanks..great

Edited by - learning_grsql on 09/12/2012 17:42:41
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2012 :  17:49:37  Show Profile  Reply with Quote
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 - 05/16/2013 :  06:15:41  Show Profile  Reply with Quote
@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

India
52317 Posts

Posted - 05/16/2013 :  06:44:28  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 05/17/2013 :  06:44:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 05/17/2013 :  16:25:48  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/20/2013 :  01:11:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000