Dynamic SQL server query with variables (Problems executing)

Question

This is a far smaller version of a query that basically needs a variable of the table on which everything is run.

When I run the procedure I get the error message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'table'.

alter procedure james_tester
@tablename nvarchar(200)
as

        BEGIN
        declare @sql nvarchar(max)

        set @sql =  

        '
        select * from ' 

        + @tablename

            EXECUTE sp_executesql @sql

        END

To fix this i tried using things like quotename and played around with the format but nothing seems to have worked yet.

execute james_tester 'dbo.Calendar table' (That is the table I am wanting to query)

TSungur · Accepted Answer · 2017-06-30 09:52:00Z

1

The problem lies in how you call your procedure.

execute james_tester 'dbo.Calendar table'

should be

execute james_tester 'dbo.Calendar'

Hence the error message :

Incorrect syntax near the keyword 'table'.

answered Jun 30, 2017 at 9:52

TSungur

4162 silver badges10 bronze badges

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

Comments

TriV · Accepted Answer · 2017-06-30 09:52:07Z

1

Table is a keyword of sql server. So you couldn't use it as an alias with out []. Try this

execute james_tester 'dbo.Calendar [table]'

or

execute james_tester 'dbo.Calendar t'

or

execute james_tester 'dbo.Calendar'

answered Jun 30, 2017 at 9:52

TriV

5,1582 gold badges12 silver badges18 bronze badges

1 Comment

user4242750 Over a year ago

Thanks. Selected * into a table 'calendar', repeated all steps and everything worked!

Collectives™ on Stack Overflow

Dynamic SQL server query with variables (Problems executing)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related