Must declare the scalar variable error in subselect SQL

Question

I am trying to create a SQL script that will create a nice easy to update script where another person only needs to update the constants involved and then run a query, however I an getting into trouble when using declared variables in sub selects.

I know this is because the declared variables are not in scope of the sub select but cant seem to nut out a workaround.

Here is the code currently:

DECLARE @_logdb varchar(30) = 'nb_tablename';
DECLARE @_sitedb varchar(30) = 'nb_tablename_site';
DECLARE @_backtodate varchar(30) = '2012-10-10';
DECLARE @_sitename varchar(50) = 'bikehaven';

UPDATE
    [@_sitedb].[dbo].[bike] 
SET [bikeStatus] = 'active' 
WHERE bikeID in (
    SELECT 
substring(
    [event], 
    CHARINDEX('bike ID' , [event]) + LEN('bike ID '),
    CHARINDEX(
        ')', 
        [event],
        CHARINDEX('bike ID' , [event])
    ) - (CHARINDEX('bike ID' , [event]) + LEN('bike ID '))
) as 'ID'
from [@_logdb].[dbo].[logs] 
where 
    user_name = 'john' AND 
    event_type = 'Deleted bike' AND 
    CHARINDEX('bike ID' , [event]) > 0 AND 
    date_time > @_backtodate AND 
    siteID = (
        SELECT id 
        from [@_logdb].[dbo].[sites] 
        WHERE site_name = @_sitename
    )
)

The two variables that are actually causing trouble are @_backtodate & @_sitename, but I also know if I substitute those variables with the real string I then get errors saying that [@_sitedb].[dbo].[bike] is also a problem.

Ive done a fair bit of googling, tried using temporary tables for the sub queries as well as defining the SQL as a variable itself and then running an EXEC command on it, but to no avail.

Edit: I've applied the change as directed by Andomar by pulling out the query itself into a declare variable and then running that, which was indeed the fix for one of the errors, however even with that fix I am still getting Must define scalar variable errors for @_back to date & @_sitename.

Solution: The change suggested by Alexander Fedorenko ended up giving me the last bit of the puzzle. Which was table names cannot be used as explicit replacements via parameters in dynamic SQL, where these work perfectly for actual variable values.

In Short: - Make the query a string variable. - Any table name replacements that need to occur you can add via string concatenations (meaning the output string needs to be a valid query) - Pass any variable parameters for substitution to the stored procedure SP_executesql

The final working code is as specified by Alexander Fedorenko.

Answer 1

procedure sp_executesql allows use parameters. Using sp_executesql

DECLARE @_logdb varchar(30) = 'nb_tablename';
DECLARE @_sitedb varchar(30) = 'nb_tablename_site';
DECLARE @_backtodate varchar(30) = '2012-10-10';
DECLARE @_sitename varchar(50) = 'bikehaven';

DECLARE @_SQL nvarchar(max)
SET @_SQL = '
UPDATE
    ' + @_sitedb + '.[dbo].[bike] 
SET [bikeStatus] = ''active'' 
WHERE bikeID in (
    SELECT 
substring(
  [event],
CHARINDEX(''bike ID'' , [event]) + LEN(''bike ID ''),
CHARINDEX(
'')'', 
[event],
CHARINDEX(''bike ID'' , [event])
) - (CHARINDEX(''bike ID'' , [event]) + LEN(''bike ID ''))
) as ''ID''
from ' + @_logdb + '.[dbo].[logs]
where 
user_name = ''john'' AND 
event_type = ''Deleted bike'' AND
CHARINDEX(''bike ID'' , [event]) > 0 AND
date_time > @_backtodate AND
siteID = (
SELECT id 
from ' + @_logdb + '.[dbo].[sites]
WHERE site_name = @_sitename
    )
)'
EXEC sp_executesql @_SQL, N'@_backtodate varchar(30), @_sitename varchar(50)', @_backtodate, @_sitename