Using Variables in EXECUTE statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Using Variables in EXECUTE statement

Using Variables in EXECUTE statement

Rate Topic

Display Mode

Topic Options

Author

Message

rferguson-989993

Posted Thursday, January 15, 2009 4:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 26, 2009 11:02 PM
Points: 3, Visits: 12

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.

Post #637670

RBarryYoung

Posted Thursday, January 15, 2009 4:30 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

You cannot do expressions in the EXECUTE command. You need to do them in a preceding SET or SELECT.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #637679

bitbucket-25253

Posted Thursday, January 15, 2009 7:43 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 5,103, Visits: 20,213

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-----)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #637720

rferguson-989993

Posted Thursday, January 15, 2009 9:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 26, 2009 11:02 PM
Points: 3, Visits: 12

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 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 + ' = ' + @TableName + '.' + @PrimaryKey + '
UPDATE ' + @TableName + '
SET ' + @FieldName + ' = 0
WHERE ' + @FieldName + ' IS NULL'
EXECUTE sp_executesql @sql
GO

Regards, Richard.

Post #637732

Grant Fritchey

Posted Friday, January 16, 2009 6:35 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383, Visits: 25,187

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #637970

rferguson-989993

Posted Sunday, January 18, 2009 3:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 26, 2009 11:02 PM
Points: 3, Visits: 12

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.

Post #639036

Grant Fritchey

Posted Monday, January 19, 2009 6:14 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383, Visits: 25,187

rferguson (1/18/2009)

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #639260

a.rajmane

Posted Sunday, March 06, 2011 5:06 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, April 16, 2011 10:43 PM
Points: 19, Visits: 39

New EXECUTE statement in SQL Server Denali. See wxample on www.sqlsuperfast.com

Post #1073825

« Prev Topic | Next Topic »

Permissions