How can I fix this SQL error on this query

Question

DECLARE @providerIdList varchar(400)
DECLARE @q varchar(400)

SELECT @q  =  ''

SELECT @providerIdList = '(1, 5, 15)'


SET @q = 'SELECT u.Id FROM [user] u
    LEFT JOIN Provider p ON u.Provider_FK = p.Id
    LEFT JOIN  Providers2Users pu ON pu.user_FK = u.Id 
    LEFT JOIN Provider ap ON ap.Id = pu.provider_fk
    WHERE p.Id  IN ' + @providerIdList




exec @q

Here is the exception that I am getting when I tried to execute query shown above

---

Msg 203, Level 16, State 2, Line 18
The name 'SELECT u.Id FROM [user] u
    LEFT JOIN Provider p ON u.Provider_FK = p.Id
    LEFT JOIN  Providers2Users pu ON pu.user_FK = u.Id 
    LEFT JOIN Provider ap ON ap.Id = pu.provider_fk
    WHERE p.Id  IN (1, 5, 15)' is not a valid identifier.

---

I would really appreciate if somebody can point me the cause of these errors

Answer 1

You're looking for sp_executesql. Do this instead:

DECLARE @providerIdList nvarchar(400)
DECLARE @q nvarchar(400)

SELECT @q  =  N''

SELECT @providerIdList = N'(1, 5, 15)'


SET @q = N'SELECT u.Id FROM [user] u
    LEFT JOIN Provider p ON u.Provider_FK = p.Id
    LEFT JOIN  Providers2Users pu ON pu.user_FK = u.Id 
    LEFT JOIN Provider ap ON ap.Id = pu.provider_fk
    WHERE p.Id  IN ' + @providerIdList

exec sp_executesql @q

What you're doing now is trying to invoke the command as a stored procedure, which it's clearly not. sp_executesql is a system stored procedure, which allows you to execute a valid SQL statement. The system stored procedures are on the master database, FYI.

Answer 2

You also can use

exec(@q)

this will execute the literal string.

The difference is in the parenthesis!

Answer 3

your trying to execute a stored procedure @q, which is not valid, use:

execute (@q)

Answer 4

Ok make sure you use NVARCHAR not VARCHAR. Just then you might use EXEC sp_executesql @q.

Answer 5

Sorry...I can't comment yet because of my rep...but using what Eric said, you would have to change your variables to Nvarchar.

Answer 6

declare @params NVARCHAR(4000)
declare @sql NVARCHAR(4000)

SET @providerIdList = '(1, 5, 15)'

SET @sql = 'SELECT u.Id FROM [user] u LEFT JOIN Provider p ON u.Provider_FK = p.Id LEFT JOIN Providers2Users pu ON pu.user_FK = u.Id LEFT JOIN Provider ap ON ap.Id = pu.provider_fk WHERE p.Id IN ' + @providerIdList'

SELECT @params = N'@providerIdList VARCHAR OUTPUT'

exec sp_executesql @sql, @params,@providerIdList=@providerIdList

Answer 7

Or given what you show as your code, I wouldn't use dynamic SQL at all. Dynamic SQl should be avoided if possible.

SELECT u.Id FROM [user] u    
LEFT JOIN Provider p ON u.Provider_FK = p.Id    
LEFT JOIN  Providers2Users pu ON pu.user_FK = u.Id     
LEFT JOIN Provider ap ON ap.Id = pu.provider_fk    
WHERE p.Id  IN (1, 5, 15)