sql存储过程>表名作为输入参数。 [英] sql Stored Procedure > table name as an input parameter.

问题描述

我有一个存储过程如下：

  USE  [MashtiHasanShoppingDB] 
  GO  
  / *   *****对象：StoredProcedure [dbo]。[Del_Users]脚本日期：9/12/2015 1:57:45 AM * ***** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
  ALTER   PROCEDURE  [dbo]。[Del_Users] 
  @UserID   int  
  AS  
  BEGIN  
  DELETE   FROM 用户其中 UserID =  @ UserID  
  END  

现在，我想让这个存储过程适用于其他表。我不应该为一个数据库反复编写相同的代码。

我希望它将表名作为参数，例如：

  ALTER   PROCEDURE  [dbo]。[Del_Users] 
  @ UserID   int ，
  @ Table_name  nvarcher（< span class =code-digit> 50 ）
  AS  
  BEGIN  
  DELETE   FROM   @ Table_name  其中 UserID =  @ UserID  
 结束 

但我不知道怎么做。



我很感激你的帮助，

谢谢。

解决方案

通过创建动态sql语句，您将能够在运行时构建和运行查询。



这应该可以解决问题。首先运行存储过程并检查 print 命令的输出。当您确信一切正常时，请注释掉打印命令并取消注释 exec 命令：

 ALTER PROCEDURE [dbo]。[Del_Users] 
 @UserID int，
 @Table_name nvarcher（50）
 AS 
 BEGIN 
 
声明@cmd nvarchar（max）=''
设置@cmd + = N'DELETE FROM'+ @Table_name +'其中UserID ='@ UserID 
 print @cmd 
 --exec sys .sp_executesql @cmd 
 
 
 END 

你可以做到 - 但这很痛苦：

  DECLARE   @ Command   NVARCHAR （MAX）
  SET   @ Command  = '  DELETE FROM' +  @ Table_name  + '  WHERE UserId =' +  @ UserID  
  EXEC （ @命令）

它也有风险：如果将@UserID更改为字符串，它会让您对SQL注入攻击持开放态度。



个人？我不会这样做。

Hi,

I have a stored procedure as below:

USE [MashtiHasanShoppingDB]
GO
/****** Object:  StoredProcedure [dbo].[Del_Users]    Script Date: 9/12/2015 1:57:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Del_Users]
@UserID int
AS
BEGIN
	DELETE FROM Users where UserID = @UserID
END

Now, I want to have this stored procedure working for other table's. I shouldn't write the same code over and over for one database.
I want it to take the table name as an parameter, something like:

ALTER PROCEDURE [dbo].[Del_Users]
@UserID int,
@Table_name nvarcher(50)
AS
BEGIN
	DELETE FROM @Table_name where UserID = @UserID
END

But i don't know how to do it.

I'd be grateful of your help,
Thanks.

解决方案

By creating a dynamic sql statement you will be able to build and run the query at runtime.

This should do the trick. First run the stored procedure and check the output from the print command. When you are confident that all is okay, comment out the print command and uncomment the exec command:

ALTER PROCEDURE [dbo].[Del_Users]
@UserID int,
@Table_name nvarcher(50)
AS
BEGIN
	
	declare @cmd nvarchar(max) = ''
	set @cmd += N' DELETE FROM ' + @Table_name + ' where UserID = ' @UserID
    print @cmd
	--exec sys.sp_executesql @cmd

	
END

You can do it - but it's a pain:

DECLARE @Command NVARCHAR(MAX)
SET @Command = 'DELETE FROM ' + @Table_name + ' WHERE UserId = ' + @UserID
EXEC(@Command)

It's also risky: it leave you open to SQL Injection attacks if @UserID is changed to a string.

Personally? I wouldn't do it.

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