Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Using XML to pass Multi-Select parameters from SSRS to SQL Server Expand / Collapse
Author
Message
Posted Thursday, May 8, 2008 2:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
lklein (5/8/2008)
ok - adding the code.Return... enable it to run - however with no results.

Is this right for in my sp
C=my parameter name
Customer=label
Customer_Number=int value

INNER JOIN
@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')

please... anyone... thank you!


As you wrote the query, you should have a parameter in your stored procedure defined as @C XML.
I don't see anything wrong with your query. If it still doesn't return anything, try adding this to your procedure. When you run the report, you'll see what you're sending it.
declare @c1 varchar(max)
set @c1 = convert(varchar(max), @C)
RaisError (@c1, 16, 1)

At this point, you can copy the XML string to SSMS and debug your proc from there.

Also, you can run this to see if the XML string is processing correctly:
select m.item.value('Customer_Number[1]', 'integer') CustomerNumber
from @C.nodes('/C/Customer') AS m(item)

Just to clarify:
C=Root
Customer=Node
Customer_Number=Element



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497420
Posted Thursday, May 8, 2008 2:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 64, Visits: 265
C=Root is the parameter name both in the report and the sp and there is only this param
Customer=Node is the displayed label name of the param
Customer Number=Element is the actual value of the param

I ran the select sample code and just ran it as the sp code then ran it in the report data and it returned nothing.

I would love for this to work. Thank you for trying to help me. Any other ideas?
Post #497426
Posted Thursday, May 8, 2008 2:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 64, Visits: 265
The declare @c1 varchar(max)
set @c1 = convert(varchar(max), @C)
returns my params...
Post #497429
Posted Thursday, May 8, 2008 2:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
I think I found your problem (at least I hope so)...

you are calling:
=Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")

and in the procedure you are going:
INNER JOIN
@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')


I just ran a test, and found out that this is CaSe SeNsItIvE.
Change your select to:
INNER JOIN
@C.nodes('/C/CUSTOMER') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('CUSTOMER_NUMBER[1]','integer')

If you're getting no result set, then the improper case is in the nodes(). If you only get a NULL result, then it's in the value().
Most likely, it's in both.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497436
Posted Thursday, May 8, 2008 2:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 64, Visits: 265
Dang I wish that were it - it's all caps in both the sp and in the report pieces.
Post #497441
Posted Thursday, May 8, 2008 2:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 64, Visits: 265
this is what I have in my sp
INNER JOIN
@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')
Post #497445
Posted Thursday, May 8, 2008 2:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
lklein (5/8/2008)
Dang I wish that were it - it's all caps in both the sp and in the report pieces.

Is everything is spelled the same?

Well, can you:
1. Post the code you are doing with the ReturnXML(), and
2. Post the procedure code (at least the parameters and select statement), and
3. Post the XML string you're getting from the RaisError code above.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497449
Posted Thursday, May 8, 2008 2:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 64, Visits: 265
First off thank you so much for trying to help me... I've tried the passing and parsing out - that doesn't work. I'm wondering if there's a setting I need.

The raise error - I'm not sure how that works but it didn't return anything -

=Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")

and the sp:
ALTER proc [dbo].[spCUSTOMER_PROMO] @C XML
--, @INPUT_DATE DATETIME
AS
declare @INPUT AS table (
CUSTOMER VARCHAR(35),
CUSTOMER_NUMBER VARCHAR(10),
PREM NUMERIC(18,2),
PREM_VALID VARCHAR(25),
PREM_CODE VARCHAR(25),
FREIGHT NUMERIC(18,2),
FREIGHT_VALID VARCHAR(25),
FREIGHT_CODE VARCHAR(25) )

INSERT INTO @INPUT (CUSTOMER,CUSTOMER_NUMBER,PREM,PREM_VALID,PREM_CODE)
SELECT CS.CUSTOMER_NAME, TA.CUSTOMER_NUMBER,TD.TP_DISCOUNT_AMOUNT,
CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101),
TA.TP_PROMOTION_CODE
FROM TP_CUSTOMER_ASSOCIATIONS AS TA INNER JOIN
TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE INNER JOIN
CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER INNER JOIN
TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE INNER JOIN
@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')
WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE AND
--(CS.CUSTOMER_NUMBER IN (SELECT VALUE FROM dbo.FX_SPLIT(@C,','))) and
TP.TP_PROGRAM_ID IN ('PREMIUM')

INSERT INTO @INPUT (CUSTOMER,CUSTOMER_NUMBER,FREIGHT,PREM_VALID,PREM_CODE)
SELECT CS.CUSTOMER_NAME, TA.CUSTOMER_NUMBER,TD.TP_DISCOUNT_AMOUNT,
CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101),
TA.TP_PROMOTION_CODE
FROM TP_CUSTOMER_ASSOCIATIONS AS TA INNER JOIN
TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE INNER JOIN
CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER INNER JOIN
TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE INNER JOIN
@C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')
WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE AND
--(CS.CUSTOMER_NUMBER IN (SELECT * FROM dbo.fnDStringToTable(@C,','))) and
TP.TP_PROGRAM_ID IN ('FREIGHT')

select * from @INPUT ORDER BY CUSTOMER
Post #497452
Posted Thursday, May 8, 2008 3:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
As far as your XML code goes, I don't see anything wrong with what you're doing.
I'd really like to see the XML string that's being passed to the report.

How about trying this code out? (I consolidated your code into one select statement, and formatted it some to make it easier for me to read).
When you call this from your report, you should get the XML string that you're passing to the procedure. Can you copy it and put it up here?

ALTER proc [dbo].[spCUSTOMER_PROMO] @C XML
--, @INPUT_DATE DATETIME
AS

declare @c1 varchar(max)
set @c1 = convert(varchar(max), @C)
RaisError(@c1, 16, 1)

declare @temp varchar(25) -- need something since two fields are never inserted into, but needed for output
select
CS.CUSTOMER_NAME CUSTOMER,
TA.CUSTOMER_NUMBER,
CASE WHEN TP.TP_PROGRAM_ID = 'PREMIUM' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END PREM,
CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101) PREM_VALID,
TA.TP_PROMOTION_CODE,
CASE WHEN TP.TP_PROGRAM_ID = 'FREIGHT' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END FREIGHT,
@temp FREIGHT_VALID,
@temp FREIGHT_CODE
FROM TP_CUSTOMER_ASSOCIATIONS AS TA
INNER JOIN TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE
INNER JOIN CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER
INNER JOIN TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE
INNER JOIN @C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')
WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE
AND TP.TP_PROGRAM_ID IN('PREMIUM', 'FREIGHT')
ORDER BY CS.CUSTOMER_NAME

-- =Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #497470
Posted Thursday, May 8, 2008 3:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 64, Visits: 265
This is so frustrating... yikes - I get an error running that just in design view right?
here's the whole detail
===================================

An error occurred while executing the query.
10008 (Microsoft Report Designer)

===================================

10008 (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=50000&LinkId=20476

------------------------------
Server Name: x.x.com
Error Number: 50000
Severity: 16
State: 1
Procedure: spCUSTOMER_PROMO
Line Number: 7


------------------------------
Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.ExecuteQuery()

Post #497476
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse