SQL Server存储过程错误！！！ [英] SQL Server Stored Procedure Error!!!

问题描述

在创建新的存储过程存储过程时，在'+ @Items +'附近出现错误:不正确的语法.我正在附上代码，

I am getting an error as :Incorrect Syntax near '' + @Items + '' while making a new stored procedure stored procedure. I am attaching the code,

-- =============================================
-- Author:      <Varun Sareen>
-- Create date: <04/05/2010>
-- Description: <Item L5 Description Search>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Search_Item_L5_Desc]
    -- Add the parameters for the stored procedure here
    @strSearchKeyword varchar(200),
    @Current_Logged_User int,
    @Items sysname,  
    @Unit_Of_Measurement sysname,
    @Source sysname,
    @User_Default_Sources sysname,
    @User_Default_Options sysname,
    @Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn sysname

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- SET NOCOUNT ON;
SET @Items=RTRIM(@Items)
SET @Unit_Of_Measurement=RTRIM(@Unit_Of_Measurement)
SET @Source=RTRIM(@Source)
SET @User_Default_Sources=RTRIM(@User_Default_Sources)
SET @User_Default_Options=RTRIM(@User_Default_Options)
SET @strSearchKeyword=RTRIM(@strSearchKeyword)
SET @Current_Logged_User=RTRIM(@Current_Logged_User)
SET @Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn=RTRIM(@Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn)
    -- Insert statements for procedure here
Declare @temp_ItemsL5 Table
(
item_id int,
item_level5_code varchar(10),
item_short_description varchar(max),
item_long_description varchar(max),
unit_symbol varchar(25),
urc_code varchar(50),
source_code varchar(50),
item_level_id int,
number_of_units decimal(18,9),
cost_of_one_unit decimal(30,9),
net_item_amount decimal(30,9)
)

insert into @temp_ItemsL5
select a.item_id,a.item_level5_code,a.item_short_description,a.item_long_description,b.unit_symbol, a.urc_code,c.source_code,a.item_level_id,a.number_of_units,a.cost_of_one_unit,a.net_item_amount
from ' + @Items + ' a
inner join ' + @Unit_Of_Measurement + ' b on a.unit_id=b.unit_id
inner join ' + @Source + ' c on c.source_id=a.source_id
inner join ' + @User_Default_Sources + ' uds on uds.source_id = c.source_id
inner join ' + @User_Default_Options + ' udo on udo.user_default_option_id = uds.user_default_option_id
inner join ' + @Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn + '(@strSearchKeyword,' ') f on a.item_id=f.tempId
where udo.user_id= ' + @Current_Logged_User + ' order by f.Id

END

``@Items''是作为参数传递的表名

谢谢

Varun Sareen

''@Items'' is table name passed as a parameter

Thanks

Varun Sareen

推荐答案

问题出在您的sql-string中.

The problem is situated in your sql-string.

Varun Sareen写道:

Varun Sareen wrote:

来自"+ @Items +" a
内部连接''+ @Unit_Of_Measurement +''a.unit_id = b.unit_id
上的b 内部连接''+ @Source +''c.source_id = a.source_id
上的c 内部联接''+ @User_Default_Sources +''uds.source_id = c.source_id
上的uds 内部连接''+ @User_Default_Options +''udo上的udo.user_default_option_id = uds.user_default_option_id
内部连接''+ @ a.item_id = f.tempId
上的@ Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn +''(@strSearchKeyword，'''')f 其中udo.user_id =''+ @Current_Logged_User +''按f.Id排序

from '' + @Items + '' a
inner join '' + @Unit_Of_Measurement + '' b on a.unit_id=b.unit_id
inner join '' + @Source + '' c on c.source_id=a.source_id
inner join '' + @User_Default_Sources + '' uds on uds.source_id = c.source_id
inner join '' + @User_Default_Options + '' udo on udo.user_default_option_id = uds.user_default_option_id
inner join '' + @Tbl_Fn_Search_Keyword_Iems_1P_Keyword_2P_SplitOn + ''(@strSearchKeyword,'' '') f on a.item_id=f.tempId
where udo.user_id= '' + @Current_Logged_User + '' order by f.Id

您不能只是像这样构建您的SQL字符串.

看看
http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong [

You can''t just build your SQL-string like this.

have a look at
http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong[^]

Short example:

declare @kolom as nvarchar(20)
declare @xpwhere as nvarchar(100)
declare @res as nvarchar(max)
declare @params nvarchar(4000)

set @kolom = 'AdresID'
set @xpwhere = 'AdresID = 3'

declare @sql as nvarchar(255)

select @params = N'@result nvarchar(max) output'
set @sql = 'SELECT @result = ' + @kolom + ' FROM dbo.Adressen WHERE ' + @xpwhere
exec sp_executesql @sql, @params, @result=@res output
print @res

Varun Sareen写道:

Varun Sareen wrote:

来自"+ @Items +" a

from '' + @Items + '' a

这应该只是from a.

This should just be from a.

这篇关于SQL Server存储过程错误！！！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！