将列名称作为参数传递的存储过程 [英] Stored Procedure to pass column name as parameter

问题描述

  ALTER   PROCEDURE  [dbo]。[checkUser_SP] 
   -   在此处添加存储过程的参数 
  @ Fieldname   varchar （ 150 ），
  @ FieldValue   VARCHAR （ 150 ）
 < span class =code-keyword> AS  
 
  BEGIN  
 
  DECLARE   @ isExist   bit  
  DECLARE   @ columnName   VARCHAR （ 150 ）
 选择 @ columnName =  @ Fieldname  
  SET   NOCOUNT  < span class =code-keyword> ON  
 
  IF  存在（选择  null  来自 dbo。 TBL_Users  WHERE   @ Fieldname  =  @ FieldValue ） 
 开始 
  set  @ isExist = 1 
 结束 
 其他 
 开始 
  set  @ isExist = 0 
  end  
 选择  @ isExist  
 结束 

这是我创建的程序我需要结果如果特定列有传递的数据然后 1 否则 0

  exec  [checkUser_SP] @ Fieldname = '  [User_ID]'，@ FieldValue = 1 

我有表中的值，但它总是显示结果 0

解决方案

您需要更改SP以启用动态查询

  DECLARE   @sql   VARCHAR （MAX）= ' ' 
 
  SET   @sql  = '  SELECT * FROM WHERE' + <跨度class =code-sdkkeyword> @ FieldName  + '  =' +  @ FieldValue  
  EXEC （ @ sql ） 

如需了解更多信息，请参阅：

http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server / [ ^ ]

在存储过程中构建动态SQL [ ^ ]

http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1 [ ^ ]

http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures [ ^ ]

ALTER PROCEDURE [dbo].[checkUser_SP] 
	-- Add the parameters for the stored procedure here
	   @Fieldname varchar(150),
    @FieldValue VARCHAR(150)
AS

BEGIN

    DECLARE @isExist bit 
DECLARE @columnName  VARCHAR(150)
select  @columnName= @Fieldname
 SET NOCOUNT ON

    IF Exists ( Select null from dbo.TBL_Users WHERE  @Fieldname = @FieldValue  )
     begin
	set @isExist=1
	end
	else
	begin
	set @isExist=0
	end
select @isExist
end

It is the procedure I have created i need the result if particular column have the data that is passed then 1 else 0

exec [checkUser_SP] @Fieldname='[User_ID]', @FieldValue=1

I have the value in the table but it always shows result 0

解决方案

You need to change your SP to enable dynamic queries

DECLARE @sql VARCHAR(MAX) = ''

SET @sql = 'SELECT * FROM WHERE ' + @FieldName + '=' + @FieldValue
EXEC(@sql)

For further informatin, please see:
http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]
Building Dynamic SQL In a Stored Procedure[^]
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1[^]
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures[^]

这篇关于将列名称作为参数传递的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！