-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: Sam786786
Message 5 in Discussion
Hi Manoj
Use this function for returning rows based on the
provided string
example this statment select * from
dbo.fn_split('1,2,3,4,5',',') will return rows
1
2
3
4
5
result can be used in IN clause of SQL
query
create
FUNCTION dbo.FN_SPLIT (
@src_str AS
VARCHAR(5000),
@row_delim AS
VARCHAR(100)
)
RETURNS @TEMP1 TABLE
(
COL_1
VARCHAR(2000)
)
BEGIN
DECLARE @temp_str AS
VARCHAR(5000)
DECLARE @start_pos AS INT --Start
position
DECLARE @end_pos AS INT --End position
DECLARE
@row_delim_len AS INT
-- DECLARE @col_delim_len AS
INT
DECLARE @len AS INT
SELECT @end_pos =
CHARINDEX(@row_delim, @src_str)
SELECT @start_pos = 1
SELECT
@row_delim_len = LEN(@row_delim)
WHILE @end_pos >
0
BEGIN
SELECT @len = @end_pos - @start_pos
IF @len
> 0
SELECT @temp_str = SUBSTRING ( @src_str , @start_pos
,@len )
SELECT @start_pos = @end_pos + @row_delim_len
SELECT @end_pos = CHARINDEX(@row_delim, @src_str,@start_pos)
INSERT INTO @TEMP1 SELECT ISNULL(@temp_str,'')
END
SELECT
@len = LEN(@src_str) - @start_pos + @row_delim_len
IF @len >
0
BEGIN
SELECT @temp_str = SUBSTRING ( @src_str ,
@start_pos ,@len )
INSERT INTO @TEMP1 SELECT
ISNULL(@temp_str,'')
END
RETURN
END
Hope it will help
Mohammad
----- Original Message -----
From:
BDOTNET
To: [email protected]
Sent: Thursday, November 10, 2005 3:33
PM
Subject: Urgent! Help on SQLQuery
New Message on BDOTNET
Urgent!
Help on SQLQuery
Reply
Recommend
Message 1 in
Discussion
From: manojpro
Hi guys,
I have written one UDF (function) in sql , which will take
2 parameters one is integer and other one is string of some ids
with coma separated.
I want to use the second parameter as parameter for IN
Clause of sql subquery.
but it's not giving me correct result, as i executed
the function its taking only first id from that given coma
separated string.
Is there any functionality is there or we cann't pass
string variable.
here is that UDF...
input will be @auditId = '3,4'
@nodeId =
89
when i execute this it will return me
only values of 3,
if i reverse the order of auditId , it
will return me value for 4..
alter function
dbo.GetSumKRIOptionMarks(@auditId
varchar,@nodeId int) returns int
as
Begin
declare @OptionMarks
int
select @OptionMarks = sum(case when
Option_Marks is null then 0 end) from
AS_RiskTemplate_QueryDetails
Where [EMAIL PROTECTED] And Option_id in (select Option_id from
AS_RiskTemplate_Response Where
Audit_id in (
@auditId) and Node_id =
@nodeId )
return @OptionMarks
End
--
Regards,
Manoj
Kulkarni
9448644188
View other groups in this
category.
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
