sql help sqlserver 2000 - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

sql help sqlserver 2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-09-22 : 14:52:33

Please help me in writing the stored procedure.

I am developing a front end VB.net application which has a check box values.

SR1,
SR2,
SR3,
SR4,
SR5
Whenever a user selects the check box it should retrieve the data based on the check box selection.

I have the sample data like below

Code Sid
---- ----
SR1 5101
SR1 5102
SR2 5101
SR2 5102
SR2 5113
SR2 5120
SR3 5101
SR3 5102
SR3 5108
SR3 5124
SR4 5102
SR5 5102
SR5 5106

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 15:01:01

[code]
if object_id('dbo.mySP') > 0
drop proc dbo.mySP
go

create proc dbo.mySP
@code varchar(3)
as
begin
select Code
,Sid
from myTable
where code = @code
end

go

--EDIT
Grant exec on dbo.mySP to <myApplicationAccount>
go

--Sample Call
exec dbo.mySP @code = 'SR1'
[/code]

Be One with the Optimizer
TG

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-09-22 : 15:07:30

Thanks TG!
But if a user selects multiple check box values then how to do..

thanks for your help in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 15:30:02

One way is to build a delimited list in your application based on the user selections. Use a parsing function in your SP to populate a table variable that holds the individual codes. There are lots of parsing routines on this site. check out the "sticky" topic in "new to sql server forum. Here is a simple example of a parsing routine written out within the SP (rather than a generaic UDF):

EDIT:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&searchterms=csv
also look through the topic "best split functions"


if object_id('dbo.mySP') > 0
	drop proc dbo.mySP
go

create proc dbo.mySP
	@codes varchar(8000)
as
begin

	------------------------------------------------------------------------------------
	--parse the codes into a table variable
	declare @ci int
	declare @codeTb table (code varchar(3))
	select	@codes = ',' + @codes + ','
		,@ci = 1
	
	while charindex(',', @codes, @ci+1) > 0 and charindex(',', @codes, @ci) < len(@codes)
	begin
		insert @codeTb (code)
		values (substring(@codes, @ci+1, charindex(',', @codes, @ci+1)-@ci-1))
	
		set @ci = charindex(',', @codes, @ci+1)
	end
	------------------------------------------------------------------------------------

	select	Code
		,Sid
	from	@codeTb a
	join	myTable b
			on b.code = a.code
end

go
Grant exec on dbo.mySP to <myApplicationAccount>
go

--Sample Call
exec dbo.mySP @codes = 'SR1,SR2,SR3'

Be One with the Optimizer
TG

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-09-22 : 15:57:54

Thanks TG once again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-23 : 11:52:56

or one of these

Exec('Select columns from table
Where code in('+@codes+')')

Select columns from table
where '%,'+@codes+'%,' like '%,'+cast(code as varchar(20))+',%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources