Table Name Variable in SQL used in an If..else

• Data-Man

  SSC Veteran

  Points: 291

  More actions

  January 6, 2003 at 10:40 am

  #79611

  I'm trying to use some dynamic SQL to determine if a record exists within a table in a different database. I have used the below SQL before by assigning the SQL to a variable and then using the EXEC to return the results set. But, I can't seem to figure out how to do the same when I want to use the returned value in an If..Else statement.

  How do I accomplish the logic below? I tried assigning the SQL to a variable and then inserting an Exec (@strSQL) in place of the SQL, but that gave me an error.

  if (Select Count(*) from ' + @strDatabaseName +'.dbo.VENDORS v WHERE v.VENDORID = @strVENDORID) = 0

  Any ideas would be greatly appreciated.

  Thanks,

  Mike

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  January 6, 2003 at 11:56 am

  #445644

  You can use code like this to return a variable from sp_executesql.

  declare @strstor_id int

  set @strstor_id = 6380

  declare @strDatabaseName varchar(10)

  set @strDatabaseName = 'pubs'

  declare @cnt int

  Select @cnt=Count(*) from pubs.dbo.stores v WHERE v.stor_ID = 6380

  print @cnt

  set @cnt = 99

  declare @cmd nvarchar(1000)

  set @cmd = 'Select @cnt=Count(*) from ' + rtrim(@strDatabaseName) + '.dbo.stores v WHERE v.stor_ID = ' + cast(@strstor_ID as char(10))

  print @cmd

  print @cnt

  exec sp_executesql @cmd,N'@cnt int out',@cnt out

  print @cnt

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP