Developing Matt

My Technical Journal

Dynamic Sql with parameters? Are you crazy?

leave a comment »

Earlier, I discussed how to call a stored procedure within a stored procedure (with a result). Ante up. Dynamic sql can be a real pain to work with: It gets complicated quick, SQL doesn’t manage text well (bloat), and it means you are creating queries that don’t compile and aren’t optimized. But when you need a result from a dynamically built query that
1. takes parameters
2. gives results
3. can be used inside your stored procedure
You can do it with sp_executesql! It takes 3 parameters: your sql string, parameter definition, and parameter output values. Here’s a dummied down example for you:

create procedure [dbo].[dummyReturnLastNameUsing_sp_executesql]
@FullName varchar
(25)
as
begin
declare
@LastName nvarchar(15)
declare @strsql nvarchar(200)
set @strsql = ‘select @lastnameOut = substring(”’ + @fullname + ”’,charindex(” ”,”’+ @fullname + ”’), len(”’ + @fullname + ”’)-charindex(” ”,”’ + @fullname + ”’)+1)’
declare @parmDefinition nvarchar(40)
set @parmDefinition = N’@lastnameOut varchar(30) Output’
execute sp_executesql @strsql,@parmDefinition,@lastnameout = @lastname output
select @lastname

Watch out for the nvarchar vs. the varchars. It’s a gotcha that’ll getcha with sp_executesql.

Advertisements

Written by matt

July 19, 2007 at 7:44 am

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: