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

Eric · Accepted Answer · 2009-06-02 20:37:40Z

8

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.

edited Jun 2, 2009 at 20:37

answered Jun 2, 2009 at 19:51

Eric

95.7k13 gold badges117 silver badges116 bronze badges

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

3 Comments

Eoin Campbell Over a year ago

exec (@q) will also work. You just need to surround your @var in parenthesis

KM. Over a year ago

this will not work: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Eric Over a year ago

@KM: Fixed it to be nvarchar. I forget this dependency of sp_executesql all the time. As a rule, you should be using nvarchar, anyway, though. Don't assume ASCII will be able to cover all possible characters!

tekBlues · Accepted Answer · 2009-06-02 19:55:15Z

5

You also can use

exec(@q)

this will execute the literal string.

The difference is in the parenthesis!

answered Jun 2, 2009 at 19:55

tekBlues

5,8031 gold badge31 silver badges32 bronze badges

2 Comments

Eric Over a year ago

I'm not a fan of this method for readability reasons. It makes it tough, especially when you have a stored proc calling a bunch of SQL and other stored procs. Using sp_executesql keeps these separate enough, at least for me. But, that's just my preference.

tekBlues Over a year ago

Eric, I agree, but for me it's easier to remember, sometimes I forget sp_executesql.

KM. · Accepted Answer · 2009-06-02 19:56:49Z

0

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

execute (@q)

answered Jun 2, 2009 at 19:56

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

maxbeaudoin · Accepted Answer · 2009-06-02 20:01:06Z

0

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

answered Jun 2, 2009 at 20:01

maxbeaudoin

7,0165 gold badges41 silver badges55 bronze badges

Comments

Christopher Klein · Accepted Answer · 2009-06-02 20:06:50Z

0

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

answered Jun 2, 2009 at 20:06

Christopher Klein

2,8035 gold badges40 silver badges65 bronze badges

Comments

HLGEM · Accepted Answer · 2009-06-02 20:54:10Z

0

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)

answered Jun 2, 2009 at 20:54

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

How can I fix this SQL error on this query

6 Answers 6

3 Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related