查询表数据通用存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[SearchTableData]
@TableName VARCHAR(255),
@Fields VARCHAR(255),
@WhereField VARCHAR(255),
@WhereValue VARCHAR(255)
AS 
BEGIN
DECLARE @sql VARCHAR(255)
DECLARE @xtype INT=0
SET @sql ='SELECT '+@Fields+' FROM '+@TableName;
SELECT @xtype=syscolumns.xtype
FROM syscolumns, systypes 
WHERE syscolumns.xusertype = systypes.xusertype 
AND syscolumns.id = object_id(@TableName)and  syscolumns.name=@WhereField
IF(@xtype=0)
BEGIN
RAISERROR ('修改字段不存在', 16, 1)
END
SET @sql=@sql+' WHERE '+@WhereField+'='
IF(@xtype=48 OR @xtype=52 OR @xtype=56 OR @xtype=62OR @xtype=127)
BEGIN
SET @sql=@sql+@WhereValue;
END
ELSE IF(@xtype=35OR @xtype=99 OR @xtype=167 OR @xtype=175 OR @xtype=231 OR @xtype=239)
BEGIN
SET @sql=@sql+''''+@WhereValue+'''';
END
ELSE
RAISERROR ('数据类型错误', 16, 1)
EXEC (@sql)
END 
exec SearchTableData '表名','*','CountId','1'

 

转载于:https://www.cnblogs.com/RambleLife/p/9830778.html