Multiple Columns in Dynamic Sql Error

Question

When i do this

declare @cmd nvarchar(50)
set @cmd ='SELECT category_id FROM tbl_BHMCategoryMaster '
exec(@cmd)

query executes properly.

But When I do this

declare @cmd nvarchar(50)
set @cmd ='SELECT [category_id],[category_name],[Basic],[Dimension],[In_Out],[ProfileHand],[Glass],[Hinge],[Edgeband]  FROM [tbl_BHMCategoryMaster] ' 
exec(@cmd)

I get this error and i have no clue what i am doing wrong

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Dime'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Dime'.

But again when i tried this

 exec('SELECT [category_id],[category_name],[Basic],[Dimension],[In_Out],[ProfileHand],[Glass],[Hinge],[Edgeband]  FROM [tbl_BHMCategoryMaster]')

it worked properly.

There may be many alternative to this but i want to know the problem in detail so that i don't face it next time. If anyone can put some light it will be of great help. Thanks :)

Your nvarchar needs to be bigger than (50)

Laurence
– Laurence

2013-09-15 14:11:11 +00:00
Commented Sep 15, 2013 at 14:11 — Laurence
– Laurence, Commented Sep 15, 2013 at 14:11
thanks for pointing out the mistake Laurence :)

Subhojit Mukherjee
– Subhojit Mukherjee

2013-09-15 14:19:15 +00:00
Commented Sep 15, 2013 at 14:19 — Subhojit Mukherjee
– Subhojit Mukherjee, Commented Sep 15, 2013 at 14:19

Gordon Linoff · Accepted Answer · 2013-09-15 14:11:25Z

2

You haven't declared @cmd to be long enough. Try this:

declare @cmd nvarchar(max);
set @cmd = N'SELECT [category_id],[category_name],[Basic],[Dimension],[In_Out],[ProfileHand],[Glass],[Hinge],[Edgeband]  FROM [tbl_BHMCategoryMaster] ' ;
exec(@cmd);

answered Sep 15, 2013 at 14:11

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Subhojit Mukherjee Over a year ago

that was so lame of me..Anyways thanks for pointing out the mistake Gordon :)

Collectives™ on Stack Overflow

Multiple Columns in Dynamic Sql Error

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related