|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sp question re: dynamic sqlCREATE PROCEDURE dbo.usp_GetSelectTempTasksUser @intUserId int AS BEGIN Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, dbo.tblTempTasks.varCompContactFirstName, dbo.tblTempTasks.varCompContactLastName, dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, dbo.tblTempTasks.dtTaskTime, dbo.tblCommunicationType.varCommunicationTypeName, dbo.tblCompanyStatus.numCompanyStatusId, dbo.tblCompany.varCompanyName, dbo.tblCompanyFranchise.numProgramId, dbo.tblTempTasks.varProgramName FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON dbo.tblTempTasks.numCommunicationType = dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany ON dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON dbo.tblCompanyFranchise.numCompanyStatusId = dbo.tblCompanyStatus.numCompanyStatusId WHERE (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10)) SELECT @SQL = @SQL + ' )) tab and intTStatus = 0 order by convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), varProgramName' Exec ( @SQL) END GO Is this considered to be dynamic sql because it uses "EXEC"? If so, does this mean that there is no query plan reuse whereas if the query was written without dynamic sql, there would be query plan reuse? Thanks, -- Dan D. yes, its dynamic SQL and there will be no plan reuse.
Just a suggestion,Instead of exec use sp_executesql, the plan might be reused at times. Hope this helps. You do get plan reuse with EXEC and any other statements.
sp_executesql is a better choice because you can better parameterise the query and not rely on SQL Server auto-parameterising it. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:86223E0C-8831-4ABA-86A8-0F3B2D97AD8F@microsoft.com... > yes, its dynamic SQL and there will be no plan reuse. > Just a suggestion,Instead of exec use sp_executesql, the plan might be > reused at times. Hope this helps. My belief was that auto-parameterization was done only for simple SQL
Statements, for which (i believe) we don't need a cache of the execution plan :) So, I still want to believe that there is no such as auto-parameterization and I cannot rely on it when I write my queries. Am I right? Thanks a lot for that pointer, nevertheless. Regards Omnibuzz Show quote "Tony Rogerson" wrote: > You do get plan reuse with EXEC and any other statements. > > sp_executesql is a better choice because you can better parameterise the > query and not rely on SQL Server auto-parameterising it. > > -- > Tony Rogerson > SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL > Server Consultant > http://sqlserverfaq.com - free video tutorials > > > "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message > news:86223E0C-8831-4ABA-86A8-0F3B2D97AD8F@microsoft.com... > > yes, its dynamic SQL and there will be no plan reuse. > > Just a suggestion,Instead of exec use sp_executesql, the plan might be > > reused at times. Hope this helps. > > > Why do you even need dynamic SQL here, wouldn't this work??
CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser @intUserId int AS BEGIN SELECT TOP 100 * FROM (SELECT dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, dbo.tblTempTasks.varCompContactFirstName, dbo.tblTempTasks.varCompContactLastName, dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, dbo.tblTempTasks.dtTaskTime, dbo.tblCommunicationType.varCommunicationTypeName, dbo.tblCompanyStatus.numCompanyStatusId, dbo.tblCompany.varCompanyName, dbo.tblCompanyFranchise.numProgramId, dbo.tblTempTasks.varProgramName FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON dbo.tblTempTasks.numCommunicationType = dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany ON dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON dbo.tblCompanyFranchise.numCompanyStatusId = dbo.tblCompanyStatus.numCompanyStatusId WHERE (dbo.tblTempTasks.numUserId = CAST(@intuserid AS varchar(10)) )) tab and intTStatus = 0 order by convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), varProgramName END GO Denis the SQL Menace http://sqlservercode.blogspot.com/ Well, I'm not sure why this is dynamic SQLin the first place. Also, you
should learn to use aliases, they make code much easier to follow. And I am not sure of the point of the outer query and sub-select? CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser @UserID INT AS BEGIN SET NOCOUNT ON; SELECT TOP 100 t.NumCompanyID, t.NumContactID, /* ... fill in the rest of the tblTempTasks columns here ... */ ct.varCommunicationTypeName, cs.numCompanyStatusId, c.varCompanyName, cf.numProgramId FROM dbo.tblTempTasks t INNER JOIN dbo.tblCommunicationType ct ON t.numCommunicationType = ct.numCommunicationTypeId INNER JOIN dbo.tblCompany c ON t.numCompanyId = c.numCompanyId INNER JOIN dbo.tblCompanyFranchise cf ON c.numCompanyId = cf.numCompanyId INNER JOIN dbo.tblCompanyStatus cs ON cf.numCompanyStatusId = cs.numCompanyStatusId WHERE t.numUserId = @UserID AND t.intTStatus = 0 ORDER BY t.dtTaskDate, t.dtTaskTime, t.varProgramName; END GO Show quote "Dan D." <D***@discussions.microsoft.com> wrote in message dbo.tblCompany.varCompanyName,news:61689F5E-3169-4E72-9A24-373E61236DDC@microsoft.com... > Using SS2000 SP4. I have a question about the following code. > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > @intUserId int > AS > BEGIN > Declare @SQL VarChar(1000) > SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT > dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, > dbo.tblTempTasks.varCompContactFirstName, > dbo.tblTempTasks.varCompContactLastName, > dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, > dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, > dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, > dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, > dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, > dbo.tblTempTasks.dtTaskTime, > dbo.tblCommunicationType.varCommunicationTypeName, > dbo.tblCompanyStatus.numCompanyStatusId, > dbo.tblCompanyFranchise.numProgramId, dbo.tblTempTasks.varProgramNameShow quote > FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON > dbo.tblTempTasks.numCommunicationType = > dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany > ON > dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN > dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = > dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON > dbo.tblCompanyFranchise.numCompanyStatusId = > dbo.tblCompanyStatus.numCompanyStatusId WHERE > (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10)) > SELECT @SQL = @SQL + ' )) tab and intTStatus = 0 order by > convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), > varProgramName' > Exec ( @SQL) > END > GO > > Is this considered to be dynamic sql because it uses "EXEC"? If so, does > this mean that there is no query plan reuse whereas if the query was > written > without dynamic sql, there would be query plan reuse? > > Thanks, > -- > Dan D. I hadn't thought of getting rid of the outer query and sub select. I was so
focused on the dynamic sql part. Thanks for the suggestion. I'll test it. What is the "tab" for in "WHERE (dbo.tblTempTasks.numUserId = CAST(@intuserid AS varchar(10)) )) tab"? Thanks, -- Show quoteDan D. "Aaron Bertrand [SQL Server MVP]" wrote: > Well, I'm not sure why this is dynamic SQLin the first place. Also, you > should learn to use aliases, they make code much easier to follow. And I am > not sure of the point of the outer query and sub-select? > > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > @UserID INT > AS > BEGIN > SET NOCOUNT ON; > > SELECT TOP 100 > t.NumCompanyID, > t.NumContactID, > /* ... fill in the rest of the tblTempTasks columns here ... */ > ct.varCommunicationTypeName, > cs.numCompanyStatusId, > c.varCompanyName, > cf.numProgramId > FROM > dbo.tblTempTasks t > INNER JOIN dbo.tblCommunicationType ct > ON t.numCommunicationType = ct.numCommunicationTypeId > INNER JOIN dbo.tblCompany c > ON t.numCompanyId = c.numCompanyId > INNER JOIN dbo.tblCompanyFranchise cf > ON c.numCompanyId = cf.numCompanyId > INNER JOIN dbo.tblCompanyStatus cs > ON cf.numCompanyStatusId = cs.numCompanyStatusId > WHERE > t.numUserId = @UserID > AND t.intTStatus = 0 > ORDER BY > t.dtTaskDate, > t.dtTaskTime, > t.varProgramName; > END > GO > > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > news:61689F5E-3169-4E72-9A24-373E61236DDC@microsoft.com... > > Using SS2000 SP4. I have a question about the following code. > > > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > > @intUserId int > > AS > > BEGIN > > Declare @SQL VarChar(1000) > > SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT > > dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, > > dbo.tblTempTasks.varCompContactFirstName, > > dbo.tblTempTasks.varCompContactLastName, > > dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, > > dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, > > dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, > > dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, > > dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, > > dbo.tblTempTasks.dtTaskTime, > > dbo.tblCommunicationType.varCommunicationTypeName, > > dbo.tblCompanyStatus.numCompanyStatusId, > dbo.tblCompany.varCompanyName, > > dbo.tblCompanyFranchise.numProgramId, > dbo.tblTempTasks.varProgramName > > FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON > > dbo.tblTempTasks.numCommunicationType = > > dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany > > ON > > dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN > > dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = > > dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON > > dbo.tblCompanyFranchise.numCompanyStatusId = > > dbo.tblCompanyStatus.numCompanyStatusId WHERE > > (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10)) > > SELECT @SQL = @SQL + ' )) tab and intTStatus = 0 order by > > convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), > > varProgramName' > > Exec ( @SQL) > > END > > GO > > > > Is this considered to be dynamic sql because it uses "EXEC"? If so, does > > this mean that there is no query plan reuse whereas if the query was > > written > > without dynamic sql, there would be query plan reuse? > > > > Thanks, > > -- > > Dan D. > > > tab is used as an alias
take a look at these 2 examples select * from (select * from pubs..authors) --error select * from (select * from pubs..authors) t -- is fine, t could be any name Denis the SQL Menace http://sqlservercode.blogspot.com/ > What is the "tab" for in "WHERE I guess you inherited the code. When you have a sub-select you need to give > (dbo.tblTempTasks.numUserId = CAST(@intuserid AS varchar(10)) > )) tab"? the inner query a name (like a table alias). I did inherit the code. I was first trying to go through and make sure they
weren't using dynamic sql anywhere that is wasn't needed. Then I was going to test the queries and any alternatives I could come up with. Thanks, -- Show quoteDan D. "Aaron Bertrand [SQL Server MVP]" wrote: > > What is the "tab" for in "WHERE > > (dbo.tblTempTasks.numUserId = CAST(@intuserid AS varchar(10)) > > )) tab"? > > I guess you inherited the code. When you have a sub-select you need to give > the inner query a name (like a table alias). > > > I've always read that for performance reasons it is best to use fully
qualified objects. Does using aliases hurt performance in any way? Thanks, -- Show quoteDan D. "Aaron Bertrand [SQL Server MVP]" wrote: > Well, I'm not sure why this is dynamic SQLin the first place. Also, you > should learn to use aliases, they make code much easier to follow. And I am > not sure of the point of the outer query and sub-select? > > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > @UserID INT > AS > BEGIN > SET NOCOUNT ON; > > SELECT TOP 100 > t.NumCompanyID, > t.NumContactID, > /* ... fill in the rest of the tblTempTasks columns here ... */ > ct.varCommunicationTypeName, > cs.numCompanyStatusId, > c.varCompanyName, > cf.numProgramId > FROM > dbo.tblTempTasks t > INNER JOIN dbo.tblCommunicationType ct > ON t.numCommunicationType = ct.numCommunicationTypeId > INNER JOIN dbo.tblCompany c > ON t.numCompanyId = c.numCompanyId > INNER JOIN dbo.tblCompanyFranchise cf > ON c.numCompanyId = cf.numCompanyId > INNER JOIN dbo.tblCompanyStatus cs > ON cf.numCompanyStatusId = cs.numCompanyStatusId > WHERE > t.numUserId = @UserID > AND t.intTStatus = 0 > ORDER BY > t.dtTaskDate, > t.dtTaskTime, > t.varProgramName; > END > GO > > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > news:61689F5E-3169-4E72-9A24-373E61236DDC@microsoft.com... > > Using SS2000 SP4. I have a question about the following code. > > > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > > @intUserId int > > AS > > BEGIN > > Declare @SQL VarChar(1000) > > SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT > > dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, > > dbo.tblTempTasks.varCompContactFirstName, > > dbo.tblTempTasks.varCompContactLastName, > > dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, > > dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, > > dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, > > dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, > > dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, > > dbo.tblTempTasks.dtTaskTime, > > dbo.tblCommunicationType.varCommunicationTypeName, > > dbo.tblCompanyStatus.numCompanyStatusId, > dbo.tblCompany.varCompanyName, > > dbo.tblCompanyFranchise.numProgramId, > dbo.tblTempTasks.varProgramName > > FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON > > dbo.tblTempTasks.numCommunicationType = > > dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany > > ON > > dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN > > dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = > > dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON > > dbo.tblCompanyFranchise.numCompanyStatusId = > > dbo.tblCompanyStatus.numCompanyStatusId WHERE > > (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10)) > > SELECT @SQL = @SQL + ' )) tab and intTStatus = 0 order by > > convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), > > varProgramName' > > Exec ( @SQL) > > END > > GO > > > > Is this considered to be dynamic sql because it uses "EXEC"? If so, does > > this mean that there is no query plan reuse whereas if the query was > > written > > without dynamic sql, there would be query plan reuse? > > > > Thanks, > > -- > > Dan D. > > > Dan D. (D***@discussions.microsoft.com) writes:
> I've always read that for performance reasons it is best to use fully No. You qualify objects in the FROM and JOIN clauses. When you refer to> qualified objects. Does using aliases hurt performance in any way? a column you should always use a prefix to say from where the column is coming. This prefix can be the table name, or an alias. (And if there is an alias, you should use the alias.) Like Aaron, I strongly prefer aliases, because in a query like the one in your original post, I feel that it is difficult to see the forest for all the trees. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx What is the significance of them using cast and convert in the order by
clause and you didn't? Also, the numUserId is an int datatype in the table. Any idea why they would cast it as a varchar in the sp? "WHERE (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10))" Thanks, -- Show quoteDan D. "Aaron Bertrand [SQL Server MVP]" wrote: > Well, I'm not sure why this is dynamic SQLin the first place. Also, you > should learn to use aliases, they make code much easier to follow. And I am > not sure of the point of the outer query and sub-select? > > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > @UserID INT > AS > BEGIN > SET NOCOUNT ON; > > SELECT TOP 100 > t.NumCompanyID, > t.NumContactID, > /* ... fill in the rest of the tblTempTasks columns here ... */ > ct.varCommunicationTypeName, > cs.numCompanyStatusId, > c.varCompanyName, > cf.numProgramId > FROM > dbo.tblTempTasks t > INNER JOIN dbo.tblCommunicationType ct > ON t.numCommunicationType = ct.numCommunicationTypeId > INNER JOIN dbo.tblCompany c > ON t.numCompanyId = c.numCompanyId > INNER JOIN dbo.tblCompanyFranchise cf > ON c.numCompanyId = cf.numCompanyId > INNER JOIN dbo.tblCompanyStatus cs > ON cf.numCompanyStatusId = cs.numCompanyStatusId > WHERE > t.numUserId = @UserID > AND t.intTStatus = 0 > ORDER BY > t.dtTaskDate, > t.dtTaskTime, > t.varProgramName; > END > GO > > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > news:61689F5E-3169-4E72-9A24-373E61236DDC@microsoft.com... > > Using SS2000 SP4. I have a question about the following code. > > > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > > @intUserId int > > AS > > BEGIN > > Declare @SQL VarChar(1000) > > SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT > > dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, > > dbo.tblTempTasks.varCompContactFirstName, > > dbo.tblTempTasks.varCompContactLastName, > > dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, > > dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, > > dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, > > dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, > > dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, > > dbo.tblTempTasks.dtTaskTime, > > dbo.tblCommunicationType.varCommunicationTypeName, > > dbo.tblCompanyStatus.numCompanyStatusId, > dbo.tblCompany.varCompanyName, > > dbo.tblCompanyFranchise.numProgramId, > dbo.tblTempTasks.varProgramName > > FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON > > dbo.tblTempTasks.numCommunicationType = > > dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany > > ON > > dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN > > dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = > > dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON > > dbo.tblCompanyFranchise.numCompanyStatusId = > > dbo.tblCompanyStatus.numCompanyStatusId WHERE > > (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10)) > > SELECT @SQL = @SQL + ' )) tab and intTStatus = 0 order by > > convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), > > varProgramName' > > Exec ( @SQL) > > END > > GO > > > > Is this considered to be dynamic sql because it uses "EXEC"? If so, does > > this mean that there is no query plan reuse whereas if the query was > > written > > without dynamic sql, there would be query plan reuse? > > > > Thanks, > > -- > > Dan D. > > > > What is the significance of them using cast and convert in the order by They didn't need to. They were basically saying, order this by date, then > clause and you didn't? order by time. The convert was completely unnecessary (unless, of course, the Date includes time and the time includes an unrelated date). > Also, the numUserId is an int datatype in the table. Any idea why they Have you ever tried concatenating a string and an int?> would > cast it as a varchar in the sp? > "WHERE (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10))" DECLARE @i INT; SET @i = 5; SELECT 'foo' + CONVERT(VARCHAR(10), @i); SELECT 'foo' + @i; -- <-- error here In SQL Server 2000 and 2005 we have auto-parameterisation and singleton
statements are cached and can be re-used. To answer your re-use question then yes, its likely that the plan will get re-used, depends on how you are executing it and what else is going on on the server. And yes, its dynamic because of the EXEC. Tony. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Dan D." <D***@discussions.microsoft.com> wrote in message news:61689F5E-3169-4E72-9A24-373E61236DDC@microsoft.com... > Using SS2000 SP4. I have a question about the following code. > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > @intUserId int > AS > BEGIN > Declare @SQL VarChar(1000) > SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT > dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, > dbo.tblTempTasks.varCompContactFirstName, > dbo.tblTempTasks.varCompContactLastName, > dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, > dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, > dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, > dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, > dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, > dbo.tblTempTasks.dtTaskTime, > dbo.tblCommunicationType.varCommunicationTypeName, > dbo.tblCompanyStatus.numCompanyStatusId, dbo.tblCompany.varCompanyName, > dbo.tblCompanyFranchise.numProgramId, dbo.tblTempTasks.varProgramName > FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON > dbo.tblTempTasks.numCommunicationType = > dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany > ON > dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN > dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = > dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON > dbo.tblCompanyFranchise.numCompanyStatusId = > dbo.tblCompanyStatus.numCompanyStatusId WHERE > (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10)) > SELECT @SQL = @SQL + ' )) tab and intTStatus = 0 order by > convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), > varProgramName' > Exec ( @SQL) > END > GO > > Is this considered to be dynamic sql because it uses "EXEC"? If so, does > this mean that there is no query plan reuse whereas if the query was > written > without dynamic sql, there would be query plan reuse? > > Thanks, > -- > Dan D. Is it likely to be reused because the value being passed in is an int?
Whereas, if it was a string, then the plan wouldn't be reused? Thanks, -- Show quoteDan D. "Tony Rogerson" wrote: > In SQL Server 2000 and 2005 we have auto-parameterisation and singleton > statements are cached and can be re-used. > > To answer your re-use question then yes, its likely that the plan will get > re-used, depends on how you are executing it and what else is going on on > the server. > > And yes, its dynamic because of the EXEC. > > Tony. > > -- > Tony Rogerson > SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL > Server Consultant > http://sqlserverfaq.com - free video tutorials > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > news:61689F5E-3169-4E72-9A24-373E61236DDC@microsoft.com... > > Using SS2000 SP4. I have a question about the following code. > > > > CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser > > @intUserId int > > AS > > BEGIN > > Declare @SQL VarChar(1000) > > SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT > > dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, > > dbo.tblTempTasks.varCompContactFirstName, > > dbo.tblTempTasks.varCompContactLastName, > > dbo.tblTempTasks.varCompanyStatusName, dbo.tblTempTasks.dtTaskDate, > > dbo.tblTempTasks.Contact, dbo.tblTempTasks.numUserId, > > dbo.tblTempTasks.bitComPrimaryContact, dbo.tblTempTasks.intTStatus, > > dbo.tblTempTasks.dtCreationDate, dbo.tblTempTasks.numTaskDetailsId, > > dbo.tblTempTasks.numTaskId, dbo.tblTempTasks.numCommunicationType, > > dbo.tblTempTasks.dtTaskTime, > > dbo.tblCommunicationType.varCommunicationTypeName, > > dbo.tblCompanyStatus.numCompanyStatusId, dbo.tblCompany.varCompanyName, > > dbo.tblCompanyFranchise.numProgramId, dbo.tblTempTasks.varProgramName > > FROM dbo.tblTempTasks INNER JOIN dbo.tblCommunicationType ON > > dbo.tblTempTasks.numCommunicationType = > > dbo.tblCommunicationType.numCommunicationTypeId INNER JOIN dbo.tblCompany > > ON > > dbo.tblTempTasks.numCompanyId = dbo.tblCompany.numCompanyId INNER JOIN > > dbo.tblCompanyFranchise ON dbo.tblCompany.numCompanyId = > > dbo.tblCompanyFranchise.numCompanyId INNER JOIN dbo.tblCompanyStatus ON > > dbo.tblCompanyFranchise.numCompanyStatusId = > > dbo.tblCompanyStatus.numCompanyStatusId WHERE > > (dbo.tblTempTasks.numUserId = ' + CAST(@intuserid AS varchar(10)) > > SELECT @SQL = @SQL + ' )) tab and intTStatus = 0 order by > > convert(char(12),dtTaskDate,3), CONVERT(CHAR(8),dtTaskTime,8), > > varProgramName' > > Exec ( @SQL) > > END > > GO > > > > Is this considered to be dynamic sql because it uses "EXEC"? If so, does > > this mean that there is no query plan reuse whereas if the query was > > written > > without dynamic sql, there would be query plan reuse? > > > > Thanks, > > -- > > Dan D. > > > Dan D. (D***@discussions.microsoft.com) writes:
> Is it likely to be reused because the value being passed in is an int? That does not have much to do with it.> Whereas, if it was a string, then the plan wouldn't be reused? The topic of plan reuse of ad hoc statements is somewhat complex. I have discussion of this in the section "Caching Query Plans" in my article on dynamic SQL. See http://www.sommarskog.se/dynamic_sql.html#queryplans. The gist is that with sp_executesql and fully qualified table names, the odds for plan reuse are a lot better. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx "Is it likely to be reused because the value being passed in is an int?
Whereas, if it was a string, then the plan wouldn't be reused? That does not have much to do with it." I thought I remembered in one of your articles that if using "exec" even an additional or missing space could cause an execution plan not to be reused? Is that not true? Thanks, -- Show quoteDan D. "Erland Sommarskog" wrote: > Dan D. (D***@discussions.microsoft.com) writes: > > Is it likely to be reused because the value being passed in is an int? > > Whereas, if it was a string, then the plan wouldn't be reused? > > That does not have much to do with it. > > The topic of plan reuse of ad hoc statements is somewhat complex. I have > discussion of this in the section "Caching Query Plans" in my article > on dynamic SQL. See http://www.sommarskog.se/dynamic_sql.html#queryplans. > > The gist is that with sp_executesql and fully qualified table names, > the odds for plan reuse are a lot better. > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Dan D. (D***@discussions.microsoft.com) writes:
> "Is it likely to be reused because the value being passed in is an int? That is very correct. But that does not have much to do whether parameters> Whereas, if it was a string, then the plan wouldn't be reused? > That does not have much to do with it." > > I thought I remembered in one of your articles that if using "exec" even > an additional or missing space could cause an execution plan not to be > reused? > Is that not true? are passed as strings on ints. Or have I misunderstood your question? Could you clarify? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I see what you mean. It could be 8 or 888. Thanks,
-- Show quoteDan D. "Erland Sommarskog" wrote: > Dan D. (D***@discussions.microsoft.com) writes: > > "Is it likely to be reused because the value being passed in is an int? > > Whereas, if it was a string, then the plan wouldn't be reused? > > That does not have much to do with it." > > > > I thought I remembered in one of your articles that if using "exec" even > > an additional or missing space could cause an execution plan not to be > > reused? > > Is that not true? > > That is very correct. But that does not have much to do whether parameters > are passed as strings on ints. Or have I misunderstood your question? > Could you clarify? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >  |
|||||||||||||||||||||||
Other interesting topics