Appending string to SQL Server stored procedure

Question

I'm trying to append table name dynamically to SQL Server stored procedure, but I get an error:

couldn't find the stored procedure 's'

Code:

SET ANSI_NULLS ON
GO    
SET QUOTED_IDENTIFIER ON    
GO     

ALTER PROCEDURE [dbo].[atest_demo_sp]    
    @name varchar(10)     
AS     
BEGIN     
    Declare @tname nvarchar    
    Declare @sql nvarchar    

    set @tname = @name           
    Set @sql = 'select * from ' + convert(varchar(10), @tname)    

    -- select @sql     
    execute sp_executesql @sql     
END

error message you posted ,doesn't relate to the code you are showing. — TheGameiswar
– TheGameiswar, Commented Mar 24, 2016 at 5:58

Community · Accepted Answer · 2020-07-29 18:26:20Z

2

You need to explicitly define a length for your string variables!

SET ANSI_NULLS ON
GO    
SET QUOTED_IDENTIFIER ON    
GO     

ALTER PROCEDURE [dbo].[atest_demo_sp]    
    @name varchar(10)     
AS     
BEGIN     
    Declare @tname nvarchar(200)    -- DEFINE A LENGTH HERE !!!
    Declare @sql nvarchar(200)      -- DEFINE A LENGTH HERE !!!

    set @tname = @name           
    Set @sql = N'select * from ' + convert(nvarchar(10), @tname)    

    -- select @sql     
    execute sp_executesql @sql     
END

Otherwise, your variables @tname and @sql will be exactly 1 character long! Probably not what you want!

See this blog post for more details: Bad habits to kick : declaring VARCHAR without (length)

Also, as a side note: either pick varchar (non-Unicode), or use nvarchar (Unicode) - but don't mix the two. And if you use nvarchar, you should always use the N'...' notation for assigning string literals.

edited Jul 29, 2020 at 18:26

CommunityBot

11 silver badge

answered Mar 24, 2016 at 5:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arulkumar · Accepted Answer · 2016-03-24 06:13:32Z

1

The issue with the set @tname = @name, no need to assign the varchar to nvarchar here. Avoid the @tname variable and directly use @name in the SET statement since it is already varchar.

Hope the @name's first character only assigning to @tname. Also need to define the @sql to some length.

Use the below statement, it will work:

Declare @sql varchar(100);
Set @sql = 'select * from ' + @name

edited Mar 24, 2016 at 6:13

answered Mar 24, 2016 at 5:55

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Collectives™ on Stack Overflow

Appending string to SQL Server stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related