Having problems calling a procedure which returns output variables from another proc.

• Harshavardhan

  SSC Rookie

  Points: 29

  More actions

  July 26, 2006 at 8:56 pm

  #90346

  Hi All,

  Iam having a proc which has dynamic sql inside that.

  ex-

  Use Northwind

  CREATE PROC TEST(

  @DATABASENAME VARCHAR(30),

  @COUNTOUT INT OUTPUT

  )

  AS

  BEGIN

  DECLARE @v_SQL nVARCHAR(4000)

  DECLARE @COUNT INT

  SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'

  EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT

  SET @COUNTOUT=@COUNT

  RETURN @countout

  END

  the above sp returns the @countout param.The authors table exists in the pubs database .

  Now i want to call this sp from another sp.

  ex-

  CREATE PROC CALL

  AS

  BEGIN

  DECLARE @sql NVARCHAR(100)

  DECLARE @COUNTOUT INT

  SET @sql= 'EXEC TEST ''pubs'''

  print @sql

  EXEC SP_EXECUTESQL @sql,n'@COUNTOUT int output'

  END

  But iam unable to do this ..Can anyone help me in this ???

• Purushotham Kumar Katakam

  SSC Enthusiast

  Points: 172

  More actions

  July 27, 2006 at 1:10 am

  #651799

  Use Northwind

  Go

  CREATE PROC TEST(

  @DATABASENAME VARCHAR(30),

  @COUNTOUT INT OUTPUT

  )

  AS

  BEGIN

  DECLARE @v_SQL nVARCHAR(4000)

  DECLARE @COUNT INT

  SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'

  EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT

  SET @COUNTOUT=@COUNT

  END

  CREATE PROC CALL

  AS

  BEGIN

  DECLARE @sql NVARCHAR(100)

   

  SET @sql= 'DECLARE @COUNTOUT INT EXEC TEST ''pubs'',@countout OUT select @countout'

  print @sql

  EXEC SP_EXECUTESQL @sql

  END

   

  Thanks & Regards,
  9989069383
  Katakam.

• PNS

  Right there with Babe

  Points: 773

  More actions

  July 27, 2006 at 3:53 am

  #651817

  Use Northwind

  ALTER PROC TEST(

  @DATABASENAME VARCHAR(30),

  @COUNTOUT INT OUTPUT

  )

  AS

  BEGIN

  DECLARE @v_SQL nVARCHAR(4000)

  DECLARE @COUNT INT

  SET @V_SQL='SELECT @COUNT=COUNT(*) FROM '+@DATABASENAME+'..Authors'

  EXEC SP_EXECUTESQL @V_SQL,N'@COUNT INT OUTPUT',@COUNT OUTPUT

  SET @COUNTOUT=@COUNT

  RETURN @countout

  END

  Alter PROC CALL

  AS

  BEGIN

  DECLARE @sql NVARCHAR(100)

  DECLARE @COUNTOUT INT

  SET @sql= 'EXEC TEST ''pubs'',@COUNTOUT OUTPUT'

  EXEC SP_EXECUTESQL @sql,N'@COUNTOUT int output',@COUNTOUT output

  select @COUNTOUT

  END