posted on Wednesday, December 14, 2005 12:00 PM by bknight

Three methods for dynamic queries

There are three main ways to do dynamic queries inside of SQL Server. My personal favorite method is option one below since it's a single line of code. Option three helps with caching of parameters though. Needless to say, dynamic queries should be avoided whenever possible :).

DECLARE @ColumnName sysname
DECLARE
@TableName sysname
DECLARE
@strSQL varchar(255)
DECLARE @unicodestrSQL nvarchar(255)

SET @TableName = 'Employees'
SET @ColumnName = 'LastName'

--Method one

EXEC ('select max(datalength('+@ColumnName+')) from ' + @TableName);

--Method two

SET @strSQL = 'select max(datalength('+@ColumnName+')) from ' + @TableName
EXEC (@strSQL)

--Method three

SET @unicodestrSQL = N'select max(datalength('+@ColumnName+')) from ' + @TableName
EXEC sp_executesql @unicodestrSQL;

-- Brian Knight

 

Comments

# re: Three methods for dynamic queries

Tuesday, March 18, 2008 7:20 AM by zxevil163
Q7yxka Hi from Russia!