January 16, 2011 at 2:38 pm
I pass a string to my stored procedure which is similar to this:
'test1,test2,test3,test4'
SQL Server will not recognise the string like this since each word is not enclosed in quotes. The string needs to appear like this:
'test1','test2','test3','test4'
How do I format the string like this? I will be calling a separate UDF to take care of this but I'm not sure of what I need to do to return the correctly formatted string.
Thanks in advance
January 16, 2011 at 2:46 pm
Really could use more context regarding what you are trying to accomplish. Can you provide the code for your stored procedure and UDF?
Please remember, we can't see from here what you see from there.
January 16, 2011 at 3:00 pm
My function is below:
USE [SAS_LIVE]
GO
/****** Object: UserDefinedFunction [dbo].[fn_SplitString] Script Date: 01/17/2011 08:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Partridge, Mike>
-- Create date: <14th January 2011>
-- Description:<Split string>
-- =============================================
ALTER FUNCTION [dbo].[fn_SplitString]
(
@CSVString NVARCHAR(MAX),
@DelimiterNVARCHAR(1)
)
RETURNS @tbl TABLE (Result NVARCHAR(MAX))
AS
BEGIN
DECLARE
@i INT,
@j-2 INT
SELECT @i = 1
WHILE @i <= LEN(@CSVString)
BEGIN
SELECT@j-2 = CHARINDEX(@Delimiter, @CSVString, @i)
IF @j-2 = 0
BEGIN
SELECT @j-2 = LEN(@CSVString) + 1
END
INSERT @tbl SELECT SUBSTRING(@CSVString, @i, @j-2 - @i)
SELECT@i = @j-2 + LEN(@Delimiter)
END
UPDATE @tbl
SET Result = '''' + Result + '''' + ','
RETURN
END
Here's the line that is run from my stored procedure:
SELECT * FROM fn_SplitString(@Fund,',')
The variable @Fund contains the following data:
'Annual Giving 2008, Annual Giving 2009, Annual Giving 2010'
The results the function returns are in a table format. Is it possible to return these as a single string?
January 16, 2011 at 3:26 pm
Not exactly sure what you mean by a single string, but the following code works for me and returns what I would expect based on what you provided. It also will also scale better than your function as long as the input string is <= 8000 bytes (4000 nvarchar, and 8000 varchar).
/****** Object: UserDefinedFunction [dbo].[DelimitedSplit] Script Date: 01/16/2011 15:19:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[NDelimitedSplit] (
@pString nvarchar(max),
@pDelimiter nchar(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
--a4 as (select
-- 1 as N
-- from
-- a3 as a
-- cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by a.N) as N
from
a3 as a
cross join a2 as b),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit
GO
declare @TestStr varchar(8000);
set @TestStr = 'Annual Giving 2008,Annual Giving 2009,Annual Giving 2010';
select Item from dbo.NDelimitedSplit(@TestStr,',');
January 16, 2011 at 3:37 pm
Thanks for that. When I said single string, I meant that I wanted the results to be returned as one line (or one row), for example:
Annual Giving 2008, Annual Giving 2009, Annual Giving 2010
In other words, not as a table. Can I use COALESCE to combine all these table rows into a single row?
January 16, 2011 at 3:46 pm
Then what are you using the DelimtedString function for if you don't want to split the original input string?
January 16, 2011 at 3:54 pm
Looks like I might be on the wrong track, my apologies for the confusion.
Here's a bit of background. I have a SSRS report running that has the ability for users to select multiple values. When SSRS passes this parameter to my SQL sp, it passes a single string that is enclosed in quotes (as oposed to enclosing each value in this string in quotes, its a shortcoming of SSRS). Hence the following string:
'Annual Giving 2008, Annual Giving 2009, Annual Giving 2010'
This value is used in the following statement in my sp:
SELECT * FROM #Fundraising
WHERE Fund IN (@Fund)
SQL will not return any results since @Fund does not have quotes around every value. In order for this to work correctly, @Fund needs to have the following value:
'Annual Giving 2008','Annual Giving 2009','Annual Giving 2010'
I hope that makes sense, let me know if I haven't clarified something. I'm looking for a way to get from point a to b, i.e., enclosing each of the values in quotes as ooposed to just having a pair of quotes around the whole string.
January 16, 2011 at 3:59 pm
Now we are getting somewhere. Please post the stored proc with the query.
January 16, 2011 at 4:02 pm
ALTER PROCEDURE [dbo].[sp_AllFundraisingPayments]
@Fund NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Fundraising(
AccountNo INT,
ContactID INT,
AccountTo NVARCHAR(MAX),
FundraisingGreeting NVARCHAR(MAX),
FirstName NVARCHAR(MAX),
PreferredName NVARCHAR(MAX),
LastName NVARCHAR(MAX),
MailingAddressID INT,
Address1 NVARCHAR(MAX),
Address2 NVARCHAR(MAX),
Address3 NVARCHAR(MAX),
Postcode NVARCHAR(MAX),
Country NVARCHAR(MAX),
FundInvoiceID INT,
DueDate DATETIME,
FundId INT,
FundCode NVARCHAR(MAX),
Fund NVARCHAR(MAX),
SubfundID INT,
SubFundCode NVARCHAR(MAX),
Subfund NVARCHAR(MAX),
Amount INT,
PaidDate DATETIME)
INSERT INTO #Fundraising
(AccountNo, ContactID,AccountTo,FirstName,PreferredName,LastName,MailingAddressID,FundinvoiceID,DueDate,FundId,FundCode,Fund,SubfundID,SubFundCode,Subfund,Amount)
SELECT
fundinvoice.accountid,
contact.ID,
contact.AccountTo,
contact.FirstName,
contact.PreferredName,
contact.LastName,
contact.MailAddrID,
fundinvoice.ID,
fundinvoice.duedate,
FundInvoice.FundID,
fund.code,
fund.Description,
FundInvoice.SubFundID,
subfund.Code,
subfund.Description,
fundinvoice.Amount
FROM fundinvoice
LEFT JOIN subfund ON fundinvoice.subfundid = subfund.id, contact, fund
WHERE (fundinvoice.contactid = contact.id ) and ( fundinvoice.fundid = fund.id )
ORDER BY fundinvoice.id ASC
UPDATE F
SET F.FundraisingGreeting = AD.FieldValue
FROM #Fundraising F
LEFT JOIN (SELECT * FROM AdhocData WHERE AdhocID = 31) AD ON F.ContactID = AD.SourceID
UPDATE F
SET F.Address1 = A.Address1,
F.Address2 = A.Address2,
F.Address3 = A.Address3,
F.Postcode = A.PostCode,
F.Country = A.Country
FROM #Fundraising F
LEFT JOIN Address A ON F.MailingAddressID = A.ID
UPDATE F
SET F.PaidDate = FR.PayDate
FROM #Fundraising F
LEFT JOIN FundReceiptDetail FRD ON F.FundInvoiceID = FRD.FundInvoiceID
LEFT JOIN FundReceipt FR ON FRD.ID = FR.ID
--This is the Select statement that returns the values for the report
SELECT * FROM #Fundraising
WHERE Fund IN (@Fund)
DROP TABLE #Fundraising
END
January 16, 2011 at 4:07 pm
Actually, nevermind, what you are looking for is this:
SELECT * FROM #Fundraising
WHERE Fund IN (select Item from dbo.NDelimitedSplit(@Fund,','))
OR
SELECT
fr.*
FROM
#Fundraising fr
INNER JOIN dbo.NDelimitedString(@Fund,',') ds
ON (fr.Fund = ds.Item)
January 16, 2011 at 4:28 pm
Using either of your examples, I get the values returned as multiple rows. SQL then only filters on the first row. For example, if @Fund = 'Annual Giving 2008, Annual Giving 2009' then SQL only displays the results for 'Annual Giving 2008'. There are definitely results for 2009
January 16, 2011 at 4:37 pm
Will need the DDL for the tables involved (CREATE TABLE statements), sample data that is representative of your problem (a series of select into statements), and the expected results based on the sample data.
Don't go overboard on the data, only need enough to see what you are doing, not 80+ pages.
January 19, 2011 at 2:31 pm
Sorry for the delayed answer. I didn't provide any DDLs because I was sure that something wasn't right. I eventually got the string manipulation working, but found that the function was adding to the processing time of my query.
I then came across an article which explained that SSRS 2008 could not pass multi value parameters to stored procedures.
So I ended up dropping the function and stored procedure and instead embedded the query into my SSRS report. The result was a fully functionining report that passed the correct parameters to SQL (with the correct quotes), without any need for gymnastics of any kind in SQL. It works a charm.
Thanks for your help Lynn 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply