passing \ as a parameter to a function

I have a UDF for splitting delimiter strings:
CREATE FUNCTION Split
(@.Source varchar (5000)
,@.Delimiter varchar (10) = ','
)
RETURNS @.T table (F1 varchar (100))
AS
--Accepts a source string @.Source and parses it to break it up into single
units
--delineated by @.Delimiter.
--Returns a Single Column Table with each row containing one of the split
chunks
--e.g. @.Source = 'SP,AQ,YD'
-- Returns @.T with three rows:
-- SP
-- AQ
-- YD
-- or @.Source = 'P1=V1, P2=V2'
-- Returns @.T with two rows:
-- P1=V1
-- P2=V2
BEGIN
DECLARE @.w varchar (5000)
DECLARE @.inte int
SET @.W = @.Source + @.Delimiter
WHILE len(@.W) > 0
BEGIN
SET @.inte = patindex('%,%',@.w) - 1
INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
END
RETURN
END
A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
the @.Reps table would have three records with one of the names in each.
e.g. Fred
Joe
Andy
It can also be called "inline":
SELECT s.* FROM tblSales s
INNER JOIN (SELECT * FROM split(@.Reps,',') r
ON s.Rep = r.F1
This all works fine until I try to call it using '\' as the delimiter
parameter, then I just get an error that says "Invalid length parameter
passed to the substring function"
Here is sample code to run this:
DECLARE @.NewPath varchar (100)
--Use this pair and it works
-- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
-- SELECT * FROM split(@.NewPath, ',')
--Use this pair and it fails
SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
SELECT * FROM split(@.NewPath, '\')
Sorry to be so long winded, but does anyone have any ideas?
Regards,
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"Hi
I have modified a little bit the function written by Dejan Sarka.
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000),@.delim VARCHAR(2))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(@.delim,@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(@.d
elim),8000)
END
RETURN
END
GO
--A typical use of this would be:
DECLARE @.Reps table (RepIn varchar (20))
declare @.RepSelect varchar(50)
set @.RepSelect='Fred\Joe\Andy'
INSERT @.Reps (RepIn) SELECT * FROM TsqlSplit(@.RepSelect,'')
select * from @.Reps
"RMarmion" <RMarmion@.Discussions.Microsoft.com> wrote in message
news:76AF1578-99B3-45FE-A24E-4D82B5435CF8@.microsoft.com...
>I have a UDF for splitting delimiter strings:
> CREATE FUNCTION Split
> (@.Source varchar (5000)
> ,@.Delimiter varchar (10) = ','
> )
> RETURNS @.T table (F1 varchar (100))
> AS
> --Accepts a source string @.Source and parses it to break it up into
> single
> units
> --delineated by @.Delimiter.
> --Returns a Single Column Table with each row containing one of the split
> chunks
> --e.g. @.Source = 'SP,AQ,YD'
> -- Returns @.T with three rows:
> -- SP
> -- AQ
> -- YD
> -- or @.Source = 'P1=V1, P2=V2'
> -- Returns @.T with two rows:
> -- P1=V1
> -- P2=V2
> BEGIN
> DECLARE @.w varchar (5000)
> DECLARE @.inte int
> SET @.W = @.Source + @.Delimiter
> WHILE len(@.W) > 0
> BEGIN
> SET @.inte = patindex('%,%',@.w) - 1
> INSERT @.T (F1) VALUES (substring(@.W, 1, @.inte))
> SET @.W = substring(@.W,@.inte+2,len(@.W)-(@.inte+1))
> END
> RETURN
> END
> A typical use of this would be:
> DECLARE @.Reps table (RepIn varchar (20))
> INSERT @.Reps (RepIn) SELECT * FROM Split(@.RepSelect,',')
> Assuming that a parameter @.RepSelect is passed, containing 'Fred,Joe,Andy,
> the @.Reps table would have three records with one of the names in each.
> e.g. Fred
> Joe
> Andy
> It can also be called "inline":
> SELECT s.* FROM tblSales s
> INNER JOIN (SELECT * FROM split(@.Reps,',') r
> ON s.Rep = r.F1
> This all works fine until I try to call it using '' as the delimiter
> parameter, then I just get an error that says "Invalid length parameter
> passed to the substring function"
> Here is sample code to run this:
> DECLARE @.NewPath varchar (100)
> --Use this pair and it works
> -- SET @.NewPath = 'c:,MSSQL,Data,MSSQL,DBFile.mdf'
> -- SELECT * FROM split(@.NewPath, ',')
> --Use this pair and it fails
> SET @.NewPath = 'c:\MSSQL\Data\MSSQL\DBFile.mdf'
> SELECT * FROM split(@.NewPath, '')
> Sorry to be so long winded, but does anyone have any ideas?
> Regards,
> -Rob
> --
> Robert Marmion
> ITBridges Inc
> 609 844 0949
> "Connecting your Business with your Software"|||Here lies the pain:
> SET @.inte = patindex('%,%',@.w) - 1
You're still looking for the comma. And, BTW, you could just as well use
CHARINDEX.
ML|||Duh!
Thank you both so much. What a stupid error!!
-Rob
--
Robert Marmion
ITBridges Inc
609 844 0949
"Connecting your Business with your Software"
"ML" wrote:

> Here lies the pain:
> You're still looking for the comma. And, BTW, you could just as well use
> CHARINDEX.
>
> ML

Pass back one value from UDF having found it in a table

How do I pass back only one value, such as TotalJobPrice from the tblJobRecords when I know which JobID to Select. All I want is the TotalJobPrice returned from the UDF, not a record from the table. I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)
Hope that is clear.
Thanks in advance,you should write your UDF as a scalar function.

