1 :普通SQL语句可以用
Exec
执行
eg:
Select
*
from
tableName
Exec
(
'select * from tableName'
)
Exec
sp_executesql N
'select * from tableName'
2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
eg:
declare
@fname
varchar
(20)
set
@fname =
'FiledName'
Select
@fname
from
tableName
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)
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)
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
几个是几个相关实例:
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
/*======================================================*/
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
/*======================================================*/
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)