InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:29:09 GMT20http://www.sqlservercentral.com/Forums/Topic637670-149-1.aspxNew EXECUTE statement in SQL Server Denali. See wxample on [url=http://www.sqlsuperfast.com]www.sqlsuperfast.com[/url]Sun, 06 Mar 2011 05:06:17 GMTa.rajmanehttp://www.sqlservercentral.com/Forums/Topic637670-149-1.aspx[quote][b]rferguson (1/18/2009)[/b][hr]Hi Grant, the stored procedure already uses the sp_executesql function. What I'm essentially trying to acheive is to create a dynamic SQL statement, but in which the WHERE clause is different in each case and also uses variables for the date ranges. The sticking point for me is to sending the variable dates to the stored procedure within the "text" of the dynamic SQL statement.Starting to find ow this becomes increasingly difficult without adding a second line of code to set these variables first as EXECUTE won't allow expressions.Richard.[/quote]But using sp_executesql you can also pass it variables. You simply write the query, dynamic or not, like this:[code]DECLARE @sql NVARCHAR(MAX), @paramdef NVARCHAR(MAX)SET @sql = 'SELECT * FROM MyTable WHERE Date BETWEEN @Var1 and @Var2'SET @paramdef = '@Var1 DATETIME, @Var2 DATETIME'EXEC sp_executesql @sql,@paramdef,@Var1='1/1/2009',@Var2='1/31/2009'[/code]Mon, 19 Jan 2009 06:14:13 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic637670-149-1.aspxHi Grant, the stored procedure already uses the sp_executesql function. What I'm essentially trying to acheive is to create a dynamic SQL statement, but in which the WHERE clause is different in each case and also uses variables for the date ranges. The sticking point for me is to sending the variable dates to the stored procedure within the "text" of the dynamic SQL statement.Starting to find ow this becomes increasingly difficult without adding a second line of code to set these variables first as EXECUTE won't allow expressions.Richard.Sun, 18 Jan 2009 15:35:37 GMTrferguson-989993http://www.sqlservercentral.com/Forums/Topic637670-149-1.aspxIf you're getting this sophisticated with dynamic SQL, then you really need to look into sp_executesql instead of simply using the exec statement. sp_executesql will allow you to define parameters within your dynamic sql, which means it will create execution plans that are more likely to be reused rather than a new compile each and every time. It works largely like exec, but not entirely. Look it up in the Books Online.Fri, 16 Jan 2009 06:35:11 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic637670-149-1.aspxHi Bitbucket,I'm actually trying to create a dynamic UPDATE statement within the stored procedure as there are multiple (100's) of update statements I'll end up with which does differing calculations on the same data table. I started passing this to a stored procedure as some of the Fields in each select statement are common such as Select BranchCode and also Group By BranchCode are always used. I'm basically trying to cut down the length of the coding required by using the same common items where possible.What's different in each line is the calculation I want and also the WHERE clause based on two dates. Some calculations are dependant on the WHERE clause requiring a range between two dates, others where no WHERE clause is applicable. I got the dynamic update statement working fine, but I can't use the two date variables directly in the EXECUTE statement as it appears is not allowed.I think the example I used was a bit too simple, but as RBarry pointed out that you can't use expressions in EXECUTE statements, that seems to have explained what the problem was, rather than me posting the rest of the code first.I've posted it below if you were curious to take a look.--this basically gets repeated many times over with a different calculation and works, but has a fixed date in the WHERE clauseEXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'SalesExGST', 'SELECT BranchCode, ISNULL(SUM(ExtendedNetAmount)-SUM(ExtendedTaxAmount),0) AS SalesExGST WHERE SaleDate>='20081201 00:00:00' and SaleDate<'20090101 00:00:00' FROM zzTEMP_SaleLine GROUP BY BranchCode'--this this is what I tried doing, as some, but not all of the statements will use the same date range that defined earlier--1st calculation, uses the datesEXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'SalesExGST', 'SELECT BranchCode, ISNULL(SUM(ExtendedNetAmount)-SUM(ExtendedTaxAmount),0) AS SalesExGST WHERE SaleDate>=''' + @StartDate + ''' AND SaleDate<''' + @EndDate + ''' FROM zzTEMP_SaleLine GROUP BY BranchCode'--2nd calculation, uses the datesEXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'GST', 'SELECT BranchCode, ISNULL(SUM(ExtendedTaxAmount),0) AS GST WHERE SaleDate>=''' + @StartDate + ''' AND SaleDate<''' + @EndDate + ''' FROM zzTEMP_SaleLine GROUP BY BranchCode'--3rd calculation, does not use the dates, but does use same table name and group by etc.EXECUTE zzUpdateSummaryData 'zzTEMP_BranchSummary', 'BranchCode', 'AvgItemsSale', 'SELECT BranchCode,ISNULL(NoItemsSold/NULLIF(NoSales,0),0) AS AvgItemsSale FROM zzTEMP_BranchSummary'--The Stored ProcedureCREATE PROCEDURE zzUpdateSummaryData@TableName VARCHAR(30), @PrimaryKey VARCHAR(30), @FieldName VARCHAR(30), @sqlStatement NVARCHAR(4000)ASSET NOCOUNT ONDECLARE @sql AS NVARCHAR(4000)SET @sql='UPDATE ' + @TableName + 'SET ' + @FieldName + '=tmpRecordSet.' + @FieldName + 'FROM('+ @sqlStatement + ') tmpRecordSetWHERE tmpRecordSet.' + @PrimaryKey + ' = ' + @TableName + '.' + @PrimaryKey + 'UPDATE ' + @TableName + 'SET ' + @FieldName + ' = 0WHERE ' + @FieldName + ' IS NULL'EXECUTE sp_executesql @sqlGORegards, Richard.Thu, 15 Jan 2009 21:27:56 GMTrferguson-989993http://www.sqlservercentral.com/Forums/Topic637670-149-1.aspx[quote]rfergusonI'm trying to construct a more complex variable where I'll pass two date variables [/quote].May I inquire as to why you want to use a a complex variable to pass two dates? Seems it would be much simplier to pass two variables each containing one date to your procedure. Anyway using what you have posted try this:[code]ALTER PROCEDURE PrintMessage @variable VARCHAR(30) @var2 VARCHAR(30)= ' 'ASPRINT @variable, +' ' + @var2--testingDECLARE @variable VARCHAR(30)SET @Variable = 'Print message'EXECUTE PrintMessage @Variable, 'some more text'ResultPrint message some more textorDECLARE @variable VARCHAR(30)SET @Variable = 'I will Print message'EXECUTE PrintMessage @VariableResult when only one variable is passed to the procedure:I will Print message [/code]Again I would urge you to follow the KISS principle (Keep It Simple S-----)Thu, 15 Jan 2009 19:43:19 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic637670-149-1.aspxYou cannot do expressions in the EXECUTE command. You need to do them in a preceding SET or SELECT.Thu, 15 Jan 2009 16:30:42 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic637670-149-1.aspxHi All, just looking for some assistance in using EXECUTE commands in SQL2000.As a simple example of what I'm stuck on:I have a stored procedure:CREATE PROCEDURE PrintMessage @variable VARCHAR(30)ASPRINT @variableGOand I'm passing a variable string in Query Analyser to the stored procedure which works fine:DECLARE @variable NVARCHAR(30)SET @variable = 'Message Text'EXECUTE PrintMessage @variablewhen I try this and add some other text to the EXECUTE statement:DECLARE @variable NVARCHAR(30)SET @variable = 'Message Text'EXECUTE PrintMessage @variable + 'Some More Text'Auery Analyser won't parse the query and returns the error:Server: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near '+'.I'm trying to construct a more complex variable where I'll pass two date variables within a SELECT statement, but I'm stuck with the same situation as above. The reason I don't place the + 'Some More Text' in the SET statement, is nothing more than I'll be doubling the amount of lines of code and I'll eventually end up with a few thousand lines, which I'm attempting to write this so I'm only changing the EXECUTE statement, rather than adding and changing two lines of code for each step I'm strying to create (hopefully makes it shorter for me).I thought I'd use a simple example above first as I'm stuck as to why this concept won't work. If I can understand this, I can look again at another method for the more complex query.Appreciate any assistance, thanks :)Richard.Thu, 15 Jan 2009 16:01:54 GMTrferguson-989993