Post your fnTotalJobPrice here.|||I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)I would like to write a function that would allow me to be able to get the TotalJobPrice out of the function. But I DO NOT KNOW HOW to get one field of data out of one record in a table using T-SQL. I am brand new at T-SQL and would like to know if there is a way to get one field of data out of one record so I can put that data into a local variable. I don't have fnTotalJobPrice to post. I want to learn how to do this one thing, then I feel I can write it. Thanks,|||declare @.i int
select @.i = myintcolumn from mytable where ...

Just make sure that the query returns only one row. if it returns multiple, which value you get is undefined.|||Thank you very much for your input on how to obtain a value from a table within a stored procedure or function. Here is my stored procedure, as I found out I did not need the function after all, but the same coding seems to hold true in both types of objects. I also noticed that I needed the SELECT in the SQL string. Any idea why? I thought is must be because I was not assigning the return of that SQL string to a local variable.ALTER PROC spCalculateTotalBillToDate
@.JobID as int
AS
BEGIN
DECLARE @.TotAmount AS Money
DECLARE @.BillOption AS varchar(3)
DECLARE @.BillCodeID AS int
DECLARE @.BillingStep as int
SET @.TotAmount = 0
SET @.BillCodeID = (SELECT Max(BillCodeID) FROM dbo.tblInvoice
WHERE JobID = @.JobID)
IF ISNULL(@.BillCodeID, ' ') = ' '
Begin
GOTO Return0
END
-- Bill Option
SET @.BillOption = (SELECT BillOption FROM dbo.tblBillOptions
WHERE BillOptionID =
(SELECT BillOptionID FROM dbo.tblProject
WHERE ProjectID =
(SELECT ProjectID FROM dbo.tblJob WHERE JobID = @.JobID)))
SET @.BillingStep = (SELECT max(BillingStep) FROM dbo.tblJobStatus
WHERE JobID = @.JobID AND JobStatusID <=
(SELECT JobStatusID FROM dbo.tblJob WHERE JobID = @.JobID))
--Calculate TotAmount
SET ANSI_WARNINGS OFF
SET @.TotAmount = (SELECT Sum(Amount) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob WHERE JobID = @.JobID)
AND BillCodeID <= @.BillCodeID)
SET ANSI_WARNINGS ON
IF @.BillingStep >= 89
BEGIN
--Print 'BillingStep is >= 89'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END
ELSE
IF @.BillOption = '_'
BEGIN
--Print 'BillOption = underscore'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID,
(SELECT Sum([Percent]) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob
WHERE JobID = @.JobID) AND BillCodeID <= @.BillCodeID))
END
ELSE
IF @.BillOption = 'D'
BEGIN
--Print 'BillOption = "D"'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END

Return0:
--print CAST(@.TotAmount AS VARCHAR)
SELECT @.TotAmount TotAmount
END|||not exactly sure what you are asking. There are several ways to return data from a proc. this article will educate you on all of them:

http://www.sommarskog.se/share_data.html|||What I was asking was about needing to use "SELECT" when obtaining the information from the table. The code you shared with me you did NOT use "SELECT", so I asked if this was different because I was using in within some other SQL, rather than just assigning the data to a local variable. If you notice within the stored procedure I posted, I was able to return what I wanted to the caller.

My original question was not how to return data FROM a function, but how to get data from a table to be used IN a function.

Again, thanks for your help.|||hmm.

in the code I posted for you, i *did* use "select" to get data from a table. I didn't type it in all caps though. sql keywords are not case sensitive. I don't know of any way to get data out of a table other than select...

in any case it sounds like you got the answer you were looking for. :)