calling exec withi a stored procedure

• John Kress

  SSC-Addicted

  Points: 400

  More actions

  February 6, 2006 at 5:59 pm

  #170879

  I am using a stored procedure to retrieve a set of records into a #temp table. Once I have the records I traverse through them in a loop, executing other stored procedures.

  Within a procedure that I exec, there is a varchar that is included as one of the parameters. I am using the following to code to execute the procedure "usp_MyProc" which has 2 parameters @SchID int and @UpdatedBy varchar(39).

   

  SET @mySQL = 'usp_MyProc'+convert(nvarchar(20), @SchoolID)+', '+char(39)+'DATA IMPORT'+char(39)
  
  EXEC (@mySQL)
  

   

  Is there a better or a preferred method to surround the varchar paramenter with single quotes (')?

  Thanks,

  John

• Paul Mu

  SSCertifiable

  Points: 5547

  More actions

  February 6, 2006 at 6:39 pm

  #619144

  Not really! But the other alternatives (apart from char(39) that you've used) include QUOTENAME() and to double the single quote.

• Ken Johnson-162971

  Ten Centuries

  Points: 1296

  More actions

  February 6, 2006 at 9:28 pm

  #619151

  Hi John,

  You could use sp_executeSQL and pass your parameters in separately, withoug worrying about the extra enclosing quotes...

  SET @mySQL = N'EXEC usp_MyProc @SchID, @UpdatedBy'

  SET @params = '@SchID nvarchar(20), @UpdatedBy nvarchar(20)'

  SET @paramSchID = Convert(nvarchar(20),@SchoolID)

  EXEC sp_executesql @mySQL, @params, @paramSchID, 'DATA IMPORT'

• vadba

  SSChampion

  Points: 11133

  More actions

  February 8, 2006 at 7:42 am

  #619524

  SET @mySQL = 'usp_MyProc ' + Convert(nvarchar(20), @SchoolID) + ', ''' + 'DATA IMPORT' + ''''

  --or--

  SET @mySQL = 'usp_MyProc ' + Convert(nvarchar(20), @SchoolID) + ', ' + '''' + 'DATA IMPORT' + ''''