Find a Integer from a nvarchar (string) fild ::
In this procedure firstly i create a table ( containing Int value) , from a comma delimited string(nvarchar).
Here you can use like function but there is a problem , suppose according to bellow procedure you are find
"id" whose value is "56". and the string(nvarchar) value is "2,45,212,156,178".
here if you use " like " operator, following select statement reture true as because of 156 in that string(nvarchar) .
For avoiding this problem, i solved that issue by the following SP & Function :
ALTER procedure [dbo].[AbhisHiFi] --'amrita_agp'
(@uNAME nvarchar(20))
as
begin
-- for get the string
declare @a nvarchar(max)=(select menu_list1 from menu_permit where UserName=@uNAME)
select * from menu where status=1 and id not in (465) and Url is null
union
-- find "id" within that string(nvarchar) ,here that nvarchar was modified to a table by
-- a user define function
select * from menu where status=1 and id in (SELECT ID FROM [dbo].[Abhi_StringToIntList](@a,',')) order by pos
end
AND The Function is as follows :
ALTER FUNCTION [dbo].[Abhi_StringToIntList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS
@result TABLE (
[ID] INT NULL)
AS
BEGIN
-- here the function is return a table , and the table is created through XML
DECLARE @x XML
SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'
INSERT INTO @result
SELECT DISTINCT x.i.value('.', 'int') AS token
FROM @x.nodes('//t') x(i)
ORDER BY 1
RETURN
END

0 comments:
Post a Comment