stored-procedudure error

• anakin

  SSC Veteran

  Points: 272

  More actions

  November 5, 2002 at 9:58 am

  #79853

  hi,

  this is my stored procedure:

  CREATE PROCEDURE stp_SelectSQL

  (

  @sql text,

  @Result int OUTPUT

  )

  AS

  DECLARE @OID int

  EXEC(@SQL)

  SET @Result=@OID

  RETURN @Result

  GO

  and this is en example of query to pass to stored procedure:

  SELECT @OID=OID FROM tbBaseProducts WHERE ImpPrKey = '020amm0010'

  This is the error message:

  Must declare the variable '@OID'. The 'stp_SelectSQL' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

  Help me !!!!!

  thanks, anakin

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  November 5, 2002 at 10:34 am

  #440361

  Try looking at the following topic, I believe it holds your answer.

  http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=7690&FORUM_ID=8&CAT_ID=1&Topic_Title=declaring+variable&Forum_Title=T%2DSQL

  Far away is close at hand in the images of elsewhere.
  Anon.

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  November 6, 2002 at 11:57 am

  #440525

  So what you need is

  DECLARE @OID int

  exec sp_executesql @sql, '@OID int OUTPUT',@OID OUTPUT

  SET @Result=@OID

  RETURN @Result

  Be aware that using dyanmic sql means you need to give users writes to the underlying tables, rather than give access to stored procedures.

  Simon Sabin

  Co-author of SQL Server 2000 XML Distilled

  http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

  
  Simon Sabin
  SQL Server MVP
  
  http://sqlblogcasts.com/blogs/simons

• RDEHudson

  Grasshopper

  Points: 12

  More actions

  November 7, 2002 at 10:34 am

  #440678

  Also be careful if you inadvertently pass in an erroneous SQL string (i.e. refering to a field or table that doesn't exist). This may cause the server to ramp up to CPU 100%.