I'm really having a hard time with the problem that I'm encountering, so this is my very first time to ask something in any forum.
I guess this is the best sql forum that I've been to. Anyway, here is my dilemma.
Inside my SP:
IF EXISTS (select topiccode from topic_status where topiccode = @topiccode and status = @status)
delete from topic_status ...;
insert into topic_status ...;
update topic_status ...;
Above is actually a simple query if you notice, but now I was told that we need to add the schemaname and incorporate it in every command. The schemaname will be of course an input parameter.
IF EXISTS (select topiccode from [companyA].[topic_status] where topiccode = @topiccode and status = @status)
So basically, to add the @schemaname, I need to put the command in string before executing the said command, easy? well, not really for me:
declare @sql varchar(max) -- I need this to run the if exists command
select @sql = 'IF EXISTS (select topiccode from [' + @schemaname + '].[topic_status] where topiccode = ''' + @topiccode + ''' and status = ''' + @status + ''') print 1 else print 0'
Of course this will run, but how can I get the value of 1 if it exists and 0 if it doesn't.
Using "print' will just write the value, I've tried using "return" but it doesn't work and even instead of using
the "print/return" I tried "set @returnval = 1 else set @returnval = 0"; which returns again an error.
I also tried this:
set @returnval = exec(@sql) -- of course it wont work
Is there any workaround this problem? Thank for your time and effort. Please help.
Richard C. Delavin