Formatting/Returning stored proc results as XML

I have a stored proc that contains a simple select statement. The select statement does not use the 'FOR XML' clause, nor can the select statement be modified to use the 'FOR XML' clause (for compatibility reasons)

Is there a way to execute the stored proc such that it returns its output in XML? I'm looking for something similar to this:

Code Snippet

exec spGetUserDetails @.ID = 1234 for xml raw

Thanks.

I found an approach that would preserve backwards compatibility while allowing me to manipulate results retrieved by a stored proc. It has to do with Table-valued user-defined functions:

Move the select statement from the stored proc into a user--defined function that returns TABLE:

Code Snippet

-- Example from Books Online

CREATE FUNCTION fn_SalesByStore (@.storeid int)
RETURNS TABLE
AS
RETURN ( SELECT * from Products where StoedID = @.storeid );

Modify the original stored proc to call this user-defined function, i.e., the stored proc wraps the UDF hence preserving backwards compatibility:

Code Snippet

-- Inside the stored proc comment the select statement and call the UDF

-- SELECT * from Products where StoedID = @.storeid -- No longer needed

select * from fn_SalesByStore (@.storeid);

Now I can call the UDF to retreive the same results that would have been retrieved by the original stroed proc, and more importantly, manipulate them as required.

Code Snippet

select * from fn_SalesByStore (@.storeid) for xml raw

Yazan

formatting dates

Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
2004-01-19 00:00:00.000
but I want it to be like this:
1/19/04 or 1/19/2004
Is there a way to do this?
Thanks.Try this:

SELECT CONVERT(char(10),getdate(),103)|||Please.. use the 101 code for the date format..

like this:

SELECT CONVERT(char(10),getdate(),101)

the other example is another format.

You can consult all the date format in the SQL SERVER BOOKS ON LINE!.|||But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Just replace the getdate() with checkedOutDate. Do the same again with ReturnedDate (a separate query).

Originally posted by domiflichi
But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Look at my post from yesterday. I had the same question.
exdter|||That's what I thought you're supposed to do. But when I do that...here's my new SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),checkedOutDate,101), ReturnedDate FROM Checkouts

I get an error when trying to display my results of the query when encountering that field:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 173

If it helps: my webserver is Win2k Pro running Apache and PHP, and my SQL server is on Win2K server and it's actually MSDE 2000.|||Originally posted by exdter
Look at my post from yesterday. I had the same question.
exdter

I'm getting the same error message on my query:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 174

Here's the SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103), ReturnedDate FROM Checkouts

Why isn't this working for me?
Thanks,
domiflichi|||Try this

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP
, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103) AS checkedOutDate, ReturnedDate FROM Checkouts|||Thank you Brett!...it worked! And thank you to everybody else for getting me through each step, and being patient with this newbie. And thank you all for such quick replies!

Format parameter date string

I have a Analysis services dimension which is returning a date in the format
yyyy/mm/dd, I have added reporting filters (Parameters from-Date and to-Date
with datatype date to show calendar) for this date which is showing the
selected date in the format mm/dd/yyyy. How can I "format the parameter
values" or "write the expression" or "update MDX" so that the report shows
data within the filter dates?
Thanks and Regards
SridharFYI: I am using SQL Server 2005 and web-based reports using reporting
services.
--
Sridhar
>I have a Analysis services dimension which is returning a date in the
>format yyyy/mm/dd, I have added reporting filters (Parameters from-Date and
>to-Date with datatype date to show calendar) for this date which is showing
>the selected date in the format mm/dd/yyyy. How can I "format the parameter
>values" or "write the expression" or "update MDX" so that the report shows
>data within the filter dates?
>
> Thanks and Regards
> Sridhar
>