February 3, 2012 at 8:45 am
Hi Guys,
I have an SP which has table column names as input parameters. The proc uses dynamic query and uses column names from input parameter to update the columns... I was thinking what if not all the columns need to be updated? I can do that by using IF, but there are at least 5 columns and need to create IF for all combinations... is there another way to do that? Another way I thought was of breaking into separate procs and create a wrapper Proc with IF... Any suggestion guys.. please let me know if I am not clear.
Thanks,
Laura
February 3, 2012 at 8:47 am
Laura_SqlNovice (2/3/2012)
Hi Guys,I have an SP which has table column names as input parameters. The proc uses dynamic query and uses column names from input parameter to update the columns... I was thinking what if not all the columns need to be updated? I can do that by using IF, but there are at least 5 columns and need to create IF for all combinations... is there another way to do that? Another way I thought was of breaking into separate procs and create a wrapper Proc with IF... Any suggestion guys.. please let me know if I am not clear.
Thanks,
Laura
Dynamic SQL built from sys.columns would solve this issue.
If you want a more detailed answer with code, post your current code and business logic.
February 3, 2012 at 8:51 am
Are you trying to build an update sproc that will only update the columns that are supplied? That seems like an awful lot of extra overhead and processing. Given what you describe the only way to make sure you get all possible combinations would be to have an update statement to run for each column or query sys.syscolumns. This all seems like a lot of extra effort for little gain.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 8:52 am
Sean Lange (2/3/2012)
Are you trying to build an update sproc that will only update the columns that are supplied? That seems like an awful lot of extra overhead and processing. Given what you describe the only way to make sure you get all possible combinations would be to have an update statement to run for each column or query sys.syscolumns. This all seems like a lot of extra effort for little gain.
sys.syscolumns is deprecated isn't it?
February 3, 2012 at 8:56 am
Cadavre (2/3/2012)
Sean Lange (2/3/2012)
Are you trying to build an update sproc that will only update the columns that are supplied? That seems like an awful lot of extra overhead and processing. Given what you describe the only way to make sure you get all possible combinations would be to have an update statement to run for each column or query sys.syscolumns. This all seems like a lot of extra effort for little gain.sys.syscolumns is deprecated isn't it?
/facepalm
Yes, yes it is but it just rolls off the keyboard so easily.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 8:56 am
Yes that is what I was trying to do and I think I am trying to do... Probably I should use a table to hold the input parameters and use that instead... Out of 5 columns sometimes they might want to update only 3 columns and could send nulls for 2 columns...
Sean Lange (2/3/2012)
Are you trying to build an update sproc that will only update the columns that are supplied? That seems like an awful lot of extra overhead and processing. Given what you describe the only way to make sure you get all possible combinations would be to have an update statement to run for each column or query sys.syscolumns. This all seems like a lot of extra effort for little gain.
February 3, 2012 at 9:01 am
I still think you are creating a lot of unneeded work but I of course have no idea about your business requirements. Probably the easiest way to get that in a table is to delimit the column names and then use Jeff Moden's splitter (see link in my signature). You should be able to use that to build your list of updates without a cursor. I would be curious to see the sproc you are working on and to hear the business requirements to pass a different amount of parameters everytime.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 12:50 pm
Just a snippet; also I'm not a huge fan of dynamic SQL : you'll also want to only let your col_names get generated if the input belongs to a protected list (create some sort of domain-table to help avoid some of the side effects of bad dynamic SQL . The following can be used in a proc. You can also specify parameters of proc as NULLable, and use ISNULL or something to determine when to update or not. That can be done in a single update statement without complicated IF-type logic, and far safer than dynamic SQL
DECLARE@s_sqlstr varchar(max),
@col_names varchar(max) = '',
@s_where varchar(max)
SET@s_sqlstr = 'UPDATE myTable SET '
SET@s_where = ' WHERE someKey = keyValue '
SET@col_names = @col_names + 'myColName1 = ''Frank'', '
SET@col_names = @col_names + 'myColName2 = ''Bob'', '
IF(LEN(@col_names) > 0)
BEGIN
SET @s_sqlstr = @s_sqlstr + @col_names + @s_where
EXEC (@s_sqlstr) -- or SELECT @s_sqlstr to see how the build of the string looks
END
February 3, 2012 at 2:07 pm
Thanks Kevin... I will try this approach.. looks interesting.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply