SQL Server Stored Procedure Error!!!

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

C++

-- =============================================
-- 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' is table name passed as a parameter

Thanks

Varun Sareen

Posted 4-May-10 1:47am

Varun Sareen

Updated 4-May-10 1:49am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2010-05-04T02:09:00

Solution 2

Varun Sareen wrote:
from ' + @Items + ' a

This should just be from a.

Posted 4-May-10 2:09am

Abhinav S

Comments

Tom Deketelaere 4-May-10 8:14am

Actually the 'a' is his alias for the table name passed in the @items parameter.

Abhinav S 4-May-10 10:41am

Ah I missed that....Thanks.

Varun Sareen 5-May-10 0:45am

then where will we define the alias name for the table with parameter name '@Items'

Tom Deketelaere · Answer 2 · 2010-05-04T02:08:00

The problem is situated in your sql-string.

Varun Sareen wrote:
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

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:

SQL

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