I need to build dynamic query in sp based on some conditions. The part of the sp is as follows
Declare @query varchar(Max)
Declare @au varchar(100)
set @au='s'
Set @query='Select t.Title,t.Price, (a.au_Fname + a.au_Lname) as Author FROM titles t Join titleauthor ta on t.title_id=ta.title_id Join authors a on ta.au_id=a.au_id Where (a.au_FName + a.au_Lname) Like ''%' + @au +'%'''
Print @query
Execute @query
The print statement is giving me correct query but Execute @query is throwing below error .
Msg 203, Level 16, State 2, Line 6
The name 'Select t.Title,t.Price, (a.au_Fname + a.au_Lname) as Author FROM titles t Join titleauthor ta on t.title_id=ta.title_id Join authors a on ta.au_id=a.au_id Where (a.au_FName + a.au_Lname) Like '%s%'' is not a valid identifier.
got solution. Need to use Exec SP_EXECUTESQL @query insteadof Exec @query .
Declare @query nvarchar(Max)
Declare @au varchar(100)
set @au='s'
Set @query='Select t.Title,t.Price, (a.au_Fname + a.au_Lname) as Author FROM titles t Join titleauthor ta on t.title_id=ta.title_id Join authors a on ta.au_id=a.au_id Where (a.au_FName + a.au_Lname) Like ''%' + @au +'%'''
Print @query
Exec SP_EXECUTESQL @query
srikar1
Member
82 Points
23 Posts
Unable to write SQL dynamic query in sp
May 07, 2012 05:14 AM|LINK
HI All,
I need to build dynamic query in sp based on some conditions. The part of the sp is as follows
Declare @query varchar(Max)
Declare @au varchar(100)
set @au='s'
Set @query='Select t.Title,t.Price, (a.au_Fname + a.au_Lname) as Author FROM titles t Join titleauthor ta on t.title_id=ta.title_id Join authors a on ta.au_id=a.au_id Where (a.au_FName + a.au_Lname) Like ''%' + @au +'%'''
Print @query
Execute @query
The print statement is giving me correct query but Execute @query is throwing below error .
Msg 203, Level 16, State 2, Line 6
The name 'Select t.Title,t.Price, (a.au_Fname + a.au_Lname) as Author FROM titles t Join titleauthor ta on t.title_id=ta.title_id Join authors a on ta.au_id=a.au_id Where (a.au_FName + a.au_Lname) Like '%s%'' is not a valid identifier.
Bhavik Solu...
Member
746 Points
159 Posts
Re: Unable to write SQL dynamic query in sp
May 07, 2012 05:20 AM|LINK
nice link same problme:)
http://www.sqlservercentral.com/Forums/Topic757493-338-1.aspx
bakamaru
Member
128 Points
76 Posts
Re: Unable to write SQL dynamic query in sp
May 07, 2012 06:07 AM|LINK
Is there any table on database ?that youve mentioned earlier ? you have to use exec (@query)
tusharrs
Contributor
3230 Points
668 Posts
Re: Unable to write SQL dynamic query in sp
May 07, 2012 06:12 AM|LINK
Instead of Execute @query
use this exec (@query)
( Mark as Answer if it helps you out )
View my Blog
bakamaru
Member
128 Points
76 Posts
Re: Unable to write SQL dynamic query in sp
May 07, 2012 06:14 AM|LINK
Is there any table on database ?that youve mentioned earlier ? you have to use exec (@query)
srikar1
Member
82 Points
23 Posts
Re: Unable to write SQL dynamic query in sp
May 07, 2012 07:32 AM|LINK
got solution. Need to use Exec SP_EXECUTESQL @query insteadof Exec @query .
Declare @query nvarchar(Max)
Declare @au varchar(100)
set @au='s'
Set @query='Select t.Title,t.Price, (a.au_Fname + a.au_Lname) as Author FROM titles t Join titleauthor ta on t.title_id=ta.title_id Join authors a on ta.au_id=a.au_id Where (a.au_FName + a.au_Lname) Like ''%' + @au +'%'''
Print @query
Exec SP_EXECUTESQL @query
Thank you for your help.