是这个样子吗??????? --> 测试数据:#tb IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([id] INT,[name] VARCHAR(4)) INSERT #tb SELECT 1,'土豆' UNION ALL SELECT 2,'水锅' UNION ALL SELECT 3,'蠢驴' GO --------------开始查询-------------------------- IF OBJECT_ID('pp',N'P') IS NOT NULL DROP PROC pp GO CREATE PROC pp(@tablename VARCHAR(10),@colname VARCHAR(10),@col VARCHAR(10), @out VARCHAR(10) OUTPUT) AS BEGIN DECLARE @sql NVARCHAR(4000) SET @sql = 'select @a=[id] from ' + @tablename + ' where ' + @colname + ' = ' + '''' + @col + '''' --PRINT @sql EXEC sp_executesql @sql , N'@a VARCHAR(10) OUTPUT' , @out OUTPUT END GO --> 测试 DECLARE @o VARCHAR(10) EXEC pp '#tb','name','土豆',@o OUTPUT SELECT @o ----------------结果---------------------------- /* 1 */
create proc P_test @tablename varchar(50), @field varchar(50), @Value varchar(100) as begin declare @sql varchar(2000) set @sql='select * from '+@tablename+' where '+@field+'='+@Value exec(@sql) end
--> 测试数据:#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([id] INT,[name] VARCHAR(4))
INSERT #tb
SELECT 1,'土豆' UNION ALL
SELECT 2,'水锅' UNION ALL
SELECT 3,'蠢驴'
GO
--------------开始查询--------------------------
IF OBJECT_ID('pp',N'P') IS NOT NULL DROP PROC pp
GO
CREATE PROC pp(@tablename VARCHAR(10),@colname VARCHAR(10),@col VARCHAR(10), @out VARCHAR(10) OUTPUT)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
SET @sql = 'select @a=[id] from ' + @tablename + ' where ' + @colname + ' = ' + '''' + @col + ''''
--PRINT @sql
EXEC sp_executesql
@sql ,
N'@a VARCHAR(10) OUTPUT' ,
@out OUTPUT
END
GO
--> 测试
DECLARE @o VARCHAR(10)
EXEC pp '#tb','name','土豆',@o OUTPUT
SELECT @o ----------------结果----------------------------
/*
1
*/
@tablename varchar(50),
@field varchar(50),
@Value varchar(100)
as
begin
declare @sql varchar(2000)
set @sql='select * from '+@tablename+' where '+@field+'='+@Value
exec(@sql)
end
输入的参数里有 @InValue 和 @intype
@InValue 的类型是 sql varant在里面进行转换
declare @realVal=CONVERT(varchar(100),@InValue,100)
这样就转换成varchar类型了然后根据intype判断where里面要不要加‘号。