August 21, 2013 at 7:09 pm
Use selects to build syntax? For instance, the other day I had to build a variable based on some column information in my table, so I did this:
select 'when quota = ' + quota + ' and center = ''' + center + ''' and pfield5 = ''' + pfield5 + '''' + ' then '
from table
group by quota, center, pfield5
order by quotacell, center, pfield5
The result was the meat of a case statement that coded a variable from 1-60. I was pretty happy with myself. I wish I had thought ahead a little bit and used something like below to fill in the THEN, rather than going back and doing it by hand after pasting.
I just used it again to build an update based on those 60 variables after making a lookup type table of the information:
select 'update table set reps = 3 where id in (select top '+ cast(records as varchar) + ' id from table where view = ' + cast(view as varchar) + ' and status = 0 and reps is null order by sortid)'
from table_lookup
Of course, I'm also interested in hearing if this is a bad idea for some reason that I'm unaware of.
August 21, 2013 at 9:23 pm
I use metadata of various sorts to generate code as much as I can. This tends to reduce the likelihood of errors in your code (assuming you have valid metadata etc). I typically generate table create scripts, stored procedures and am now moving on to generating SSIS packages.
The out of the box metadata that comes with every SQL Server database is really helpful - good knowledge of the various views in schema INFORMATION_SCHEMA and SYS are a really good start for this.
August 22, 2013 at 7:31 am
Sure I do stuff like that all the time. Given the EAV-ish type of query you posted yesterday I suspect you will need to leverage this type of thing all the time. đ
_______________________________________________________________
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/
August 22, 2013 at 7:41 am
Sean Lange wins the Twisting the Knife Before 10am award! Congratulations! đ
August 22, 2013 at 7:46 am
I do use sql quite frequently for generating code. But I also use excel, where I put into the first column my template sql with tokens for the variables e.g. ="SELECT 1 from {1} where {2} = 'bob'"
Columns B and C have values for {1} and {2}, and column D has a formula to replace {1} and {2} with the values in B and C. Does this make sense? A nice thing about Excel is that there is no conflict with ' and ".
happycat59 (8/21/2013)
I use metadata of various sorts to generate code as much as I can. This tends to reduce the likelihood of errors in your code (assuming you have valid metadata etc). I typically generate table create scripts, stored procedures and am now moving on to generating SSIS packages.
I also use xsl (xml stylesheets) quite a bit for transforming sql metadata (for documentation etc.) and have in the past written SSIS packages in this way. As the resulting document is an xml document (a dtsx file) xsl is in some ways the natural choice.
August 22, 2013 at 8:13 am
erikd (8/22/2013)
Sean Lange wins the Twisting the Knife Before 10am award! Congratulations! đ
Not my intention at all. Using sql to help you build sql is good idea and something that many people (including myself) do frequently. It can save hours of painful and tedious typing when you have to generate dozens of nearly identical lines.
_______________________________________________________________
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/
August 22, 2013 at 8:24 am
Used to be a really common way of doing multiple tasks in the "Good Old Day" before Microsoft gave us things like sp_MSForEachTable and the like...:-P
August 22, 2013 at 8:49 am
Sean Lange (8/22/2013)
erikd (8/22/2013)
Sean Lange wins the Twisting the Knife Before 10am award! Congratulations! đNot my intention at all. Using sql to help you build sql is good idea and something that many people (including myself) do frequently. It can save hours of painful and tedious typing when you have to generate dozens of nearly identical lines.
I was just kidding. It's not so bad. Sometimes. :Whistling:
August 22, 2013 at 9:40 am
For more on the xml approach, check out this article (the url is for part 2, but part 1 will give you the introduction.)
August 22, 2013 at 9:42 am
David McKinney (8/22/2013)
For more on the xml approach, check out this article (the url is for part 2, but part 1 will give you the introduction.)
This looks neat. I'll check it out this afternoon.
August 23, 2013 at 2:12 am
Absolutely. In fact, I have an app that gets most of its data via such queries. After much experimentation, including a SQL in the City session in London last summer, during which some heavyweights told me they didn't think much of my idea, I decided to use functions, each of which generates a separate clause, then tack them all together for execution.
There is so much in common for many of the various queries that it would be very difficult and error-prone to do them all manually. There are a few parameters that affect how the clauses are assembled, but much of it is quite similar. Calling a function is frowned upon as being inefficient, and it probably adds somewhat to the processing time, but I call the functions once for each SP execution, not within the select statement, where it could be executed thousands of times. The tiny amount of overhead for a single function call is well worth it for me. When something changes in one of the tables, all I have to change is the functions, instead of dozens of select statements. They're kind of long, but here they are:
The SELECT clause generator:CREATE FUNCTION [QConstant].[FieldsClause]
(@Cnt as int,
@WhichFields varchar(100))
RETURNS nvarchar(4000)
AS
BEGIN
declare @FldLst nvarchar(4000) = N'SELECT '
if @Cnt = 0
set @FldLst += N'*'
else if @Cnt = -1
set @FldLst += N'Top (1) -1'
else if @Cnt = -2
set @FldLst += N'Count(*)'
else if @Cnt > 0
set @FldLst += N'Top (' + cast(@Cnt as varchar(10)) + ') *'
--set @FldLst += case when @Cnt > 0 then 'Top (' + cast(@Cnt as nvarchar(10)) + ') ' else '' end -- MusĂ bĂ˝t else '', jinak se z frĂĄze stane null a vĹĄechno zmizĂ.
set @FldLst += N' FROM ( SELECT '
if charindex('Real',@WhichFields) > 0
set @FldLst += N'
P.PodrobnostiAutoID, P.EvidenceLetter, P.EvidenceNumber, P.EvidenceExtra,
P.AkcesAutoID, A.AkcesitPred, A.Akcesit, A.Rok,
P.PocetKusu,
P.NepublikovatYN,
P.Lokalita,
tT.Zkratka Typ,
P.Original,
P.Popis,
P.Poznamka,
P.SystemAutoID,
P.SeriesAutoID,
P.StageAutoID,
P.SubStageAutoID,
P.LithographicUnitAutoID,
P.LithographicSubUnitAutoID,
P.ZoneAutoID,
P.GroupAutoID,
P.OrderAutoID,
P.FamilyAutoID,
P.GenusAutoID,
P.SubGenusAutoID,
P.SpeciesAutoID,
P.SubSpeciesAutoID,
P.ODAutoID,
P.PDAutoID,
P.OriginAutoID,
P.TypAutoID'
if len(@FldLst) > 0 AND charindex('Derived',@WhichFields) > 0
set @FldLst += N','
if charindex('Derived',@WhichFields) > 0
set @FldLst += N'
case when P.EvidenceLetter Is Not Null then P.EvidenceLetter + '' '' + convert(varchar(5),P.EvidenceNumber) + case when P.EvidenceExtra <> '''' then '' ('' + P.EvidenceExtra + '')'' else '''' end else '''' end AS DrEvid,
case AkcesitPred when ''A'' then A.AkcesitPred + ''/'' when ''Br'' then A.AkcesitPred + ''-'' else '''' end + convert(varchar(5),A.Akcesit) + ''/'' + convert(varchar(4),A.Rok) AKC,
CASE WHEN tDF.DepozitarAutoID IS NULL THEN '''' ELSE tDF.DepozitarFormatted END + CASE WHEN UlozisteDocasne IS NULL THEN '''' ELSE Replace(Replace('' {'' + UlozisteDocasne + ''}'',''{{'',''{''),''}}'',''}'') END AS Ulozeni,
tNI.Clovek + '', '' + left(CONVERT(varchar(20), tNI.Datum, 21),10) As Inventarizace,
dbo.fnsTaxonomieFormatted(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS Taxonomie,
dbo.fnsTaxonomie(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS TaxonomiePlain,
dbo.fnsStratigrafie(tSy.[System], tSe.Series, tSt.Stage, tSSt.SubStage, tLU.LithographicUnit, tLSU.LithographicSubUnit, tZo.Zone) AS Stratigrafie'
if len(@FldLst) > 0 AND charindex('Linked',@WhichFields) > 0
set @FldLst += N','
if charindex('Linked',@WhichFields) > 0
set @FldLst += N'
tGe.Genus,
tSGe.SubGenus,
tSp.Species,
tSSp.SubSpecies,
tFa.Family,
tOrd.[Order],
tGr.[Group],
tSy.[System],
tSE.series,
tSt.Stage,
tSSt.SubStage,
tLU.LithographicUnit,
tLSU.LithographicSubUnit,
tZo.Zone'
set @FldLst += N'
FROMPodrobnosti P'
RETURN @FldLst
END
The JOIN clause generator:CREATE FUNCTION [QConstant].[JoinClause] (@ltrfiltr nvarchar(max) = '')
RETURNS nvarchar(max)
AS
BEGIN
-- PrĂprava
DECLARE @JoinClause nvarchar(max)
DECLARE @Ltrfiltr2 nvarchar(max)
set @JoinClause = ''
set @Ltrfiltr2 = N''
-- Apostrofy se musĂ dĂĄt do porĂĄdku.
--set @Ltrfiltr = replace(@Ltrfiltr,'''','')
--set @Ltrfiltr = replace(@Ltrfiltr,',',''',''')
-- VĹĄechny normĂĄlnĂ spojky, kterĂ˝ potrebujeme pro zobrazenĂ zĂĄkladnĂch polĂcek.
if len(@ltrfiltr) > 0
begin
set @Ltrfiltr2 = N'''' + CAST(@Ltrfiltr AS nvarchar(1000)) + N''''
set @JoinClause = N'
INNER JOINdbo.fntStringsToTable (' + @LtrFiltr2 + ') L on L.EvidenceLetter = P.EvidenceLetter'
end
set @JoinClause = @JoinClause + '
INNER JOINdbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID
LEFT JOINvwTableOfDepozitars tDF ON P.DepozitarAutoID = tDF.DepozitarAutoID
LEFT JOINvwNejnovejsiInventarizace tNI ON P.PodrobnostiAutoID = tNI.PodrobnostiAutoID
LEFT JOINTableOfGenuss tGe ON P.GenusAutoID = tGe.GenusAutoID
LEFT JOINTableOfSubGenuss tSGe ON P.SubGenusAutoID = tSGe.SubGenusAutoID
LEFT JOINTableOfSpeciess tSp ON P.SpeciesAutoID = tSp.SpeciesAutoID
LEFT JOINTableOfSubSpeciess tSSp ON P.SubSpeciesAutoID = tSSp.SubSpeciesAutoID
LEFT JOINTableOfFamilys tFa ON P.FamilyAutoID = tFa.FamilyAutoID
LEFT JOINTableOfOrders tOrd ON P.OrderAutoID = tOrd.OrderAutoID
LEFT JOINTableOfGroups tGr ON P.GroupAutoID = tGr.GroupAutoID
LEFT JOINTableOfSystems tSy ON P.SystemAutoID = tSy.SystemAutoID
LEFT JOINTableOfSeriess tSe ON P.SeriesAutoID = tSe.SeriesAutoID
LEFT JOINTableOfStages tSt ON P.StageAutoID = tSt.StageAutoID
LEFT JOINTableOfSubStages tSSt ON P.SubStageAutoID = tSSt.SubStageAutoID
LEFT JOINTableOfLithographicUnits tLU ON P.LithographicUnitAutoID = tLU.LithographicUnitAutoID
LEFT JOINTableOfLithographicSubUnits tLSU ON P.LithographicSubUnitAutoID = tLSU.LithographicSubUnitAutoID
LEFT JOINTableOfZones tZo ON P.ZoneAutoID = tZo.ZoneAutoID
LEFT JOINTableOfTyps tT ON P.TypAutoID = tT.TypAutoID
) PP
'
/*LEFT JOINLinkGenusSpeciesAuthor lGSA ON P.GenusAutoID = lGSA.GenusAutoID AND P.SpeciesAutoID = lGSA.SpeciesAutoID
LEFT JOINLinkedAuthors lA on lGSA.AuthorAutoID = lA.AuthorAutoID
'*/
RETURN @JoinClause
END
The WHERE clause generator:
CREATE FUNCTION [RychlyFiltr].[WhereClause]
(
@AkcesAutoID int,
@GenusAutoID int,
@SpeciesAutoID int,
@Lokalita varchar(4000)
)
RETURNS nvarchar(4000)
AS
BEGIN
declare @WhereClause varchar(4000) = 'WHERE 1=1'
if @AkcesAutoID <> 0
set @WhereClause += ' AND PP.AkcesAutoID = @AkcesAutoID'
if @GenusAutoID = 0 and @SpeciesAutoID = 0 and @Lokalita = ''
set @WhereClause += ' AND GenusAutoID Is Not Null or SpeciesAutoID Is Not Null or Lokalita Is Not Null'
else
Begin
if @GenusAutoID <> 0
set @WhereClause += ' AND PP.GenusAutoID = @GenusAutoID'
if @SpeciesAutoID <> 0
set @WhereClause += ' AND PP.SpeciesAutoID = @SpeciesAutoID'
if @Lokalita <> ''
set @WhereClause += ' AND PP.Lokalita = @Lokalita'
End
RETURN @WhereClause
END
The SORT clause generator:CREATE FUNCTION [QConstant].[SortClause] ()
RETURNS nvarchar(1000)
AS
BEGIN
RETURN char(13) + 'Order By PP.EvidenceLetter, PP.EvidenceNumber, PP.EvidenceExtra'
END
And several stored procedures that call those functions:
CREATE PROCEDURE [RychlyFiltr].[spPodrobnosti]
@What varchar(10),
@AkcesAutoID int = 0,
@LtrFiltr nvarchar(max) = '',
@Cnt int = 0,
@GenusAutoID int = 0,
@SpeciesAutoID int = 0,
@Lokalita nvarchar(4000) = ''
with recompile, execute as owner
AS
BEGIN
declare @FieldsClause nvarchar(max)
declare @JoinClause nvarchar(max)
declare @WhereClause nvarchar(max) = 'WHERE 1=1'
declare @CompleteQuery nvarchar(max)
SET NOCOUNT ON
-- ZĂĄkladnĂ - seznam polĂcek, kterĂ˝ se zobrazĂ v dotazu
if @What = 'MaZaznam'
set @FieldsClause = QConstant.FieldsClause(-1, 'Real')
else if @What = 'Pocet'
set @FieldsClause = QConstant.FieldsClause(-2, 'Real')
else if @What = 'Zaznamy'
set @FieldsClause = QConstant.FieldsClause(@Cnt, 'Real;Derived')
-- Tady mĂĄme vĹĄechno normĂĄlnĂ spojky, kterĂ˝ potrebujeme pro zobrazenĂ zĂĄkladnĂch polĂcek.
set @JoinClause = QConstant.JoinClause(@LtrFiltr)
set @WhereClause = RychlyFiltr.WhereClause (@AkcesAutoID, @GenusAutoID, @SpeciesAutoID, @Lokalita)
-- Tidy up
set @CompleteQuery = @FieldsClause + @JoinClause + @WhereClause
if @What = 'Zaznamy'
set @CompleteQuery += QConstant.SortClause()
EXEC sp_executeSQL @CompleteQuery,
N'@AkcesAutoID int, @LtrFiltr varchar(100), @GenusAutoID int, @SpeciesAutoID int, @Lokalita varchar(1000)',
@AkcesAutoID, @LtrFiltr, @GenusAutoID, @SpeciesAutoID, @Lokalita
END
CREATE PROCEDURE [RychlyFiltr].[spPodrobnosti]
@What varchar(10),
@AkcesAutoID int = 0,
@LtrFiltr nvarchar(max) = '',
@Cnt int = 0,
@GenusAutoID int = 0,
@SpeciesAutoID int = 0,
@Lokalita nvarchar(4000) = ''
with recompile, execute as owner
AS
BEGIN
declare @FieldsClause nvarchar(max)
declare @JoinClause nvarchar(max)
declare @WhereClause nvarchar(max) = 'WHERE 1=1'
declare @CompleteQuery nvarchar(max)
SET NOCOUNT ON
-- ZĂĄkladnĂ - seznam polĂcek, kterĂ˝ se zobrazĂ v dotazu
if @What = 'MaZaznam'
set @FieldsClause = QConstant.FieldsClause(-1, 'Real')
else if @What = 'Pocet'
set @FieldsClause = QConstant.FieldsClause(-2, 'Real')
else if @What = 'Zaznamy'
set @FieldsClause = QConstant.FieldsClause(@Cnt, 'Real;Derived')
-- Tady mĂĄme vĹĄechno normĂĄlnĂ spojky, kterĂ˝ potrebujeme pro zobrazenĂ zĂĄkladnĂch polĂcek.
set @JoinClause = QConstant.JoinClause(@LtrFiltr)
set @WhereClause = RychlyFiltr.WhereClause (@AkcesAutoID, @GenusAutoID, @SpeciesAutoID, @Lokalita)
-- Tidy up
set @CompleteQuery = @FieldsClause + @JoinClause + @WhereClause
if @What = 'Zaznamy'
set @CompleteQuery += QConstant.SortClause()
EXEC sp_executeSQL @CompleteQuery,
N'@AkcesAutoID int, @LtrFiltr varchar(100), @GenusAutoID int, @SpeciesAutoID int, @Lokalita varchar(1000)',
@AkcesAutoID, @LtrFiltr, @GenusAutoID, @SpeciesAutoID, @Lokalita
END
CREATE PROCEDURE [VyberFiltr].[spPodrobnosti]
@What varchar(10),
@AkcesAutoID int = 0,
@LtrFiltr varchar(1000) = '',
@Cnt int = 0,
@PodrobnostiAutoIDList varchar(max)
with recompile, execute as owner
AS
BEGIN
declare @FieldsClause varchar(4000)
declare @JoinClause varchar(4000)
declare @WhereClause varchar(max)
declare @CompleteQuery nvarchar(max)
SET NOCOUNT ON
-- ZĂĄkladnĂ - seznam polĂcek, kterĂ˝ se zobrazĂ v dotazu
if @What = 'MaZaznam'
set @FieldsClause = QConstant.FieldsClause(-1, 'Real')
else if @What = 'Pocet'
set @FieldsClause = QConstant.FieldsClause(-2, 'Real')
else if @What = 'Zaznamy'
set @FieldsClause = QConstant.FieldsClause(@Cnt, 'Real;Derived')
-- Tady mĂĄme vĹĄechno normĂĄlnĂ spojky, kterĂ˝ potrebujeme pro zobrazenĂ zĂĄkladnĂch polĂcek.
set @JoinClause = QConstant.JoinClause(@LtrFiltr)
set @WhereClause = VyberFiltr.WhereClause (@AkcesAutoID, @PodrobnostiAutoIDList)
-- Tidy up
set @CompleteQuery = @FieldsClause + @JoinClause + @WhereClause
if @What = 'Zaznamy'
set @CompleteQuery += QConstant.SortClause()
print @CompleteQuery
EXEC sp_executeSQL @CompleteQuery,
N'@PodrobnostiAutoIDList varchar(max)',
@PodrobnostiAutoIDList
END
August 23, 2013 at 6:18 am
Those are cool. And yeah, I don't blame you. I'd hate to just hit f5 on all that outside of a SP/function.
I call that red letter roulette.
August 23, 2013 at 10:22 am
erikd (8/23/2013)
Those are cool.
Thank you. SQL Server purists may howl, but I'm pretty pleased with it. It took a while to get all the string assemblies working correctly (missing commas, duplicate commas, no spaces...), but since I got the bugs out, it has saved me a tremendous amount of work and frustration. Very simple function calls mean I can add all sorts of new and strange search routines at user's request, with very little effort. And having the entire query assembled into a string before being executed means I can debug with a single print statement.
And yeah, I don't blame you. I'd hate to just hit f5 on all that outside of a SP/function.
I call that red letter roulette.
The potential is there, of course, but since these routines just assemble strings, and are safely isolated inside functions, the possibility for massive damage is pretty minimal. Additionally, these are all strictly data retrieval queries - read only. Updates and inserts are done elsewhere, completely separate from all this.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply