January 15, 2009 at 4:01 pm
Hi 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)
AS
PRINT @variable
GO
and 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 @variable
when 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 3
Line 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.
January 15, 2009 at 4:30 pm
You cannot do expressions in the EXECUTE command. You need to do them in a preceding SET or SELECT.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 15, 2009 at 7:43 pm
rferguson
I'm trying to construct a more complex variable where I'll pass two date variables
.
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:
ALTER PROCEDURE PrintMessage
@variable VARCHAR(30)
@var2 VARCHAR(30)= ' '
AS
PRINT @variable, +' ' + @var2
--testing
DECLARE @variable VARCHAR(30)
SET @Variable = 'Print message'
EXECUTE PrintMessage @Variable, 'some more text'
Result
Print message some more text
or
DECLARE @variable VARCHAR(30)
SET @Variable = 'I will Print message'
EXECUTE PrintMessage @Variable
Result when only one variable is passed to the procedure:
I will Print message
Again I would urge you to follow the KISS principle (Keep It Simple S-----)
January 15, 2009 at 9:27 pm
Hi 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 clause
EXECUTE 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 dates
EXECUTE 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 dates
EXECUTE 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 Procedure
CREATE PROCEDURE zzUpdateSummaryData
@TableName VARCHAR(30), @primarykey-2 VARCHAR(30), @FieldName VARCHAR(30), @sqlStatement NVARCHAR(4000)
AS
SET NOCOUNT ON
DECLARE @sql AS NVARCHAR(4000)
SET @sql='
UPDATE ' + @TableName + '
SET ' + @FieldName + '=tmpRecordSet.' + @FieldName + '
FROM
('+ @sqlStatement + ') tmpRecordSet
WHERE tmpRecordSet.' + @primarykey-2 + ' = ' + @TableName + '.' + @primarykey-2 + '
UPDATE ' + @TableName + '
SET ' + @FieldName + ' = 0
WHERE ' + @FieldName + ' IS NULL'
EXECUTE sp_executesql @sql
GO
Regards, Richard.
January 16, 2009 at 6:35 am
If 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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 18, 2009 at 3:35 pm
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.
January 19, 2009 at 6:14 am
rferguson (1/18/2009)
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.
But using sp_executesql you can also pass it variables. You simply write the query, dynamic or not, like this:
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'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2011 at 5:06 am
New EXECUTE statement in SQL Server Denali. See wxample on www.sqlsuperfast.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply