Dynamic sql (sp_executesql)

• LeopoldNjampa

  SSC-Addicted

  Points: 473

  More actions

  July 3, 2003 at 5:44 am

  #83781

  Hi all,

  This is driving me to the wall.

  I want to count and return (to a local variable) the number of rows in a table using a dynamically built sql string.

  Any ideas how to best do this?

  This is what i aim for :

  SELECT @iCount = COUNT(*) FROM Pubs.dbo.Employee

  IF @iCount > xx

  --do something

  else

  --do something else

  --This is what I have been trying (unsuccessfully):

  declare @strSQL nvarchar(500)

  declare @dbname sysname

  declare @tablename sysname

  declare @icount int

  set @dbname = 'Pubs'

  set @tablename = 'Employee'

  SET @strSQL ='select @icount = COUNT(*) FROM ' + @DBName + '..' + @TableName

  EXEC sp_executesql @strsql

  --Ofcourse @iCount is outside the dynamic statement so I tried:

  SET @strSQL ='declare @iCount tinyint select @icount = COUNT(*) FROM ' + @DBName + '..' + @TableName

  EXEC sp_executesql @strSQL

  This executes fine but I am not able to return the value of @iCount so that I can perform various checks/validations in my stored procedure.

  I could create a table to hold the value of @iCount which i could then use, but don't like it! I don't wnat to write the whole stored proc in dynamic sql either.

  I am hoping for a more elegant solution.

  Thanks.

• mccork

  SSC-Insane

  Points: 22288

  More actions

  July 3, 2003 at 6:17 am

  #464037

  You were almost there.

  Try the following:

  declare @strSQL nvarchar(500)

  declare @dbname sysname

  declare @tablename sysname

  declare @icount int

  set @dbname = 'Pubs'

  set @tablename = 'Employee'

  SET @strSQL ='select @icount = COUNT(*) FROM ' + @DBName + '..' + @TableName

  EXEC sp_executesql @strSQL, N'@iCount INT OUTPUT', @icount=@icount output

  SELECT @iCount

  
  Cheers,
  - Mark

• LeopoldNjampa

  SSC-Addicted

  Points: 473

  More actions

  July 3, 2003 at 7:12 am

  #464043

  Thanks very much mccork.

  Exactly what i was after.