/****** Script for SelectTopNRows command from SSMS ******/
use data
go
declare @count int
---1 EXEC中直接@变量无法传递出去
exec('declare @count int;SELECT @count=COUNT(*)
FROM [data].[dbo].[产品表]')
select @count
--2带入游标
--判断游标是否存在
if exists(select * from MASTER.dbo.syscursors where cursor_name='test')
begin
deallocate test
end
exec('declare test cursor for (SELECT COUNT(*)
FROM [data].[dbo].[产品表])')
open test
FETCH NEXT FROM test INTO @count
select @count
close test
--3全局临时表
--先删除出现的临时表
if OBJECT_ID('tempdb..##temp') is not null
begin
drop table tempdb..##temp
end
exec('SELECT COUNT(*) as 总数 into ##temp
FROM [data].[dbo].[产品表]')
select @count=总数 from ##temp
select @count
--4执行 EXEC sp_executesql.
declare @sql nvarchar(500) --@sql的定义必须为N的固定长度!!!
SET @sql='SELECT [产品编号]FROM [data].[dbo].[产品表]where [产品编号]like @str;
SELECT @count=count([产品编号])FROM [data].[dbo].[产品表]where [产品编号]like @str'
declare @str varchar(250);set @str='YO%'
/***********************************************************************/
EXEC sp_executesql N'SELECT [产品编号]FROM [data].[dbo].[产品表]where [产品编号]like @str;
SELECT @count=count([产品编号])FROM [data].[dbo].[产品表]where [产品编号]like @str'
,N'@str varchar(250),@count int out',N'YO%',@count out
--以逗号区分区域块
--第二块内容变量必须N'...'隔开 如果是传出参数后面必须跟OUT,包括第三块也要
select @count
EXEC sp_executesql @sql,N'@str varchar(250),@count int out',@str,@count out
select @count
--与EXE的区别 字符变量无须再用''''转义出来!!!
SET @sql='SELECT [产品编号]FROM [data].[dbo].[产品表]where [产品编号]like '''+ @str+''';
SELECT count([产品编号]) as count FROM [data].[dbo].[产品表]where [产品编号]like '''+ @str +''''
exec(@sql)
sqlserver传递动态sql语句中exec(@变量)的方法
最新推荐文章于 2024-04-29 19:28:06 发布