Great feed back on this, it's adding to my research. Just know I said SQL
gurus (often purists) said to avoid. Peter has a great idea here that should
make the purists and the rest of us happy enough.
Below you'll find the BOL description of sp_executesql...
HTH,
Rick
sp_executesql
Executes a Transact-SQL statement or batch that can be reused many times, or
that has been built dynamically. The Transact-SQL statement or batch can
contain embedded parameters.
Syntax
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
Arguments
[@stmt =] stmt
Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either
a Unicode constant or a variable that can be implicitly converted to ntext. More
complex Unicode expressions (such as concatenating two strings with the + operator)
are not allowed. Character constants are not allowed. If a constant is specified, it
must be prefixed with an N. For example, the Unicode constant N'sp_who' is legal, but
the character constant 'sp_who' is not. The size of the string is limited only by
available database server memory.
stmt can contain parameters having the same form as a variable name, for example:
N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'
Each parameter included in stmt must have a corresponding entry in both the @params
parameter definition list and the parameter values list.
[@params =] N'@parameter_name data_type [,...n]'
Is one string that contains the definitions of all parameters that have been embedded
in stmt. The string must be either a Unicode constant or a variable that can be
implicitly converted to ntext. Each parameter definition consists of a parameter name
and a data type. n is a placeholder indicating additional parameter definitions. Every
parameter specified in stmt must be defined in @params. If the Transact-SQL statement
or batch in stmt does not contain parameters, @params is not needed. The default valu
e for this parameter is NULL.
[@param1 =] 'value1'
Is a value for the first parameter defined in the parameter string. The
value can be a constant or a variable. There must be a parameter value
supplied for every parameter included in stmt. The values are not needed if
the Transact-SQL statement or batch in stmt has no parameters.
n
Is a placeholder for the values of additional parameters. Values can be only
constants or variables. Values cannot be more complex expressions such as
functions, or expressions built using operators.
Return Code Values
0 (success) or 1 (failure)
Result Sets
Returns the result sets from all the SQL statements built into the SQL
string.
Remarks
sp_executesql has the same behavior as EXECUTE with regard to batches, the
scope of names, and database context. The Transact-SQL statement or batch in
the sp_executesql stmt parameter is not compiled until the sp_executesql
statement is executed. The contents of stmt are then compiled and executed
as an execution plan separate from the execution plan of the batch that
called sp_executesql. The sp_executesql batch cannot reference variables
declared in the batch calling sp_executesql. Local cursors or variables in
the sp_executesql batch are not visible to the batch calling sp_executesql.
Changes in database context last only to the end of the sp_executesql
statement.
sp_executesql can be used instead of stored procedures to execute a
Transact-SQL statement a number of times when the change in parameter values
to the statement is the only variation. Because the Transact-SQL statement
itself remains constant and only the parameter values change, the Microsoft�
SQL ServerT query optimizer is likely to reuse the execution plan it
generates for the first execution.
Note If object names in the statement string are not fully qualified, the
execution plan is not reused.
sp_executesql supports the setting of parameter values separately from the
Transact-SQL string:
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
Being able to substitute parameters in sp_executesql offers these advantages
to using the EXECUTE statement to execute a string:
a.. Because the actual text of the Transact-SQL statement in the
sp_executesql string does not change between executions, the query optimizer
will probably match the Transact-SQL statement in the second execution with
the execution plan generated for the first execution. Therefore, SQL Server
does not have to compile the second statement.
b.. The Transact-SQL string is built only once.
c.. The integer parameter is specified in its native format. Casting to
Unicode is not required.
Permissions
Execute permissions default to the public role.
Examples
A. Execute a simple SELECT statement
This example creates and executes a simple SELECT statement that contains an
embedded parameter named @level.
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
B. Execute a dynamically built string
This example shows using sp_executesql to execute a dynamically built
string. The example stored procedure is used to insert data into a set of
tables used to partition sales data for a year. There is one table for each
month of the year with the following format:
CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)
For more information about retrieving data from these partitioned tables,
see Using Views with Partitioned Data.
The name of each table consists of the first three letters of the month
name, the four digits of the year, and the constant Sales. The name can be
built dynamically from an order date:
/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'
This sample stored procedure dynamically builds and executes an INSERT
statement to insert new orders into the correct table. It uses the order
date to build the name of the table that should contain the data, then
incorporates that name into an INSERT statement. (This is a simple example
for sp_executesql. It does not contain error checking and does not include
checks for business rules, such as ensuring that order numbers are not
duplicated between tables.)
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
@PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'
/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)
EXEC sp_executesql @InsertString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate
GO
Using sp_executesql in this procedure is more efficient than using EXECUTE
to execute a string. When sp_executesql is used, there are only 12 versions
of the INSERT string generated, 1 for each monthly table. With EXECUTE, each
INSERT string is unique because the parameter values are different. Although
both methods generate the same number of batches, the similarity of the
INSERT strings generated by sp_executesql makes it more likely that the
query optimizer will reuse execution plans.
See Also
----- Original Message -----
From: "Peter Stolz" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 10, 2001 5:20 AM
Subject: RE: OT STORED PROCEDURES
> Use sp_executesql instead of EXEC() to run your dynamic SQL. It caches the
> query plans.
>
> P.
>
> -----Original Message-----
> From: Eric Barr [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 10, 2001 7:59 AM
> To: CF-Talk
> Subject: RE: OT STORED PROCEDURES
>
>
> I've gotten one result set back from exec before ...never tired multiple,
> but I don't see why not ...its Executing SQL.
>
> BUT, be careful with EXEC ...you can incur some pretty significant
> performance hits ..... it messes up the SP plan caching, i.e. loosing one
of
> the major benefits of SP's ...speed.
>
>
> ------------------------------------------------
> Common sense is genius dressed in its working clothes.
> -- Ralph Waldo Emerson
>
> Eric Barr
> Zeff Design
> (p) 212.714.6390
> (f) 212.580.7181
>
>
> -----Original Message-----
> From: Joe Sheble (Wizaerd) [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 10, 2001 1:15 AM
> To: CF-Talk
> Subject: RE: OT STORED PROCEDURES
>
>
> I had thought of using Dynamic SQL in SQL Server stored procedures, but
> hadn't gotten around with playing with it much yet. Can you still return
> multiple recordsets this way? In fact, does the EXEC() SQL function
return
> a recordset at all?
>
> Joseph E. Sheble
> a.k.a. Wizaerd
> Wizaerd's Realm
> Canvas, 3D, Graphics,
> ColdFusion, PHP, and mySQL
> http://www.wizaerd.com
> =================================
>
> > -----Original Message-----
> > From: Bob Silverberg [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, January 09, 2001 3:50 PM
> > To: CF-Talk
> > Subject: RE: OT STORED PROCEDURES
> >
> >
> > I just wrote this off the top of my head, so it may not run (I'm not
sure
> > about the double and single quotes), but it gives you some idea of how
to
> > dynamically build SQL statements in a stored proc (if that's what you're
> > looking for):
> >
> > CREATE PROC MyProc
> > @Active bit = NULL,
> > @Test bit = NULL
> > AS
> >
> > DECLARE @sSQL as varchar(1000)
> >
> > SET @sSQL = 'Select * from member where membernumber <> 0'
> > IF @Active = 1 SET @sSQL = @sSQL + 'and active="y"'
> > IF @Test = 1 SET @sSQL = @sSQL + 'and test="no"'
> > SET @sSQL = @sSQL + 'order by membernumber'
> >
> > EXEC (@sSQL)
> >
> > GO
> >
> > -----Original Message-----
> > From: William J Wheatley [mailto:[EMAIL PROTECTED]]
> > Sent: January 9, 2001 3:47 PM
> > To: CF-Talk
> > Subject: RE: OT STORED PROCEDURES
> >
> >
> > is there anyway to do the if in the query of a stored procedure without
> > using a Cursor.
> >
> > and this is what i wanted
> >
> > Select *
> > from member
> > where membernumber<>0
> > and active='y'
> > and test='no'
> > order by membernumber
> >
> > by using this type logic
> >
> > select *
> > from member
> > where membernumber<>0
> >
> > IF @active is 'y'
> > and active='y'
> > END
> > IF @test is 'y'
> > and test='no'
> > END
> > order by membernumber
> >
> >
> > so that by using a stored procedure i can say is active is N then the
(and
> > active='y') wont run properly.
> > =)
> >
> > hope that helps
> >
> >
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
