Technical Article

Hassle free parameters in dynamic SQL

,

I don't know about you, but passing parameters to sp_executesql via variables was never my favorite (I'm sorry if it was/is yours).

The above script offers a dynamic keyword replacement in a query string, by storing the keyword-value pairs in a @keywords table (for one-time use, which can be changed to permanent table for re-use).

The @sql string represents a simple select from the temporary table created on top.

The placeholders in the @sql string are delimited by '<%' and '%>' and are replaced by values in the @keywords table by matching the keyword with the placeholders:

select @sql=replace(@sql,'<%'+keyword+'%>',value) from @keywords...

Besides the fact that the keywords can be stored permanently and reused, I find it pretty cool that there is no need to iterate over the @sql string to replace all the placeholders. 

set nocount on
if isnull(object_id('tempdb.dbo.#t'),0)<>0
drop table #t
create table #t (column1 int,column2 int,column3 int)
declare @i int
set @i=1
while @i<=10
begin
insert #t select @i,@i+1,@i+2
set @i=@i+1
end

select * from #t

declare @sql nvarchar(1024)
set @sql='select <%col1%>,<%col2%> from <%table%> where <%col_where_1%>=<%col_where_1_val%>'

declare @keywords table(keyword varchar(255),value varchar(1024))
insert @keywords select 'table','#t'
insert @keywords select 'col1','column1'
insert @keywords select 'col2','column2'
insert @keywords select 'col3','column3'
insert @keywords select 'col_where_1','column3'
insert @keywords select 'col_where_1_val',6

select @sql=replace(@sql,'<%'+keyword+'%>',value) from @keywords where charindex('<%'+keyword+'%>',@sql)>1
print @sql
exec sp_executesql @sql
drop table #t
set nocount off

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.13 (16)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.13 (16)

You rated this post out of 5. Change rating

Related content