SP_ExecuteSQL
-
Hello
Just when you think you know something, this happens.
I'm trying to improve an SP that uses various if clauses and does a search on a set of customer tables.
Used from a search screen, given a certasin set of options it adds a % to the end of addressline1, postcode, SurName & CLI.
So I changed this to:
declare @cli varchar(50) declare @surname varchar(50) declare @address varchar(50) declare @postcode varchar(50) set @cli = '' set @surname = '' set @address = '' set @postcode = 'test'
declare @ParmDefinition nvarchar(4000) declare @where nvarchar(500) declare @SQLString nvarchar(4000) set @ParmDefinition = N'@String Nvarchar(500)'
set @where = '' if @CLI <> '' set @where = @where + ' c.CLI LIKE ''' + @CLI + '%'' and ' if @Surname <> '' set @where = @where + ' con.SurName LIKE ''' + @Surname + '%'' and ' if @Address <> '' set @where = @where + ' addr.Address1 LIKE ''' + @Address + '%'' and ' if @Postcode <> '' set @where = @where + ' addr.Postcode LIKE ''' + @Postcode + '%'' and ' set @where = @where + ' c.DateArchived IS NULL' Set @SQLString = 'print @String '
Exec sp_ExecuteSQL @SQLString, @ParmDefinition, @where
This works fine, as expected printing out the where clause. But when I change @SQLString to
set @SQLString = 'SELECT DISTINCT TOP 100 a.AccountID, a.AccountNumber, c.CLI, con.SurName, addr.Address1 [Account Address], addr.Postcode as [Account Postcode] FROM account_t a INNER JOIN Customer_T cust on a.customerID = cust.CustomerID INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID LEFT OUTER JOIN CLI_T c ON a.AccountID = c.AccountID WHERE @String ' --+ @where
It stops working, complaining of a syntax error. (Line 12: Incorrect syntax near '@String'.)
I have checked that the @where starts with a space, and if I use the already built @where it works too, but I think this is not optimised?
/* last line becomes */ WHERE ' + @where
Aarghh! Any thoughts?
Dave J
-
You're embedding the literal string @String into your SQL string, so the error is entirely as expected. @String won't be resolved to what it contains if it's inside the single quotes that delimit the entire SQL string.
Change this: WHERE @String '
to this: WHERE ' + @String
-
That's what I want to do, as per an example in the BOL:
DECLARE @IntVariable INT DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string once.*/ SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level' SET @ParmDefinition = N'@level tinyint' /* Execute the string with the first parameter value. */ SET @IntVariable = 35 EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable /* Execute the same string with the second parameter value. */ SET @IntVariable = 32 EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable
As you can see, the string @level get replaced by the parameter @IntVariable,
in my case I want @string to be replaced by @where
It works as expected in the first example...
Set @SQLString = 'print @String '
Dave J
-
The syntax for sp_executesql is for passing parameters to fill into a query, not for passing SQL clauses. You cannot put an entire Where clause in as a parameter. You may want to consider this:
declare @cli varchar(50) declare @surname varchar(50) declare @address varchar(50) declare @postcode varchar(50) set @cli = '' set @surname = '' set @address = '' set @postcode = 'test' declare @ParmDefinition nvarchar(4000) declare @where nvarchar(500) declare @SQLString nvarchar(4000) set @ParmDefinition = N'@String Nvarchar(500)'
set @where = '' set @CLI = @CLI + '%' set @Surname = @Surname + '%' set @Address = @Address + '%' set @Postcode = @Postcode + '%' set @SQLString = 'SELECT DISTINCT TOP 100 a.AccountID, a.AccountNumber, c.CLI, con.SurName, addr.Address1 [Account Address], addr.Postcode as [Account Postcode] FROM account_t a INNER JOIN Customer_T cust on a.customerID = cust.CustomerID INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID LEFT OUTER JOIN CLI_T c ON a.AccountID = c.AccountID WHERE c.CLI LIKE @pCLI and con.SurName LIKE @pSurname and addr.Address1 LIKE @pAddress and addr.Postcode LIKE @pPostcode and c.DateArchived IS NULL '
Exec sp_ExecuteSQL @SQLString, N'@pCLI nvarchar(500), @pSurname nvarchar(500), @pAddress nvarchar(500), @pPostCode nvarchar(500)', @where
Mark -
Mark,
thanks for the response, two points:
1. Your code reproduces the behavior of the original query, but you couldn't know that as I didn't post it
This produces over 4 million logical reads! My solution reduces it to about 12,000. (That is, by adding the @where to the Dynamic SQL), but I might as wellExec @SQLString
negating the advantadge of sp_ExecuteSQL
2. Setting @SQLString to 'Print @String ' works, as in it prints the Dynamically built where clause. Why?
I'm at home now & it's 23:40 in the UK, but when I get to work tomorrow afternoon, (appointment in the morning), I'll post a full script creating tables etc. so you can run it & see what I mean.
Dave J
-
Dave;
>This produces over 4 million logical reads! My solution reduces it to about 12,000.
Certainly if testing shows one way is better than another, go for it. It seems like, no matter how much we try to understand and outsmart the query engine, it still does what it wants. Best way is test the queries, and go with it. I doubt that saving a few milliseconds on compiling the query will outweigh the 300x difference on i/o.
So I see now that you are trying to optimize a query that often has a number of "LIKE '%' " statements by removing the criteria from the where clause. And I agree that, if that the tables are large, that the query will be large, probably since it will likely do full table scans just to get everything. I can only think of two choices: the dynamic query like you essentially do with the "sp_executesql @sqlstring + @swhere" option, or a stored procedure that has each possible option in the where clause. Although, I think even in the stored procedure like that, it will still be a dynamic query and get compiled every time (I think Brian or Steve or Andy wrote an article on that on this site several months ago). Updated: Andy Warren did: http://www.sqlservercentral.com/columnists/awarren/dynamicsqlorstoredprocedure.asp
>...Setting @SQLString to 'Print @String ...
With the "print" string version, you passed one parameter, a string that happens to look like a where clause. But it is not a where clause, because there is no select, no from, etc. It is just a string. The original "problem" is not the string being passed in, it is the compilation of the query needing parameters. But a parameter cannot be the structure of the statement itself. The where clause you were trying to add could not be added after the query is (pre-)compiled, you can only give parameters to values the query will need. Does this help, or did my explanation muck it up even more?
Mark -
Mark, you are spot on. 12,000 is definately better than over 4 million, hang on.. I'll dig out the figures...
--bad Table 'CLI_T'. Scan count 260, logical reads 1927, physical reads 0, read-ahead reads 150. Table 'Account_T'. Scan count 260, logical reads 1960, physical reads 19, read-ahead reads 40. Table 'Contacts_T'. Scan count 260, logical reads 1316, physical reads 17, read-ahead reads 252. Table 'Address_T'. Scan count 1308204, logical reads 4122409, physical reads 0, read-ahead reads 0. Table 'Customer_T'. Scan count 1, logical reads 11993, physical reads 40, read-ahead reads 8.
--good Table 'Address_T'. Scan count 1, logical reads 2198, physical reads 0, read-ahead reads 0. Table 'CLI_T'. Scan count 691, logical reads 4195, physical reads 0, read-ahead reads 0. Table 'Contacts_T'. Scan count 691, logical reads 2096, physical reads 0, read-ahead reads 0. Table 'Account_T'. Scan count 691, logical reads 4148, physical reads 0, read-ahead reads 0. Table 'Customer_T'. Scan count 8, logical reads 11993, physical reads 0, read-ahead reads 0.
In fact, the numbers are even better than I quoted! And again, you're right about the addition of '%' causing table scans, on tables with over a million rows.
I'll stick with
set @SQLString = 'SELECT DISTINCT TOP 100 a.AccountID, a.AccountNumber, c.CLI, con.SurName, addr.Address1 [Account Address], addr.Postcode as [Account Postcode] FROM account_t a INNER JOIN Customer_T cust on a.customerID = cust.CustomerID INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID LEFT OUTER JOIN CLI_T c ON a.AccountID = c.AccountID WHERE ' + @where
as this is what is doing the trick. I was just trying to address Andy's point of 'How can I minimize the performance hit?' as this proc is called hundreds, if not thousands of times a day. Looks like I can't dynamically build the where clause the way I want to, (which is to omit certain columns if the search argument is blank). Again, for clarity, the original appends '%' to all 5 fields returned.
Thanks for the responses, and if anyone can suggest how to dynamically build a where clause & call it with sp_ExecuteSQL, I want to read the article
Dave J
PS That last post DID help my understanding, until next time...
-
Hi,
The below mentioned script might help you to write similar script for generating Where clause Dynamically.
Hope it helps you
.CREATE PROC KTP_WhereClauseForPR
@deptfrom VARCHAR(35)='',
@deptto VARCHAR(35)='',
@traderfrom VARCHAR(15)='',
@traderto VARCHAR(15)='',
@commoditygrpfrom VARCHAR(11)='',
@commoditygrpto VARCHAR(11)='',
@commodityfrom VARCHAR(35)='',
@commodityto VARCHAR(35)='',
@counterpartyfrom VARCHAR(15)='',
@counterpartyto VARCHAR(15)='',
@contractnofrom INT = 0,
@contractsplitfrom SMALLINT = 0,
@contractnoto INT = 0,
@contractsplitto SMALLINT = 0,
@whereclause VARCHAR(2000) OUTPUT
AS
SET NOCOUNT ON
BEGIN
--Department Range
IF @deptfrom <> '' AND @deptto <>'' AND @deptfrom <> @deptto
SET @whereclause = '(TCA20007.DepartmentID >= '''+@deptfrom+''' AND TCA20007.DepartmentID <= '''+@deptto + ''')'
ELSE IF @deptfrom <> '' AND @deptto <>'' AND @deptfrom = @deptto
SET @whereclause ='(TCA20007.DepartmentID = ''' +@deptfrom + ''')'
ELSE
SET @whereclause=''
--Trader Range
IF @traderfrom <> '' AND @traderto <> '' AND @traderfrom <> @traderto
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + '(TCA20007.TraderID >= '''+@traderfrom+''' AND TCA20007.TraderID <= '''+@traderto + ''')'
ELSE @whereclause + 'AND (TCA20007.TraderID >= '''+@traderfrom+''' AND TCA20007.TraderID <= '''+@traderto + ''')'
END
ELSE IF @traderfrom <> '' AND @traderto <> '' AND @traderfrom = @traderto
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + '(TCA20007.TraderID = ''' +@traderfrom+''')'
ELSE @whereclause + 'AND (TCA20007.TraderID = ''' +@traderfrom+''')'
END
ELSE
SET @whereclause=@whereclause + ''
--Commodity Group Range
IF @commoditygrpfrom <> '' AND @commoditygrpto <> '' AND @commoditygrpfrom <> @commoditygrpto
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + ' (TFD20007.CommodityGroup >= '''+@commoditygrpfrom+''' AND TFD20007.CommodityGroup <= '''+@commoditygrpto + ''')'
ELSE @whereclause + ' AND (TFD20007.CommodityGroup >= '''+@commoditygrpfrom+''' AND TFD20007.CommodityGroup <= '''+@commoditygrpto + ''')'
END
ELSE IF @commoditygrpfrom <> '' AND @commoditygrpto <> '' AND @commoditygrpfrom = @commoditygrpto
SET @whereclause = CASE @whereclause WHEN ''
THEN @whereclause + ' (TFD20007.CommodityGroup = ''' +@commoditygrpfrom+''')'
ELSE @whereclause + ' AND (TFD20007.CommodityGroup = ''' +@commoditygrpfrom+''')'
END
ELSE
SET @whereclause=@whereclause + ''
--Commodity Range
IF @commodityfrom <> '' AND @commodityto <> '' AND @commodityfrom <> @commodityto
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + ' (TCA20007.CommodityID >= '''+@commodityfrom+''' AND TCA20007.CommodityID <= '''+@commodityto + ''')'
ELSE @whereclause + ' AND (TCA20007.CommodityID >= '''+@commodityfrom+''' AND TCA20007.CommodityID <= '''+@commodityto + ''')'
END
ELSE IF @commodityfrom <> '' AND @commodityto <> '' AND @commodityfrom = @commodityto
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + ' (TCA20007.CommodityID = ''' +@commodityfrom+''')'
ELSE @whereclause + ' AND (TCA20007.CommodityID = ''' +@commodityfrom+''')'
END
ELSE
SET @whereclause=@whereclause + ''
--Counterparty Range
IF @counterpartyfrom <> '' AND @counterpartyto <> '' AND @counterpartyfrom <> @counterpartyto
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + '(TCA20007.CounterPartyNumber >= '''+@counterpartyfrom+''' AND TCA20007.CounterPartyNumber <= '''+@counterpartyto + ''')'
ELSE @whereclause + 'AND (TCA20007.CounterPartyNumber >= '''+@counterpartyfrom+''' AND TCA20007.CounterPartyNumber <= '''+@counterpartyto + ''')'
END
ELSE IF @counterpartyfrom <> '' AND @counterpartyto <> '' AND @counterpartyfrom = @counterpartyto
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + '(TCA20007.CounterPartyNumber = ''' +@counterpartyfrom+''')'
ELSE @whereclause + 'AND (TCA20007.CounterPartyNumber = ''' +@counterpartyfrom+''')'
END
ELSE
SET @whereclause=@whereclause + ''
--Contract Number and Split Range
IF @contractnofrom > 0 AND @contractnoto > 0 AND (@contractnofrom <> @contractnoto OR @contractsplitfrom <> @contractsplitto)
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + '((RTRIM(LTRIM(STR(TCA20007.ContractNumber))) + REPLICATE(''0'', (3 - LEN(TCA20007.ContractSplit))) + RTRIM(LTRIM(STR(TCA20007.ContractSplit)))) >= '''
+ RTRIM(LTRIM(STR(@contractnofrom))) + REPLICATE('0', (3 - LEN(@contractsplitfrom))) + RTRIM(LTRIM(STR(@contractsplitfrom))) +
''' AND (RTRIM(LTRIM(STR(TCA20007.ContractNumber))) + REPLICATE(''0'', (3 - LEN(TCA20007.ContractSplit))) + RTRIM(LTRIM(STR(TCA20007.ContractSplit)))) <= '''
+ RTRIM(LTRIM(STR(@contractnoto))) + REPLICATE('0', (3 - LEN(@contractsplitto))) + RTRIM(LTRIM(STR(@contractsplitto))) + ''')'
ELSE @whereclause + ' AND ((RTRIM(LTRIM(STR(TCA20007.ContractNumber))) + REPLICATE(''0'', (3 - LEN(TCA20007.ContractSplit))) + RTRIM(LTRIM(STR(TCA20007.ContractSplit)))) >= '''
+ RTRIM(LTRIM(STR(@contractnofrom))) + REPLICATE('0', (3 - LEN(@contractsplitfrom))) + RTRIM(LTRIM(STR(@contractsplitfrom))) +
''' AND (RTRIM(LTRIM(STR(TCA20007.ContractNumber))) + REPLICATE(''0'', (3 - LEN(TCA20007.ContractSplit))) + RTRIM(LTRIM(STR(TCA20007.ContractSplit)))) <= '''
+ RTRIM(LTRIM(STR(@contractnoto))) + REPLICATE('0', (3 - LEN(@contractsplitto))) + RTRIM(LTRIM(STR(@contractsplitto))) + ''')'
END
ELSE IF @contractnofrom > 0 AND @contractnoto > 0 AND (@contractnofrom = @contractnoto AND @contractsplitfrom = @contractsplitto)
SET @whereclause = CASE @whereclause
WHEN '' THEN @whereclause + '(ContractNumber = ' + CAST(@contractnofrom AS VARCHAR(8)) + ' AND ContractSplit = '+ CAST(@contractsplitto AS VARCHAR(3)) + ')'
ELSE @whereclause + 'AND (ContractNumber = ' + CAST(@contractnofrom AS VARCHAR(8)) +' AND ContractSplit = '+ CAST(@contractsplitto AS VARCHAR(3)) + ')'
END
ELSE
SET @whereclause=@whereclause + ''
--Select @whereclause whereclause
END
GO
*************
Calling the above created proc as:
EXEC KTP_WhereClauseForPR @deptfrom, @deptto,
@traderfrom, @traderto,
@commoditygrpfrom, @commoditygrpto,
@commodityfrom, @commodityto,
@counterpartyfrom, @counterpartyto,
@contractnofrom, @contractsplitfrom,
@contractnoto, @contractsplitto, @whereclause OUT;
Thanks,
Subhash
-
I'vs actually got this going, but it is worse!
Trying to paste HTML Code ... will it work?
This one works, in that it build dynamically & calls it with sp_executesql, but is slower than the one below!
declare @cli varchar(50) declare @surname varchar(50) declare @address varchar(50) declare @postcode varchar(50) set @cli = '' set @surname = '' set @address = '' set @postcode = 'sk13' declare @ParmDefinition nvarchar(4000) declare @where nvarchar(500) declare @SQLString nvarchar(4000) set @ParmDefinition = N'@sCLI Nvarchar(50), @sSurname Nvarchar(50), @sAddress Nvarchar(50), @sPostcode Nvarchar(50)' set @where = '' if @CLI <> '' begin set @where = @where + ' c.CLI LIKE @sCLI + N''%'' and ' end if @Surname <> '' begin set @where = @where + ' con.SurName LIKE @sSurname + N''%'' and ' end if @Address <> '' begin set @where = @where + ' addr.Address1 LIKE @sAddress + N''%'' and ' end if @Postcode <> '' begin set @where = @where + ' addr.Postcode LIKE @sPostcode + N''%'' and ' end set @where = @where + ' c.DateArchived IS NULL' set @SQLString = 'SELECT DISTINCT TOP 100 a.AccountID, a.AccountNumber, c.CLI, con.SurName, addr.Address1 [Account Address], addr.Postcode as [Account Postcode] FROM account_t a INNER JOIN Customer_T cust on a.customerID = cust.CustomerID INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID LEFT OUTER JOIN CLI_T c ON a.AccountID = c.AccountID WHERE ' + @where Exec sp_ExecuteSQL @SQLString, @ParmDefinition, @CLI, @Surname, @Address, @Postcode /* Table 'CLI_T'. Scan count 165, logical reads 879, physical reads 0, read-ahead reads 0. Table 'Contacts_T'. Scan count 240, logical reads 787, physical reads 0, read-ahead reads 0. Table 'Address_T'. Scan count 611844, logical reads 1928061, physical reads 0, read-ahead reads 0. Table 'Customer_T'. Scan count 611919, logical reads 1917897, physical reads 0, read-ahead reads 0. Table 'Account_T'. Scan count 1, logical reads 22899, physical reads 0, read-ahead reads 0. */ ---------------------------------------------------- declare @cli varchar(50) declare @surname varchar(50) declare @address varchar(50) declare @postcode varchar(50) set @cli = '' set @surname = '' set @address = '' set @postcode = 'sk13' declare @ParmDefinition nvarchar(4000) declare @where nvarchar(500) declare @SQLString nvarchar(4000) set @ParmDefinition = N'@String Nvarchar(500)' set @where = '' if @CLI <> '' set @where = @where + ' c.CLI LIKE ''' + @CLI + '%'' and ' if @Surname <> '' set @where = @where + ' con.SurName LIKE ''' + @Surname + '%'' and ' if @Address <> '' set @where = @where + ' addr.Address1 LIKE ''' + @Address + '%'' and ' if @Postcode <> '' set @where = @where + ' addr.Postcode LIKE ''' + @Postcode + '%'' and ' set @where = @where + ' c.DateArchived IS NULL' set @SQLString = 'SELECT DISTINCT TOP 100 a.AccountID, a.AccountNumber, c.CLI, con.SurName, addr.Address1 [Account Address], addr.Postcode as [Account Postcode] FROM account_t a INNER JOIN Customer_T cust on a.customerID = cust.CustomerID INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID LEFT OUTER JOIN CLI_T c ON a.AccountID = c.AccountID WHERE '+ @where Exec sp_ExecuteSQL @SQLString, @ParmDefinition, @where /* Table 'Address_T'. Scan count 1, logical reads 2201, physical reads 0, read-ahead reads 0. Table 'CLI_T'. Scan count 691, logical reads 4195, physical reads 0, read-ahead reads 0. Table 'Account_T'. Scan count 691, logical reads 4148, physical reads 0, read-ahead reads 0. Table 'Contacts_T'. Scan count 691, logical reads 2096, physical reads 0, read-ahead reads 0. Table 'Customer_T'. Scan count 25, logical reads 12043, physical reads 0, read-ahead reads 0. */ -
The first version: I'm a little surprised it would work at all. You are only putting in parts of the where clause if the parameter would have a value (e.g. if @CLI <> '' begin set @where = @where + ' c.CLI LIKE @sCLI + N''%'' and ' end), but you have the (potentially unused) parameter in the @ParmDefinition and in the sp_ExecuteSQL. I guess the parser would think it was ok that you define a parameter, give it a value, but do not use it in the query itself.
I think the second version would be best. You can (should?) eliminate that @ParmDefinition and parameter, and just have: "Exec sp_ExecuteSQL @SQLString", as you already have the complete where clause in the @SQLString.
Mark -
Last post, as the numbers have stopped making sense! Definately going with the second one
Dave J
--Original declare @cli varchar(50) declare @surname varchar(50) declare @address varchar(50) declare @postcode varchar(50) set @cli = '' set @surname = '' set @address = '' set @postcode = 'sk13' SELECT DISTINCT TOP 100 a.AccountID, a.AccountNumber, c.CLI, con.SurName, addr.Address1 [Account Address], addr.Postcode as [Account Postcode] FROM account_t a INNER JOIN customer_t cust on a.customerID = cust.CustomerID INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID LEFT OUTER JOINCLI_T c ON a.AccountID = c.AccountID WHERE (c.CLI LIKE @CLI + '%') AND (con.SurName LIKE @Surname + '%') AND (addr.Address1 LIKE @Address + '%') AND (REPLACE(addr.Postcode, ' ', '') LIKE @Postcode + '%') AND c.DateArchived IS NULL /* cost = 8.74 ran in 40 secs Table 'CLI_T'. Scan count 172, logical reads 855, physical reads 7, read-ahead reads 0. Table 'Account_T'. Scan count 247, logical reads 1363, physical reads 9, read-ahead reads 0. Table 'Address_T'. Scan count 580542, logical reads 1829685, physical reads 0, read-ahead reads 0. Table 'Customer_T'. Scan count 1, logical reads 11970, physical reads 0, read-ahead reads 0. Table 'Contacts_T'. Scan count 1, logical reads 8656, physical reads 9, read-ahead reads 8733. */ ---------------------------------------------------- declare @cli varchar(50) declare @surname varchar(50) declare @address varchar(50) declare @postcode varchar(50) set @cli = '' set @surname = '' set @address = '' set @postcode = 'sk13' declare @where nvarchar(500) declare @SQLString nvarchar(4000) set @where = '' if @CLI <> '' set @where = @where + ' c.CLI LIKE ''' + @CLI + '%'' and ' if @Surname <> '' set @where = @where + ' con.SurName LIKE ''' + @Surname + '%'' and ' if @Address <> '' set @where = @where + ' addr.Address1 LIKE ''' + @Address + '%'' and ' if @Postcode <> '' set @where = @where + ' addr.Postcode LIKE ''' + @Postcode + '%'' and ' set @where = @where + ' c.DateArchived IS NULL' set @SQLString = 'SELECT DISTINCT TOP 100 a.AccountID, a.AccountNumber, c.CLI, con.SurName, addr.Address1 [Account Address], addr.Postcode as [Account Postcode] FROM account_t a INNER JOIN Customer_T cust on a.customerID = cust.CustomerID INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID LEFT OUTER JOIN CLI_T c ON a.AccountID = c.AccountID WHERE '+ @where Exec sp_ExecuteSQL @SQLString /* cost = 11.5 ran in 1 second Table 'Address_T'. Scan count 1, logical reads 2201, physical reads 0, read-ahead reads 0. Table 'CLI_T'. Scan count 691, logical reads 4195, physical reads 0, read-ahead reads 0. Table 'Account_T'. Scan count 691, logical reads 4148, physical reads 0, read-ahead reads 0. Table 'Contacts_T'. Scan count 691, logical reads 2096, physical reads 0, read-ahead reads 0. Table 'Customer_T'. Scan count 25, logical reads 12043, physical reads 0, read-ahead reads 0. */
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply