October 22, 2017 at 7:08 pm
Summary: I've got a working solution(s). My question is, which is the best approach (if any)?
Details: I want to write a stored procedure with parameters like:
dbo.foo(@list_of_columns='foo bar blah', @delimiter=' ')
or in the incredibly rare (never???) occasions where a column could contain a space:
dbo.foo(@list_of_columns='foo^bar blah^blech', @delimiter='^') (end user can choose the delimiter)
What I want to create from the column list is:
'foo','bar','blah' or 'foo','bar blah','blech'
I just want to make it easy for the end user, so he/she doesn't have to supply syntax, just the columns they want to process.
I'll then validate the list of columns using INFORMATION_SCHEMA.columns, with the derived list in the where clause.
I have created (ok, stolen) the list parser documented here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Here are my two approaches. My preference is approach #1, but which one do you recommend as the best approach (assuming you favour one over the other).
CREATE PROCEDURE [dbo].[uspCreateQuotedListV1]
( @Columns VARCHAR(8000)
, @Delimiter CHAR(1) = ' '
, @Quote CHAR(1) = ''''
, @Separator CHAR(1) = ','
)
AS
BEGIN
DECLARE @quoted_list VARCHAR(8000);
SELECT @quoted_list =
STUFF(
(
SELECT @Separator+QUOTENAME(LTRIM(RTRIM(ds.Item)),@Quote)
FROM dbo.udfDelimitedSplit8K(@Columns,@Delimiter) ds
WHERE ds.Item != ''
FOR XML PATH ('')
),1,1,'')
PRINT @quoted_list
END
CREATE PROCEDURE [dbo].[uspCreateQuotedListV2]
( @Columns VARCHAR(8000)
, @Delimiter CHAR(1) = ' '
, @Quote CHAR(1) = ''''
, @Separator CHAR(1) = ','
)
AS
BEGIN
DECLARE @quoted_list VARCHAR(8000) = '';
DECLARE @start INT = 1;
DECLARE @end INT;
SET @Columns = LTRIM(RTRIM(@Columns)) + @Delimiter;
SET @end = CHARINDEX(@Delimiter,@Columns,@start);
WHILE @end > 0
BEGIN
IF SUBSTRING(@Columns,@start,@end-@start) != ''
SET @quoted_list += @Separator+QUOTENAME(LTRIM(RTRIM(SUBSTRING(@Columns,@start,@end-@start))),@Quote);
SET @start = @end+1;
SET @end = CHARINDEX(@Delimiter,@Columns,@start);
END
SET @quoted_list = STUFF(@quoted_list,1,1,'');
PRINT @quoted_list;
END
And some test code:
EXEC uspCreateQuotedListV1 'foo bar blah'
EXEC uspCreateQuotedListV1 ' foo bar blah '
EXEC uspCreateQuotedListV1 ' foo bar blah ',@Quote=''''
EXEC uspCreateQuotedListV1 ' foo bar blah ',@Quote='"'
EXEC uspCreateQuotedListV1 ' foo bar blah ',@Quote='['
EXEC uspCreateQuotedListV1 ' foo bar blah ',@Quote=']'
EXEC uspCreateQuotedListV1 ' foo bar blah ',@Quote='+' --Empty string, I haven't added error checking on quote character
EXEC uspCreateQuotedListV1 ' foo bar blah ',@Separator='|'
EXEC uspCreateQuotedListV1 ' foo^ bar blah ^ blech ',@Delimiter='^'
EXEC uspCreateQuotedListV1 ' foo^ bar blah ^ blech ',@Delimiter='^',@Separator='~'
--=======================================
EXEC uspCreateQuotedListV2 'foo bar blah'
EXEC uspCreateQuotedListV2 ' foo bar blah '
EXEC uspCreateQuotedListV2 ' foo bar blah ',@Quote=''''
EXEC uspCreateQuotedListV2 ' foo bar blah ',@Quote='"'
EXEC uspCreateQuotedListV2 ' foo bar blah ',@Quote='['
EXEC uspCreateQuotedListV2 ' foo bar blah ',@Quote=']'
EXEC uspCreateQuotedListV2 ' foo bar blah ',@Quote='+' --Empty string, I haven't added error checking on quote character
EXEC uspCreateQuotedListV2 ' foo bar blah ',@Separator='|'
EXEC uspCreateQuotedListV2 ' foo^ bar blah ^ blech ',@Delimiter='^'
EXEC uspCreateQuotedListV2 ' foo^ bar blah ^ blech ',@Delimiter='^',@Separator='~'
October 23, 2017 at 1:31 am
I'm still interested in what you think is the best approach.
However, as I've developed this further, I think I'll need the first approach. I forgot you can't use a @variable in a IN( ) list without using dynamic SQL.
Latest revision:
CREATE PROCEDURE [dbo].[uspCreateColumnList]
( @Table SYSNAME
, @Columns VARCHAR(8000)
, @Delimiter CHAR(1) = ' '
, @QuoteChar CHAR(1) = ''''
, @Separator CHAR(1) = ','
, @Quote BIT = 1
, @Output VARCHAR(8000) OUTPUT
)
AS
BEGIN
DECLARE @object_id INT = NULL;
SET @object_id = OBJECT_ID(@Table);
IF @object_id IS NULL
BEGIN
RAISERROR(
'Table or view %s not found.',12,1,@Table
);
RETURN;
END
IF @Quote = 1
BEGIN
SELECT @Output =
STUFF(
(
SELECT @Separator+QUOTENAME(c.name,@QuoteChar)
FROM sys.all_columns c
INNER JOIN
dbo.udfDelimitedSplit8K(@Columns,@Delimiter) ds
ON c.name LIKE ds.Item
WHERE object_id = @object_id AND ds.Item != ''
ORDER BY c.column_id
FOR XML PATH ('')
),1,1,'')
END
ELSE
BEGIN
SELECT @Output =
STUFF(
(
SELECT @Separator+c.name
FROM sys.all_columns c
INNER JOIN
dbo.udfDelimitedSplit8K(@Columns,@Delimiter) ds
ON c.name LIKE ds.Item
WHERE object_id = @object_id AND ds.Item != ''
ORDER BY c.column_id
FOR XML PATH ('')
),1,1,'')
END
END
And test code:
DECLARE @quoted_list VARCHAR(8000)
EXEC uspCreateColumnList 'master.sys.all_objects', '%', @Output=@quoted_list OUTPUT
PRINT @quoted_list
EXEC uspCreateColumnList 'master.sys.all_objects', 'name', @Output=@quoted_list OUTPUT
PRINT @quoted_list
EXEC uspCreateColumnList 'master.sys.all_objects', 'name type_desc', @Output=@quoted_list OUTPUT
PRINT @quoted_list
EXEC uspCreateColumnList 'master.sys.all_objects', 'name is_%', @Output=@quoted_list OUTPUT
PRINT @quoted_list
EXEC uspCreateColumnList 'master.sys.all_objects', 'name is_%', @QuoteChar='(', @Output=@quoted_list OUTPUT
PRINT @quoted_list
EXEC uspCreateColumnList 'master.sys.all_objects', 'name is_% %date', @QuoteChar='[', @Output=@quoted_list OUTPUT
PRINT @quoted_list
This will be used in a larger stored procedure to build dynamic SQL.
Feedback and suggestions most appreciated.
October 23, 2017 at 2:41 am
If it works, and you've tested it on large (say million-plus rows) data sets and it performs well, then go with it. If you're going to be using dynamic SQL later on, remember to guard against SQL injection.
John
October 26, 2017 at 10:37 pm
I've written this SP:
ALTER PROCEDURE [dbo].[sp_MoH_GetColumnList]
( @Table SYSNAME
, @Columns VARCHAR(8000) = '%'
, @Types VARCHAR(8000) = '%'
, @Delimiter CHAR(1) = ' '
, @QuoteChar CHAR(1) = ''''
, @Separator CHAR(1) = ','
, @Debug BIT = 0
, @Output VARCHAR(8000) = '' OUTPUT
)
AS
BEGIN
DECLARE @quoted_list VARCHAR(8000) = '';
DECLARE @object_id INT = NULL;
SET @object_id = OBJECT_ID(@Table);
IF @object_id IS NULL
BEGIN
RAISERROR(
'Table or view %s not found.',12,1,@Table
);
RETURN;
END
IF @QuoteChar != ''
BEGIN
SELECT @quoted_list =
STUFF(
(
SELECT @Separator+QUOTENAME(c.name,@QuoteChar)
FROM sys.all_columns c
INNER JOIN
sys.Types t
ON c.system_type_id = t.system_type_id
INNER JOIN
Tools.dbo.udf_MoH_DelimitedSplit8K(@Columns,@Delimiter) cols
ON c.name LIKE cols.Item
INNER JOIN
Tools.dbo.udf_MoH_DelimitedSplit8K(@Types,@Delimiter) types
ON t.Name LIKE types.Item
WHERE c.object_id = @object_id AND cols.Item != '' AND types.Item != ''
ORDER BY c.column_id
FOR XML PATH ('')
),1,1,'')
END
ELSE
BEGIN
SELECT @Output =
STUFF(
(
SELECT @Separator+c.name
FROM sys.all_columns c
INNER JOIN
sys.Types t
ON c.system_type_id = t.system_type_id
INNER JOIN
Tools.dbo.udf_MoH_DelimitedSplit8K(@Columns,@Delimiter) cols
ON c.name LIKE cols.Item
INNER JOIN
Tools.dbo.udf_MoH_DelimitedSplit8K(@Types,@Delimiter) types
ON t.Name LIKE types.Item
WHERE c.object_id = @object_id AND cols.Item != '' AND types.Item != ''
ORDER BY c.column_id
FOR XML PATH ('')
),1,1,'')
END
SET @Output = @quoted_list;
IF @Debug = 1
PRINT @Output
END
I've read how to install into master and mark as a system SP. When I did that, it works:
EXEC sp_MoH_GetColumnList 'sys.columns', @Debug=1
However, I then read that MS advise not to install user stuff in master. So, I created a Tools database, installed into there, and call this and other SP's with a fully-qualified name (Tools.dbo.sp_MoH_GetColumnList).
When I do this, it doesn't work (returns 0 columns).
I think the problem is execution scope, depending on which database is active.
Should I just go back to installing in master and running EXECUTE sp_MS_marksystemobject ‘sp_<this SP>’? Or is there a better way?
(udf_MoH_DelimitedSplit8K is cppied from http://www.sqlservercentral.com/articles/Tally+Table/72993/)
October 30, 2017 at 3:18 am
Scott In Sydney - Thursday, October 26, 2017 10:37 PMHowever, I then read that MS advise not to install user stuff in master......Should I just go back to installing in master and running EXECUTE sp_MS_marksystemobject ‘sp_<this SP>’?
Well, sp_MS_marksystemobject is undocumented, but that's not necessarily a reason to avoid it. By all means use it if it works for you. Include it in your own documentation, though, so that others don't see it as a licence to create all kinds of other stuff in master.
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply