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