December 26, 2001 at 10:54 pm
Hi,
I am trying to figure out if a column exists in the system tables and if so to execute some code. I can figure out if the column exists, but how can I set a dynamic executed statement equal to another variable. Here's my code:
DECLARE @myDB varchar(255), @myTable varchar(255), @myColumn varchar(255), @myOutcome int, @strSQL varchar(4000)
'for testing purposes, give variables values
@myDB = 'myDatabase'
@myTable = 'myTable'
@myColumn = 'theVeryImportantColumn'
set @strSQL = ('SELECT Count(*) FROM [' + @myDB + '].information_schema.columns WHERE table_catalog + ''.'' + Table_Schema + ''.'' + Table_name = ''' + @myTable + ''' AND column_name=''' + @myColumn + '''')
Exec (@strSQL)
(this works, given there might be a problem somewhere in the strSQL with apostrophes, I couldn't directly cut and paste this from my code because i ended up with 2 machines with the same name, so one can't be on the network...it's a long story, fixable, I know, but not now)
Anyway, what I want to do with the last line is say
Set @myOutcome = (EXEC (@strSQL) ), but this blows up. I need to be able to run an IF statement on the outcome of that dynamic statement to make sure the value = one. The only other way I could think to do this was to find a system table that allowed you to say select count(*) from somewhere where db=@db and table=@table and column=@column, but I haven't found anything that doesn't require you to know the db in advance, so it seems I'm stuck. Do you know a way to set a variable equal to an EXEC statement?
Thanks in advance!
April
December 27, 2001 at 6:13 am
I dont think you can do it directly when you're using Exec with dynamic sql. Exec is just going to return a 'return' value. Options are to use a variable marked for output, or to write the results to a table and then have the operation following the exec read from that table (perhaps tagging results with the SPID to allow them to be identified).
Andy
December 27, 2001 at 3:47 pm
Andy,
I read up on it some and tried to use a variable marked for output, but couldn't figure out the right code. I tried some example codes, but didn't have any luck. I think I'll try your table suggestion and see how that goes. I would like to better understand how you mark a variable for output, if you have written something similar (using the EXEC, instead of output through a stored procedure) that would be helpful. I'm on a tight deadline, so I probably won't use it in this crisis, but hopefully could use it in the future.
Thanks!
April
December 27, 2001 at 6:36 pm
I dont remember the exact syntax, but you can call the stored proc you cited above from another stored proc and assign the return value to a variable (as long as the stored proc being called only return a single value):
If your stored proc is called pr_ReturnCount then, (here is a brief example, I left out proper syntax, but here you go)
Create Proc MyProc
@variable1 type,
@variable2 type
As
Declare @nCount int
exec @nCount = pr_ReturnCount @myDB, @myTable, @myColumn, @myOutcome, @strSQL
December 28, 2001 at 1:03 am
you can use sp_executeSql to return a output value in the following way
Declare @strSQL nVarchar(2000),
@strt nvarchar(100),
@Count INT
set @strSQL = ('SELECT @Count = Count(*) FROM .information_schema.columns WHERE table_catalog + ''.'' + Table_Schema + ''.'' + Table_name = '+ char(39)+'syscolumns '+ char(39)+ ' AND column_name='+ char(39)+'name'+ char(39) )
set @strt = '@Count int output'
EXEC sp_ExecuteSql @strSql , @strt, @Count Output
PRINT @Count
Shrinivas L.K.
December 28, 2001 at 10:05 am
nice example!
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 2, 2002 at 6:45 pm
declare @sql nvarchar(1000)
declare @name varchar(10)
set @name = 'name'
select @sql = 'if exists(select * from syscolumns where name = '''+ @name + ''') begin select 1 end else begin select 0 end'
exec sp_executesql @sql
--if you really need the count:
declare @sql nvarchar(1000)
declare @name varchar(10)
set @name = 'name'
select @sql = 'if (select count(*) from syscolumns where name = '''+ @name + ''') = 10 begin select 1 end else begin select 0 end'
exec sp_executesql @sql
Where I'm selecting either 1 or 0 you could also call your secondary procedure like this;
declare @sql nvarchar(1000)
declare @name varchar(10)
set @name = 'name'
select @sql = 'if (select count(*) from syscolumns where name = '''+ @name + ''') = 10 begin select * from sysobjects end else begin select 0 end'
exec sp_executesql @sql
Hope this helps...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply