如何使用参数执行存储过程？ [英] How to execute a stored procedure with parameters?

问题描述

我有一个包含40个表的数据库。我想通过使用单个存储过程从某个表中选择数据。第一个表（TblSPAU1）有6列，名为：ID，COL_SPAU1_EA，COL_SPAU1_EQ，COL_SPAU1_ore，COL_SPAU1_nivel，DateTime。例如，第四个表（TblSPAU4）具有以下6列：ID，COL_SPAU4_EA，COL_SPAU4_EQ，COL_SPAU4_ore，COL_SPAU4_nivel，DateTime。所以我想要的是从表格X中选择从DateStart到DateStop的数据。



我想从某个表中选择ID和第一列。 @val表示我想从中选择数据的表的编号。在sp_sqlexec上我有一个工具提示，说我有太多的参数指定。当我执行EXEC时，它会抛出这个错误： Msg 137，Level 15，State 2，Line 1.必须声明标量变量@sql。我的问题是我该如何执行这个存储过程？非常感谢提前！



我的尝试：

 USE [DBRapBreaza] 
 GO 
 / ******对象：StoredProcedure [dbo]。[PS_SpauOPompa]脚本日期：12/19/2018 15:48:38 ** **** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 ALTER PROCEDURE [dbo]。[PS_SpauOPompa] 
 
 @DataStart datetime，
 @DataStop datetime，
 @val int 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
声明@sql NVARCHAR（最大）
声明@ col1 varchar 
 set @ col1 ='ID'
声明@col2 varchar（25）
 set @ col2 ='COL_SPAU'+ CONVERT（VARCHAR，@ val）+'_ EA'
声明@ col3 varchar 
 set @ col3 ='DateTime'
 
 set @sql ='选择[ID]'+ @ col2 +'FROM [DBRapBreaza]。[dbo]。[TblSPAU'+ CONVERT（VARCHAR，@ value）+'] WHERE DateTime'+ CONVERT（VARCHAR（25），@ DataStart，121）+ '和'+ CONVERT（VARCHAR（25），@ DataStop，121）+';'
 
 END 
 
 EXEC sp_sqlexec @sql，N'@ DataStart datetime，@ DataStop datetime，@ val int'，@ dataStart，@ dataStop，@ val 

解决方案

你还没有在@sql体外声明@sql存储过程。在SP所有你正在做的是设置一个变量并且不做任何事情。



尝试移动 END 之后 EXEC



编辑：另一个想法 - 看起来像一个数据库设计很差。



首先，您似乎将 DateTime 列作为类型​​ varchar 。使用适当的列类型（即 date 或 datetime ）。



避免对列名使用保留字，但如果你记得将它们括在方括号中，例如 [DateTime]



为什么没有一个表有一个额外的列来区分不同的类型然后整个SP变成

 选择 [ID]，COL_SPAU_EA  FROM  [DBRapBreaza]。[dbo]。[TblSPAU] 
  WHERE   DateTime     @ DataStart  和  @ DataStop  
  AND  SPAU_TYPE =  @ val ; 

编辑：

嗯，你声称没有任何变化我ge当我将END移动到正确的位置时，一组完全不同的错误。但我很抱歉认为 datetime 列是一个varchar - 我误读了代码设置 @sql 。



但是，我刚刚意识到你正在使用 sp_sqlexec - 将其更改为 sp_executesql - 微软推荐后者超过前者。



那么你将在SQL中出现语法错误 - 你需要用引号并在之间和和之后包含一些空格，例如

  set  @sql = ' 选择[ID]' + @ col2 + '  FROM [DBRapBreaza]。[dbo]。[TblSPAU' + CONVERT（ VARCHAR ， @ val ）+ ' ] WHERE DateTime''' + CONVERT（ VARCHAR （ 25 ）， @ DataStart ， 121 ）+ ' ''和''' + CONVERT（ VARCHAR  （ 25 ）， @ DataStop ， 121  ）+ ' '';' 

回复你的评论

引用：

正如我所提到的，我有40个表（TblSPAU1 ... TblSPAU40），其中包含上面提到的列。 DateTime在DB中声明为datetime。上面代码中的SPAU_TYPE是谁？

我试图给你一个替代的数据库设计......而不是40个表只有一个表。我的示例中的SPAU_TYPE是介于1和40之间的值...因此SPAU_TYPE = 1的行将是您当前在TblSPAU1中拥有的行，SPAU_TYPE = 2的行将是您当前在TblSPAU2中的行等。这是一个更好的设计，更容易使用和维护。



顺便说一句，这是一个主观的观点，但大多数有经验的程序员避免使用表名前缀带Col_的Tbl或列名称。它没有为文档添加任何内容，导致额外的输入并导致问题进一步发生......例如，如果您需要用View替换最初的表格，该怎么办？您需要做很多工作才能将TblSPAU更改为VwSPAU而无实际好处。





编辑2 - 完整代码我一直用来测试这个

  ALTER   PROCEDURE  [ dbo]。[PS_SpauOPompa] 
 
  @ DataStart   datetime ，
  @ DataStop   datetime ，
  @ val   int  
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
 
 声明  @sql   NVARCHAR （max）
 声明  @ col1   varchar  
  set  @ col1 = '  ID' 
 声明  @ col2   varchar （ 25 ）
  set  @ col2 = < span class =code-string>'  COL_SPAU' + CONVERT（ VARCHAR ，< span class =code-sdkkeyword> @ val ）+ '  _ EA' 
 声明  @ col3   varchar  
  set  @ col3 = '  DateTime' 
 
  set  @sql = ' 选择[ ID]，' + @ col2 + '  FROM [dbo]。[TblSPAU' + CONVERT （ VARCHAR ， @ val ）+ ' ] WHERE DateTime''' + CONVERT（ VARCHAR （ 25 ）， @ DataStart ， 121 ）+ ' ''和''' + CONVERT（ VARCHAR （ 25 ）， @ DataStop ， 121 ）+  '';' 
  PRINT   @ SQL  
  EXEC  sp_executesql  @ sql ，N '   @ DataStart datetime，@ DataStop datetime，@ val int'， @ DataStart ， @ DataStop ， @ val  
 
  END  

I have a database with 40 tables. I want to select data from a certain table by using a single stored procedure. First table (TblSPAU1) has 6 columns, named: ID, COL_SPAU1_EA, COL_SPAU1_EQ, COL_SPAU1_ore, COL_SPAU1_nivel, DateTime. Fourth table (TblSPAU4), for example, has this 6 columns: ID, COL_SPAU4_EA, COL_SPAU4_EQ, COL_SPAU4_ore, COL_SPAU4_nivel, DateTime. So what I want is to select data from table X from DateStart to DateStop.

I want to select, let's say, the ID and fist column from a certain table. @val represents the number of the table I want to select data from. On sp_sqlexec I have a tool-tip which says that I have too many arguments specified. When I execute the EXEC, it throws me this error: Msg 137, Level 15, State 2, Line 1. Must declare the scalar variable "@sql". My question is how should I execute this stored procedure? Many thanks in advance!

What I have tried:

USE [DBRapBreaza]
GO
/****** Object:  StoredProcedure [dbo].[PS_SpauOPompa]    Script Date: 12/19/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PS_SpauOPompa]

@DataStart datetime,
@DataStop datetime,
@val int
AS
BEGIN
SET NOCOUNT ON;

declare @sql NVARCHAR(max)
declare @col1 varchar
set @col1='ID'
declare @col2 varchar(25) 
set @col2='COL_SPAU'+CONVERT(VARCHAR, @val)+'_EA' 
declare @col3 varchar 
set @col3='DateTime'

set @sql='select [ID]'+@col2+' FROM [DBRapBreaza].[dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between'+CONVERT(VARCHAR(25), @DataStart,121)+ 'and'+CONVERT(VARCHAR(25), @DataStop,121)+';'

END

EXEC sp_sqlexec @sql, N'@DataStart datetime,@DataStop datetime,@val int',  @DataStart, @DataStop, @val

解决方案

You have not declared @sql outside the body of the stored procedure. In the SP All you are doing is setting up a variable and doing nothing with it.

Try moving the END to after the EXEC

Edit: Another thought - that looks like a very poor database design.

Firstly you appear to have your DateTime column as type varchar. Use the appropriate column type (i.e. either date or datetime).

Avoid using reserved words for column names but if you do remember to enclose them in square brackets e.g.[DateTime]

Why not have a single table with an additional column that differentiates between the different types then the entire SP becomes

select [ID], COL_SPAU_EA FROM [DBRapBreaza].[dbo].[TblSPAU]
 WHERE DateTime between @DataStart and @DataStop 
AND SPAU_TYPE = @val;

EDIT:
Well, you claim nothing changes yet I get a completely different set of errors when I moved the END to the correct place. But my apologies for thinking the datetime column was a varchar - I misread the code setting up @sql.

However, I've just realised that you are using sp_sqlexec - change that to sp_executesql - Microsoft recommend the latter over the former.

Then you are going to get syntax errors in your SQL - you need to surround the dates with quotes and include some spaces after "between" and around "and" e.g.

set @sql='select [ID]'+@col2+' FROM [DBRapBreaza].[dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between '''+CONVERT(VARCHAR(25), @DataStart,121)+ ''' and '''+CONVERT(VARCHAR(25), @DataStop,121)+''';'

In response to your comment

Quote:

As I mentioned, I have 40 tables (TblSPAU1...TblSPAU40) which contains the columns mentioned above. DateTime is declared in DB as datetime. And who's SPAU_TYPE in your above code?

I was trying to give you an alternative database design... instead of 40 tables have just ONE table. SPAU_TYPE in my example would be a value between 1 and 40 … so the rows with SPAU_TYPE = 1 would be the rows you currently have in your TblSPAU1, those rows with SPAU_TYPE = 2 would be the rows you currently have in your TblSPAU2, etc. It's a much better design that is considerably easier to work with and maintain.

As an aside, it is a subjective view, but most experienced programmers avoid prefixing table names with Tbl or column names with Col_. It adds nothing to the documentation, causes extra typing and cause issues further down the line … for example, what if you need to replace what was originally a table with a View? You would have to do a lot of work to change TblSPAU to VwSPAU for no real benefit.


Edit 2 - the full code of the SP that I've been using to test this

ALTER PROCEDURE [dbo].[PS_SpauOPompa]

@DataStart datetime,
@DataStop datetime,
@val int
AS
BEGIN
SET NOCOUNT ON;

declare @sql NVARCHAR(max)
declare @col1 varchar
set @col1='ID'
declare @col2 varchar(25) 
set @col2='COL_SPAU'+CONVERT(VARCHAR, @val)+'_EA' 
declare @col3 varchar 
set @col3='DateTime'

set @sql='select [ID],'+@col2+' FROM [dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between '''+CONVERT(VARCHAR(25), @DataStart,121)+ ''' and '''+CONVERT(VARCHAR(25), @DataStop,121)+''';'
PRINT @SQL
EXEC sp_executesql @sql, N'@DataStart datetime,@DataStop datetime,@val int',  @DataStart, @DataStop, @val

END

这篇关于如何使用参数执行存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！