Split in stored procedure in sql server 2008

Question

0.00/5 (No votes)

See more:

, +

Hi guys,

i was working on stored procedures in sql server,

where i have a variable with datatyep nvarchar(max),.

n i want to split it, the variable is already having ',' commas.

my code:

   alter proc sp_GetSeletedTrucks
@tid nvarchar(max)
as 

select * from TruckTracking where DoorNo in (@tid)

where @tid = '101,102,103'

plzzzz... suggest me.

thanks

Posted 29-Mar-14 4:57am

abdul subhan mohammed

Updated 29-Mar-14 4:59am

v2

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

abdul subhan mohammed · Answer 1 · 2014-04-02T07:24:00

My Solution:

USE [NPS]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetSeletedTrucks]    Script Date: 04/02/2014 20:23:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_GetSeletedTrucks]
@xmldoc xml

as 

-- sp_GetSeletedTrucks '<root fromdate="03/29/2014" todate="03/29/2014" tid="600-0000,601-0000,602-0000,603-0000,604-0000,605-0000,606-0000,607-0000,608-0000,609-0000,610-0000,611-0000,612-0000" />'

 Declare @FromDate date, @ToDate date, @tid nvarchar(max)
  
  SELECT 
		@FromDate = Col.value('@FromDate', 'date'),
		@ToDate = Col.value('@ToDate', 'date'),
		@tid = Col.value('@tid', 'nvarchar(MAX)')
  FROM @xmldoc.nodes('/Root') Tbl(Col);

declare @dooroptions table
(dno varchar(max))

declare @text nvarchar(max)
set @text = REPLACE(@tid, '''','')

insert into @dooroptions 
select data from dbo.Split(@text, ',')

select 
	distinct DoorNo as '@label'
	,1 as '@value' 
	from TruckTracking v
where 
	DoorNo in (select dno from @dooroptions) and 
	(TrackDate between @FromDate and @ToDate)
 for xml path('category') , Root('categories');

Krunal Rohit · Answer 2 · 2014-03-29T08:05:00

Solution 4

http://www.webtechminer.com/split-function-in-sql-server-to-break-comma-separated-string/[^]
http://ondra.zizka.cz/stranky/programovani/sql/mysql_stored_procedures.texy[^]
http://yugalpandya.wordpress.com/2013/10/10/split-comma-separated-values-in-ms-sql/[^]

-KR

Posted 29-Mar-14 8:05am

Krunal Rohit

Maciej Los · Answer 3 · 2014-03-29T05:23:00

Solution 3

Have a look at similar question and answers: how to get the character from string in SQL ?[^]

Posted 29-Mar-14 5:23am

Maciej Los

girishmeena · Answer 4 · 2014-03-29T05:11:00

Solution 2

A Google search can give you many results depending on your requirement.
One, Two

Posted 29-Mar-14 5:11am

girishmeena

OriginalGriff · Answer 5 · 2014-03-29T05:00:00

Solution 1

See here: Using comma separated value parameter strings in SQL IN clauses[^]

Posted 29-Mar-14 5:00am

OriginalGriff

Comments

[no name] 29-Mar-14 13:14pm

Good article with nice ans

Maciej Los 29-Mar-14 16:14pm

Self-reference, i like it!
+5!