Click here to Skip to main content
14,332,414 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 29-Mar-14 4:59am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
Comments
sankarsan parida 29-Mar-14 13:14pm
   
Good article with nice ans
Maciej Los 29-Mar-14 16:14pm
   
Self-reference, i like it!
+5!
Rate this:
Please Sign up or sign in to vote.

Solution 2

A Google search can give you many results depending on your requirement.
One, Two
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

Have a look at similar question and answers: how to get the character from string in SQL ?[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

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'); 
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100