–建立测试环境IF OBJECT_ID(‘tb’,’U’) IS NOT NULL DROP TABLE tbGO
CREATE TABLE tb(id int identity, code varchar(10),name varchar(20), CONSTRAINT PK_TB PRIMARY KEY (id))GOinsert tbselect ‘001’,’财务部’ union allselect ‘002’,’贸易部’ union allselect ‘003’,’技术部’ union allselect ‘004’,’市场部’ go
–动态语句1 得到结果集declare @sql nvarchar(4000)declare @wheresql varchar(1000)set @wheresql=’ and技术部”’set @sql = ‘select * from tb where 1=1 ‘+ @wheresqlexec(@sql)–结果/*id code name———– ———- ——————–3 003 技术部*/
–动态语句2 输入输出参数declare @ID intset @sql = ‘select @id=id from tb where name=@name ‘exec sp_executesql @sql, N’@ID int output,@name varchar(20)’,@ID output,’技术部’select @ID
–结果/*———–3*/
,每天告诉自己一次,『我真的很不错』