我是想创建一个存储过程,把表名和条件传进去,看是否有符合条件的数据,如果有返回1,没有返回0,这样写为什么不正确?
create proc PR_CheckRepeat@Values varchar(100),
@TableName varchar(100),
@num int outputas BEGIN
declare @sql varchar(1000)
@sql = 'if not exists(select 1 from '+@TableName+' where '+@Values+') begin set num = 0 end else begin num = 1 end'
exec @sql
END
GO
create proc PR_CheckRepeat@Values varchar(100),
@TableName varchar(100),
@num int outputas BEGIN
declare @sql varchar(1000)
@sql = 'if not exists(select 1 from '+@TableName+' where '+@Values+') begin set num = 0 end else begin num = 1 end'
exec @sql
END
GO
--联机帮助的例子
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
declare @sql nvarchar(4000), @param nvarchar(1000);
set @sql=N'select @num=count(1) from '+quotename(@tablename)+' where '+@values;
set @param=N'@num int output';
exec sp_executesql @sql,@param, @num output;
@Values varchar(100),
@TableName varchar(100),
@num int output
as
BEGIN
exec('select 1 from '+@TableName+' where '+@Values)
set @num =sgn(@@rowcount)
return
END
GO
@Values varchar(100),
@TableName varchar(100),
@num int output
as
BEGIN
declare @sql nvarchar(1000)
set @sql = 'if not exists(select 1 from '+@TableName+' where '+@Values+') select @num=1 else select @num=1 '
exec sp_executesql @sql,N'@num output',@num output
END
GO
set @sql=N'select @num=count(1) from '+quotename(@tablename)+' where '+@values;
set @param=N'@num int output';
exec sp_executesql @sql,@param, @num output;