question

MicahHolmes-1650 avatar image
0 Votes"
MicahHolmes-1650 asked ErlandSommarskog answered

SQL Dynamic SQL Help: Msg 203, Level 16, State 2, Line 37

building a dynamic SQL and getting a error. No idea why keeps erroring:

DECLARE @Query AS NVARCHAR(MAX) = 'SELECT ' + @Rune + ' FROM [White_Box_Gaming].[dbo].[Inventory_Runes] WHERE Email = ''' + @Email + ''''

Also tried:

 DECLARE @Query AS NVARCHAR(MAX) = 'SELECT ' + @Rune + ' FROM [White_Box_Gaming].[dbo].[Inventory_Runes] WHERE Email = ''' + @Email + ''

Msg 203, Level 16, State 2, Line 37
The name 'SELECT Rune_ri FROM [White_Box_Gaming].[dbo].[Inventory_Runes] WHERE Email = 'uokgames@gmail.com' is not a valid identifier.

Soo annoying.




sql-server-general
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Which statement gives this error?

0 Votes 0 ·

Please read the response above. It will give you all the information. I'm just trying to perform a simple Dynamic SELECT statement and insert the result into a variable.

0 Votes 0 ·

What code did you use before this fix?


0 Votes 0 ·
MicahHolmes-1650 avatar image
0 Votes"
MicahHolmes-1650 answered

SOLVED:

DECLARE @Result_1 TABLE (ss BIGINT)
DECLARE @Query AS NVARCHAR(MAX) = 'SELECT ' + @Rune + ' FROM [White_Box_Gaming].[dbo].[Inventory_Runes] WHERE Email = ''' + @Email + ''''
INSERT INTO @Result_1 EXEC sp_executesql @Query

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

This is a better solution:

DECLARE @Result_1 TABLE (ss BIGINT)
DECLARE @Query AS NVARCHAR(MAX) = 
   'SELECT ' + @Rune + ' FROM [White_Box_Gaming].[dbo].[Inventory_Runes] WHERE Email = @Email'
INSERT INTO @Result_1 EXEC sp_executesql @Query, N'@Email nvarchar(50)', @Email

You should never inline parameter values into queries. It is better from all aspects to use parameterised statements.

I don't know what is in this @Rune, but may be possible to deal with it in a similar way.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.