July 8, 2008 at 2:52 am
Hi all,
I'd like to convert several rows of my table to csv list
create table my_contracts(
customer_id int,
contract_id int
)
insert into my_contracts values (1,100)
insert into my_contracts values (1,101)
insert into my_contracts values (2,102)
select * from my_contracts
---------------------------
customer_id contract_id
---------------------------
1100
1101
2102
I found some useful postings about this (recursive or via XML). Here astatic solution:
create FUNCTION my_list_static
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
declare @list varchar(1000)
select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from contracts
where customer_id= 1 order by contract_id
RETURN @list
/*
select dbo.my_list_static() as list_of_contracts
*/
END
GO
my_list_static() works as it should, I find
list_of_contracts
-----------------
100, 101
Now I want to create a dynamic function, which can be used in general
alter FUNCTION my_list_dynamic
(
@table varchar(100),
@field_to_list varchar(100),
@criteria varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
declare @list varchar(1000)
declare @sql varchar(1000)
set @sql='select @list= isnull(@list + '', '','''') + convert(varchar(1000),' + @field_to_list + ')'
set @sql= @sql + ' from ' + @table
set @sql= @sql + ' where ' + @criteria + ' order by ' + @field_to_list
-- this is what I find in @sql:
--select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from my_contracts where customer_id= 1 order by contract_id
-- here I try to fetch the value into the variable by exec but it fails!
exec @sql
RETURN @list
END
GO
/*
select dbo.my_list_dynamic('my_contracts', 'contract_id','customer_id= 1') as list_of_contracts
*/
Any idea how I can resolve this problem?
Thanks you a lot for each answer...
Best Regards
Martin Berkl
July 9, 2008 at 7:46 am
Take note of the added parentheses around @sql in your EXEC statement...
Steve
(aka smunson)
:):):)
smerg (7/8/2008)
Hi all,I'd like to convert several rows of my table to csv list
create table my_contracts(
customer_id int,
contract_id int
)
insert into my_contracts values (1,100)
insert into my_contracts values (1,101)
insert into my_contracts values (2,102)
select * from my_contracts
---------------------------
customer_id contract_id
---------------------------
1100
1101
2102
I found some useful postings about this (recursive or via XML). Here astatic solution:
create FUNCTION my_list_static
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
declare @list varchar(1000)
select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from contracts
where customer_id= 1 order by contract_id
RETURN @list
/*
select dbo.my_list_static() as list_of_contracts
*/
END
GO
my_list_static() works as it should, I find
list_of_contracts
-----------------
100, 101
Now I want to create a dynamic function, which can be used in general
alter FUNCTION my_list_dynamic
(
@table varchar(100),
@field_to_list varchar(100),
@criteria varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
declare @list varchar(1000)
declare @sql varchar(1000)
set @sql='select @list= isnull(@list + '', '','''') + convert(varchar(1000),' + @field_to_list + ')'
set @sql= @sql + ' from ' + @table
set @sql= @sql + ' where ' + @criteria + ' order by ' + @field_to_list
-- this is what I find in @sql:
--select @list= isnull(@list + ', ','') + convert(varchar(1000),contract_id) from my_contracts where customer_id= 1 order by contract_id
-- here I try to fetch the value into the variable by exec but it fails!
exec (@sql)
RETURN @list
END
GO
/*
select dbo.my_list_dynamic('my_contracts', 'contract_id','customer_id= 1') as list_of_contracts
*/
Any idea how I can resolve this problem?
Thanks you a lot for each answer...
Best Regards
Martin Berkl
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2008 at 7:47 am
Hi,
As you've no doubt guessed - You can't execute a stored procedure from a function:
Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.
If I had to fix your problem, I'd probably consider going down the CLR route - Use managed code data-access to build up your CSV, then return that from the assembly? Any CLR experts want to offer help?
Good luck - Leo.
July 9, 2008 at 8:28 pm
Leo Mepham (7/9/2008)
Any CLR experts want to offer help?
No... but you don't need any CLR experts to do this either... Leo... I'm not picking on you but
I have a bone to pick with anyone that will listen...
SEE????!!!?!?! Everybody keeps saying "Oh, cursors have their place" or "Oh,
CLR's have their place"... "You should only use them when no other way is possible..."
The problem is, PEOPLE WHO DON'T REALLY KNOW SQL ARE GOING TO USE THEM ALL THE
BLOODY TIME!!!! And they never learn T-SQL in the process...
If you use a cursor or a While loop or a CLR to get something done, there's a 99.9% chance
that it's because you don't know what you're doing in T-SQL (yet). Sure, there are some
routines that traverse databases or tables that might need a cursor or while loop (actually,
thanks to VARCHAR(MAX), NOT ANY MORE!)... you should probably use a CLR for things like
"RegEx replace" and maybe some very high level math... but the other 99.9% of the time,
you're just wrong to use them.
Take the simple problem posed on this thread...
Here's some test data...
[font="Courier New"]--===== Create and populate the test table
CREATE TABLE MyContracts
(
CustomerID INT,
ContractID INT
)
INSERT INTO MyContracts
(CustomerID,ContractID)
SELECT 1,100 UNION ALL
SELECT 1,101 UNION ALL
SELECT 1,102 UNION ALL
SELECT 1,103 UNION ALL
SELECT 1,104 UNION ALL
SELECT 1,105 UNION ALL
SELECT 1,106 UNION ALL
SELECT 1,107 UNION ALL
SELECT 1,108 UNION ALL
SELECT 2,202 UNION ALL
SELECT 3,301 UNION ALL
SELECT 3,302
--===== Verify the contents of the test table
SELECT * FROM MyContracts
[/font]
... and here's the solution...
[font="Courier New"]--===== Do the concatenation without a CLR!
SELECT t1.CustomerID ,
STUFF((SELECT ',' + CAST(t2.ContractID AS VARCHAR(10))
FROM MyContracts t2
WHERE t1.CustomerID = t2.CustomerID FOR XML PATH(''))
,1,1,'')
FROM MyContracts t1
GROUP BY t1.CustomerID[/font]
Now, somebody tell me why we need a CLR for that? That answer is, because the original
folks doing the programming didn't have a clue how to do it in T-SQL and instead of trying
to find out how, the went to the non-database programmers Nirvana known as a CLR.
And OH so many think a CLR will run quicker... consider this... they don't know how to
write some T-SQL to begin with so they write some crap T-SQL and then compare that
against a CLR which may run good even if it too is crap... of course the CLR is going to win!
You fixed the bloody race! 😉
Heh... can you tell someone is standing on my last nerve about this very subject today? 😀
Don't even get me started on people who write code so wide that you always have to
^(%*$&$! scroll to the right to read it :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2008 at 8:51 pm
Martin,
I also have to say that this is usually a very bad thing to do in SQL Server... most
will tell you that if you need to do it for the GUI, then do it in the GUI... that is, of
course, if you have a GUI. 😛
Other than that, I hope my solution helps you... and don't let people talk you into
CLR's just because they can't figure it out in SQL without some good amount of
performance. 😉 And, NO, putting something in a CLR does NOT automatically mean
that it's going to be faster. In fact, Matt Miller and I did some pretty severe testing
(I just gotta find that URL again) and we beat the pants off of every CLR problem
thrown at us with the exception of some very complicated RegEx Replace
functions... even then, we still came close! :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2008 at 1:47 am
Jeff, I work with TSQL all day long, and I do read & enjoy your pieces on here - But I don't see that you've solved his problem? :ermm: How would you create the dynamic function in your example? You've done a good example for static CSV creation, but I don't see how that could be converted into a dynamic-column-selecting CSV generator.
I went down the CLR route because the original poster has painted himself into a corner, and now needs something that should be impossible in standard SQL - He needs to execute dynamic SQL for each row of a resultset. We don't know WHY he needs to do this, so perhaps I shoul have established that in order to find a more compliant solution.
At my workplace, I don't use the CLR, I tried it for string splitting - We have ended up with lots of sprocs taking CSV lists of params, so I needed a fast splitter. Turned out that although the CLR was slightly faster, because it was opaque the query optimiser ended up mis-guessing the 'estimated rows' so badly that it was faster to go back to the tally table! Sommarskog's pages were invaluable there 😀
If you have something that fulfills the OP's requirements, some TSQL that fits into a function that can be called with params, that will output CSVs based on column names passed in - I'd be interested in seeing that.
If the CLR is not acceptable, is the possible sample set small enough that you could make every combination function? So, you'd have fnCSVfromContractID, fnCSVfromCustomerID, fnCSVfromContractIDCustomerID. These could then be selected from a master function fnCSV based on which column names you pass in? Perhaps a little excessive, but it could solve the problem without recourse to the CLR?
Best wishes - Leo.
July 10, 2008 at 6:50 pm
Leo Mepham (7/10/2008)
Jeff, I work with TSQL all day long, and I do read & enjoy your pieces on here - But I don't see that you've solved his problem? :ermm: How would you create the dynamic function in your example? You've done a good example for static CSV creation, but I don't see how that could be converted into a dynamic-column-selecting CSV generator.
OP didn't ask for a dynamic-column-selecting CSV generator... OP asked for something that would create CSV's grouped by EmployeeID... Run my code example and read the OP... it does exactly what was asked.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 11, 2008 at 1:38 am
Hi all, I'd like to convert several rows of my table to csv list
my_list_static() works as it should
Now I want to create a dynamic function, which can be used in general
alter FUNCTION my_list_dynamic
(
@table varchar(100),
@field_to_list varchar(100),
@criteria varchar(100)
)
Note also that he wants to pass in as a parameter, the column to create the CSV from. That's what was asked. OP - Are you still out there and could you clarify? Do you need to pass the column in as a parameter or was that just an example? In which case has Jeff solved this for you or do need further help?
July 11, 2008 at 2:34 am
Hi,
I'm still here (very busy at the moment, so I didn't reply so quickly...). I need to pass column and tablename as parameters, so my problem is not yet solved.
Of course I could use a static solution, but this was not the first time I had similar problems. So I would really appreciate a solution.
Thanks
smerg
July 11, 2008 at 3:12 am
Hi Smerg,
If that is the requirement then I really can't see any other way from the CLR. You just can't execute dynamic SQL in a function, so using the CLR as a 'cheat' is all I can think of.
If you haven't used the CLR its not so bad, hopefully you have some .Net experience?
Here is a good place to start:
http://msdn.microsoft.com/en-us/library/ms345135.aspx
You create a wrapper function in TSQL that accepts your table/column name plus other params as required, then passes these on to the method/class in the assembly you've created. It isn't as hard as it sounds, MSDN simple example hacked up a little to match more what you need:
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class CLRFunctions
{
[SqlFunction]
public static SqlString fnCSV(SqlString columnName, SqlString tableName)
{
// as usual, connection strings shouldn't be hardcoded for production code
using(SqlConnection conn = new SqlConnection(
"server=MyServer; database=AdventureWorks; " +
"user id=MyUser; password=MyPassword"))
{
conn.Open();
//Build up your CSV-making query here
SqlCommand cmd = new SqlCommand(
"SELECT " + columnName + " FROM " + tableName, conn);
SqlDataReader r = cmd.ExecuteReader();
string CSVResults = "";
while(r.Read())
{
CSVResults += r["" + columnName + ""].ToString() + ",";
}
}
return CSVResults;
}
}
So, in the above, you'd pass in the parameters, construct the query as if you were using dynamic SQL, then use the r.Read() loop to concatenate your CSV, which the assembly then returns to the SQL function, which returns it to the row in question.
By the way - The above is not tested or compiled, it's just to give you an example of the level of difficulty in dealing with the CLR - How is your .Net coding? I'm sure you'll be fine constructing the dynamic SQL though inside the function though, just add extra parameters to the above class and build the query as required.
To get the CLR up & running, get your class written then compile into a DLL using either Visual Studio, or CSC.exe (http://msdn.microsoft.com/en-us/library/78f4aasd(VS.80).aspx). Load the DLL into SQL Server and create the TSQL 'wrapper' for the assembly method (http://www.sqldbatips.com/showarticle.asp?ID=22).
Undoubtedly, this is not an ideal solution - Is there any other way you could approach this? Consider the usual methods in SQL Server - Constructing a function that can do anything means there is no way for SQL Server to optimise it - So performance will always be terrible.
Do take a look at http://www.sommarskog.se/dynamic_sql.html if you can. His site is full of good TSQL theory & tests, perhaps there is a way to solve your problem without recourse to dynamic SQL or the CLR, but without knowing your situation/requirements/tables etc there's not much else I can suggest.
Let me know if any of this helps, or if there's anything else you'd like to add?
Best of luck, now I'd better get out of here before Jeff comes to shout at me again 🙁
July 11, 2008 at 5:04 am
I wonder why people say it is IMPOSSIBLE to call a stored procedure from a function. It cannot be done directly, but, indirectly, YES it is POSSIBLE. I just ran through the Blog of Denis Gobo illustrating the trick to do it.
if your table data is not HUGE, it can be done easily.
Here is the Code to call sp from a UDF...
USE database
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go
--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go
--And here is the function
Alter FUNCTION fnBla(@id int)
RETURNS varchar(max)
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -SATIFULLAH -E -q "exec dbs..prLog ''fnBla''"'
--print @SQL
EXEC master..xp_cmdshell @SQL
RETURN @SQL
END
--Now call the function a couple of times
SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)
plz check the following link from where I took the Code;
http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx">
http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx
OP can use this technique to accomplish his/her goal easily.
Enjoy T-SQL...:D
Atif Sheikh
July 11, 2008 at 5:31 am
Nicely found. I doubt either of our solutions are ideal as far as performance and security goes, but it's good to find two solutions to a problem that intially seemed impossible.
Ideally the OP should redesign the table structure so that things like this aren't neccessary, but I know in the real world that's not always feasible 🙂
Looks like Atif's solution will be easiest for you to implement then Smerg, good luck!
PS: Do read up on the security you should implement with xp_cmdshell - It does have a possible downside.
July 11, 2008 at 6:35 am
Sorry... I just couldn't imagine why anyone would want a dynamic solution for this... now I see one (pass multiple different CSR parameter lists back to a GUI). Still, the need for doing that particular task should not occur that often and a dedicated UDF for when it does happen just doesn't seem all that unreasonable.
I don't have the ability to run CLR's on my box... it would be interesting if someone actually made a dynamic CLR and checked the performance against a "hard coded" UDF to compare performance. I can help provide a test table to run it against if anyone is interested.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 11, 2008 at 6:38 am
Leo Mepham (7/10/2008)
Jeff, I work with TSQL all day long, and I do read & enjoy your pieces on here - But I don't see that you've solved his problem? :ermm: How would you create the dynamic function in your example? You've done a good example for static CSV creation, but I don't see how that could be converted into a dynamic-column-selecting CSV generator.
Wow... I owe you an appology, Leo. I must not have had enough coffee when first responding to this... I couldn't understand the need for a UDF when a result set was so simple... now I get it and I agree... I didn't come close.
Thanks for the feedback, Leo.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 11, 2008 at 8:54 am
No problem Jeff, I figured you may have skim-read it. I've knocked up a working version of the CLR assembly, some test data is as follows:
Firstly, the working code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class CLRFunctions
{
[SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlString fnCSV(SqlString columnName, SqlString tableName, SqlString whereClause)
{
//Declare results CSV
SqlString CSVResults = "";
//Create Query
string sqlQuery = "SELECT " + columnName.ToString() + " FROM " + tableName.ToString();
//Add where clause if applicable
if (!String.IsNullOrEmpty(whereClause.ToString()))
sqlQuery += " " + whereClause.ToString();
//Use the current connection
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
conn.Open();
SqlCommand sqlCommand = new SqlCommand(sqlQuery, conn);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
CSVResults += sqlDataReader[columnName.ToString()].ToString() + ",";
}
//Trim trailing comma
if (CSVResults.ToString().Length > 0)
CSVResults = CSVResults.ToString().Substring(0, CSVResults.ToString().Length - 1);
}
return CSVResults;
}
}
I've compiled this into a DLL & loaded onto my test database.
Next create the TSQL function:
USE [Work]
GO
/****** Object: UserDefinedFunction [dbo].[fnCSV] Script Date: 07/11/2008 15:30:04 ******/
CREATE FUNCTION [dbo].[fnCSV](@columnName [nvarchar](4000), @tableName [nvarchar](4000), @whereClause [nvarchar](4000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLRCSV].[CLRFunctions].[fnCSV]
Lastly, a table with some test data:
CREATE TABLE TestData
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DataString NVARCHAR(8) NOT NULL
)
DECLARE @i INT
SET @i = 10000
WHILE @i > 0
BEGIN
INSERT INTO TestData(DataString)
SELECT LEFT(NEWID(),8)
SET @i = @i-1
END
Apologies for using a loop Jeff 😉
So, now we have everything in place, some time trials: (Note, all trials on my workstation - Intel Core 2 @ 1.9GHz, 2GB ram, SQL Server 2005 V 9.0.3068)
SET STATISTICS TIME ON
SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 10')
SET STATISTICS TIME OFF
Results: 7ms
SET STATISTICS TIME ON
SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 100')
SET STATISTICS TIME OFF
Results: 17ms
SET STATISTICS TIME ON
SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 1000')
SET STATISTICS TIME OFF
Results: 59ms
SET STATISTICS TIME ON
SELECT dbo.fnCSV('DataString','TestData','WHERE ID < 10000')
SET STATISTICS TIME OFF
Results: 2463ms - FAIL - No results! Perhaps some string overflow somewhere? It would have been 10000 * 8 characters, so perhaps that's the reason :ermm:
Lastly, can we use this function properly in a query?
Jeff, in your honour, here's a triangular RBAR done in the CLR:
SET STATISTICS TIME ON
SELECT
ID,
dbo.fnCSV('DataString','TestData','WHERE ID < ' + CAST(ID AS NVARCHAR(100))) AS OutputCSV
FROM
TestData
WHERE
ID < 100
SET STATISTICS TIME OFF
Results: 310ms
Lastly, a major test, all 10,000 rows in a triangular RBAR:
SET STATISTICS TIME ON
SELECT
ID,
dbo.fnCSV('DataString','TestData','WHERE ID < ' + CAST(ID AS NVARCHAR(100))) AS OutputCSV
FROM
TestData
SET STATISTICS TIME OFF
Results: Had to be cancelled at around 5 minutes, it was up to row 3,000 of 10,000 and slowing my machine to a halt 🙁
DLL attached in case Smerg still needs it.
Best wishes all & have a great weekend - Leo
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply