Where do I find usage information for sp_execute?

Question

Where do I find usage information for sp_execute?

Every place I look I found an example with a single parameter.

I need the syntax for multiple parameters.

Answer 1

Here are the set up and call.

The select statment at the bottom always shows that p_contid has the appropriate value but I

get a database error that I cannot insert a null in the field contid.

I don't see how contid is null!!!

 select @l_cnt = COUNT(*) from sa.people where CONTID = @p_contid;
  if @l_cnt = 0 
 BEGIN
 -- Build the INSERT statement.
SET @InsertString = N'INSERT INTO sa.people (pid, contid, seq,bd_name,bd_reg_rep,coaddr1,cocity,cocountry,coname,costate,cozip,email,fnm,
func1,func2,func3,func4,func5,job1,job2,job3,job4,job5,job6,job7,job8,job9,job10,job11,job12,lnm,
mkt_focus1,mkt_focus2,mkt_focus3,mkt_focus4,mkt_focus5,mnm,phone,phone_area,sex,soct,sufx,title,yrhired)'+ 
  /* Build a VALUES clause. */
'VALUES (@v_pid, @v_contid, @v_seq, @v_bd_name,@v_bd_reg_rep, @v_coaddr1,@v_cocity,@v_cocountry,@v_coname,@v_costate,@v_cozip,@v_email,@v_fnm,
  @v_func1,@v_func2,@v_func3,@v_func4
  ,@v_func5,@v_job1,@v_job2,@v_job3,@v_job4,@v_job5,@v_job6,@v_job7,@v_job8,@v_job9,@v_job10,@v_job11,@v_job12,@v_lnm,
  @v_mktfocus1,@v_mktfocus2,@v_mktfocus3,@v_mktfocus4,@v_mktfocus5,@v_mnm,@v_phone,@v_phone_area,@v_sex,@v_soct,@v_sufx,@v_title,@v_yrhired)';
<br/>

SET @ParamString = N'@v_pid DECIMAL, @v_contid DECIMAL, @v_seq DECIMAL = null, @v_bd_name NVARCHAR = null, @v_bd_reg_rep NVARCHAR = null
, @v_coaddr1 NVARCHAR = null, @v_cocity NVARCHAR = null, @v_cocountry NVARCHAR = null, @v_coname NVARCHAR = null
, @v_costate NVARCHAR = null, @v_cozip NVARCHAR = null, @v_email NVARCHAR = null, @v_fnm NVARCHAR = null, @v_func1 NVARCHAR = null
, @v_func2 NVARCHAR = null, @v_func3 NVARCHAR = null, @v_func4 NVARCHAR = null, @v_func5 NVARCHAR = null, @v_job1 NVARCHAR = null
, @v_job2 NVARCHAR = null, @v_job3 NVARCHAR = null, @v_job4 NVARCHAR = null, @v_job5 NVARCHAR = null, @v_job6 NVARCHAR = null
, @v_job7 NVARCHAR = null, @v_job8 NVARCHAR = null, @v_job9 NVARCHAR = null, @v_job10 NVARCHAR = null, @v_job11 NVARCHAR = null
, @v_job12 NVARCHAR = null, @v_lnm NVARCHAR = null, @v_mktfocus1 NVARCHAR = null, @v_mktfocus2 NVARCHAR = null, @v_mktfocus3 NVARCHAR = null
, @v_mktfocus4 NVARCHAR = null, @v_mktfocus5 NVARCHAR = null, @v_mnm NVARCHAR = null, @v_phone NVARCHAR = null, @v_phone_area NVARCHAR = null
, @v_sex NVARCHAR = null, @v_soct NVARCHAR = null, @v_sufx NVARCHAR = null, @v_title NVARCHAR = null, @v_yrhired NVARCHAR = null'

select @InsertString 'Command', @ParamString 'Params'
EXEC sp_executesql @InsertString,@ParamString,
@v_pid=@p_pid, @v_contid=@p_contid, @v_seq=@p_seq, @v_bd_name=@p_bd_name, @v_bd_reg_rep=@p_bd_reg_rep, @v_coaddr1=@p_coaddr1, @v_cocity=@p_cocity, 
@v_cocountry=@p_cocountry, @v_coname=@p_coname, @v_costate=@p_costate, @v_cozip=@p_cozip, @v_email=@p_email, @v_fnm=@p_fnm, 
@v_func1=@p_func1, @v_func2=@p_func2, @v_func3=@p_func3, @v_func4=@p_func4, @v_func5=@p_func5, @v_job1=@p_job1, @v_job2=@p_job2, @v_job3=@p_job3, 
@v_job4=@p_job4, @v_job5=@p_job5, @v_job6=@p_job6, @v_job7=@p_job7, @v_job8=@p_job8, @v_job9=@p_job9, @v_job10=@p_job10, @v_job11=@p_job11, @v_job12=@p_job12, 
@v_lnm=@p_lnm, @v_mktfocus1=@p_mktfocus1, @v_mktfocus2=@p_mktfocus2, @v_mktfocus3=@p_mktfocus3, @v_mktfocus4=@p_mktfocus4, @v_mktfocus5=@p_mktfocus5, 
@v_mnm=@p_mnm, @v_phone=@p_phone, @v_phone_area=@p_phone_area, @v_sex=@p_sex, @v_soct=@p_soct, @v_sufx=@p_sufx, @v_title=@p_title, @v_yrhired = @p_yrhired
<br/>

 select @p_pid,@p_contid,@l_cnt
 END
 else

 

 

 

Answer 2

Try using sp_executesql http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 3

As Jeff replied, the sp_executesql http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx topic shows multiple parameters.

For example:

  EXECUTE sp_executesql @SQLString, @ParmDefinition, @SalesID = @IntVariable;