how to declare database as variable in sql statment

Question

i have problem declaring sql statement (MS SQL) database as variable i have this procedure , anyone can help thanks :)

DECLARE @ID int = 700001158
DECLARE @STRING VARCHAR(250) = 'StringResource_EN'

SELECT string.[value],item.[id] FROM ItemResource item LEFT JOIN **@STRING** string ON item.[name_id] = string.[code]
WHERE item.[id] = @ID

i got Error:

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Must declare the table variable "@STRING". (1087) Blockquote

while it should be this result :

id = 700001158 value = Devildom Purge: Shield

Edited : i'v tried

DECLARE @ID int = 700001158
DECLARE @STRING VARCHAR(250) = 'StringResource_EN'
DECLARE @sql NVARCHAR(1000)

SET @sql =  'SELECT string.[value],item.[id] FROM ItemResource item LEFT JOIN '+@STRING+' string ON item.[name_id] = string.[code]
WHERE item.[id] = @ID'

EXEC sp_executesql @sql;

ALSO I got this

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Must declare the scalar variable "@ID". (137)

Always include the error message also. They are important. And please explain what do you mean by “database as variable” — Sami Kuhmonen
– Sami Kuhmonen, Commented Apr 13, 2019 at 9:54
Also, if @IN_STRING_DB is meant to be a dynamic object, there's no reason to declare it as an nvarchar(255). The longest length an object's name can have is 128 characters, so use nvarchar(128) or sysname. — Thom A
– Thom A ♦, Commented Apr 13, 2019 at 10:08
If you want to declare a database as variable, you have two options: 1) use dynamic sql bystoring the sql command in a variable, or 2) use sqlcmd mode in which you can decalre your database name as variable learn.microsoft.com/en-us/sql/ssms/scripting/… — Kemal AL GAZZAH
– Kemal AL GAZZAH, Commented Apr 13, 2019 at 10:10
To answer your question, in your second block you embedded the string @id in your statement, not the value contained in that variable. Your logic is also incorrect (or your description misleading). The value contained in [at]string must be an object name - you said database name but that isn't correct. Given the terminology issues, you should rethink the decisions that lead to this path. This is beyond your skill level and will be difficult for you to implement and maintain. — SMor
– SMor, Commented Apr 13, 2019 at 11:42

Jim Jimson · Accepted Answer · 2019-04-13 10:51:00Z

2

You're going to need to use dynamic SQL for this one:

ALTER PROCEDURE [dbo].[cpanel_get_warehouse_item]
@IN_ACCOUNT_ID      INT,
@IN_STRING_DB VARCHAR(255),
@ITEM_COUNT         INT OUTPUT,
@GOLD           INT OUTPUT

AS
SET NOCOUNT ON
SET @ITEM_COUNT = (SELECT COUNT(*) FROM [Telecaster].dbo.[Item] WHERE [account_id] = @IN_ACCOUNT_ID AND [code] > 0)
SET @GOLD = (SELECT [cnt] FROM [Telecaster].dbo.[Item] WHERE [account_id] = @IN_ACCOUNT_ID AND [code] = 0 AND [gcode] = 126)

DECLARE @sql NVARCHAR(1000);

SELECT @sql =  'SELECT string.[value] as name,item.[code] as id ,item.[cnt],item.[level],item.[enhance],data.[icon_file_name] as icon
FROM [Telecaster].dbo.[Item] item LEFT JOIN [Arcadia].dbo.[ItemResource] data on data.[id] = item.[code]
LEFT JOIN [Arcadia].dbo.' + @IN_STRING_DB +  'string on data.[name_id] = string.[code]
WHERE item.[account_id] = ' + @IN_ACCOUNT_ID' + AND item.[code] > 0 ORDER BY item.[sid] asc'

EXEC sp_executesql @sql;

And as Milney rightly points out, whenever you use dynamic SQL you should be aware of SQL injection.

edited Apr 13, 2019 at 10:51

answered Apr 13, 2019 at 10:14

Jim Jimson

2,5784 gold badges22 silver badges44 bronze badges

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

6 Comments

Milney Over a year ago

Bearing in mind that if the variable is user controlled in any way this opens you up to SQL Injection attacks: acunetix.com/websitesecurity/sql-injection so you are usually better finding an application level solution to this problem instead

Hatrick Legacy Over a year ago

I did and I got [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Must declare the scalar variable "@ID". (137)

Hatrick Legacy Over a year ago

@Jim Jimson check the post please !! i'v did what you suggested

Thom A Over a year ago

QUOTENAME is your friend. This is an injection issue right now.

Jim Jimson Over a year ago

I've updated. However I can't find anywhere you'd be seeing that error. Can you check your parameters please.

|

Collectives™ on Stack Overflow

how to declare database as variable in sql statment

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related