动态sql语句基本语法

原帖：http://blog.youkuaiyun.com/htl258/archive/2009/03/26/4027674.aspx

 

动态sql语句基本语法
1 :普通SQL语句可以用Exec执行

eg:   Select * from tableName
         Exec ( ' select * from tableName ' )
         Exec sp_executesql N ' select * from tableName '     -- 请注意字符串前一定要加N

2 :字段名，表名，数据库名之类作为变量时，必须用动态SQL

eg:  
declare @fname varchar ( 20 )
set @fname = ' FiledName '
Select @fname from tableName              -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。
Exec ( ' select ' + @fname + ' from tableName ' )     -- 请注意 加号前后的 单引号的边上加空格

当然将字符串改成变量的形式也可
declare @fname varchar ( 20 )
set @fname = ' FiledName ' -- 设置字段名

declare @s varchar ( 1000 )
set @s = ' select ' + @fname + ' from tableName '
Exec ( @s )                -- 成功
exec sp_executesql @s    -- 此句会报错



declare @s Nvarchar ( 1000 )  -- 注意此处改为nvarchar(1000)
set @s = ' select ' + @fname + ' from tableName '
Exec ( @s )                -- 成功    
exec sp_executesql @s    -- 此句正确

3 . 输出参数
declare @num int ,
        @sqls nvarchar ( 4000 )
set @sqls = ' select count(*) from tableName '
exec ( @sqls )
-- 如何将exec执行结果放入变量中？

declare @num int ,
               @sqls nvarchar ( 4000 )
set @sqls = ' select @a=count(*) from tableName '
exec sp_executesql @sqls ,N ' @a int output ' , @num output
select @num

几个是几个相关实例:

-- 1. 使用 EXEC 实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar ( 10 ) = null ,
@para2 varchar ( 10 ) = null ,
@para3 varchar ( 10 ) = null ,
@para4 varchar ( 10 ) = null
AS
SET NOCOUNT ON
DECLARE @sql varchar ( 8000 )
SET @sql = ' SELECT * FROM tbname WHERE 1=1 '
IF @para1 IS NOT NULL
    SET @sql = @sql + ' AND col1= ''' + @para1 + ''''
IF @para2 IS NOT NULL
    SET @sql = @sql + ' AND col2= ''' + @para2 + ''''
IF @para3 IS NOT NULL
    SET @sql = @sql + ' AND col3= ''' + @para3 + ''''
IF @para4 IS NOT NULL
    SET @sql = @sql + ' AND col4= ''' + @para4 + ''''
EXEC ( @sql )
GO


/* ====================================================== */

-- 2. 使用 sp_executesql 实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar ( 10 ) = null ,
@para2 datetime = null ,
@para3 varchar ( 10 ) = null ,
@para4 int = null
AS
SET NOCOUNT ON
DECLARE @sql nvarchar ( 4000 )
SET @sql = ' SELECT * FROM tbname WHERE 1=1 '
    + CASE WHEN @para1 IS NULL THEN '' ELSE ' AND col1=@para1 ' END
    + CASE WHEN @para2 IS NULL THEN '' ELSE ' AND col2=@para2 ' END
    + CASE WHEN @para3 IS NULL THEN '' ELSE ' AND col3=@para3 ' END
    + CASE WHEN @para4 IS NULL THEN '' ELSE ' AND col4=@para4 ' END
EXEC sp_executesql @sql ,N '
    @para1 varchar(10)=null,
    @para2 datetime=null,
    @para3 varchar(10)=null,
    @para4 int=null
' , @para1 , @para2 , @para3 , @para4
GO


/* ====================================================== */

-- 3. 不使用动态 Transact-SQL 语句实现的动态参数存储过程
CREATE PROC p_test
@para1 varchar ( 10 ) = null ,
@para2 datetime = null ,
@para3 varchar ( 10 ) = null ,
@para4 int = null
AS
SET NOCOUNT ON
SELECT * FROM tbname
WHERE ( @para1 IS NULL OR col1 = @para1 )
    AND ( @para2 IS NULL OR col2 = @para2 )
    AND ( @para3 IS NULL OR col3 = @para3 )
    AND ( @para4 IS NULL OR col4 = @para4 )