select id from Answers a
cross apply CheckRejectability (RejectAnswer) b
where b.returnfield=1

quote:

---

Originally posted by GilaMonster

Can you post the definition of CheckRejectability please?

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by haoest

quote:

---

Originally posted by GilaMonster

Can you post the definition of CheckRejectability please?

--
Gail Shaw
SQL Server MVP

---

I don't have it with me as it's in the computer at work, but it basically builds a dynamic SQL string with the @RejectableExpression in it somewhere, and then I run Exec() on it. It looks back at the answer table and see if the expression is true or false.

If I were to pull it up from memory, it would be something like this:

set @sqlstr = replace(@rejectableExpression, '[', 'select answerValue from Answers where id = ')

... I call replace() again just to make @sqlstr a valid sql statement. I don't think the detail is too important here...

In the end I do this:

set @sqlstr = 'select case when (' + @sqlstr + ') 1 else 0 end'

select into #mytmp exec(@sqlstr)

Now come to think of it, I have a @ReturnValue (int) output parameter initialized to 0, which is only used for better usability. Anyways, i do this in the very end:

if (select count(*) from #mytmp) > 0
select @ReturnValue = retval from #mytmp

I tried UDF like visakh16 recommended, but with exec(), selecting from a permanent table, try/catch, and other small stuff, UDF doesn't like it and spits Time-dependent error at me.

---

select id from Answers
where (SELECT returnfield
FROM OPENROWSET('SQLNCLI', 'Server=yourserver;Trusted_Connection=yes;',
     'exec CheckRejectability @RejectableExpression=RejectAnswer') =1

quote:

---

Originally posted by Vinnie881

Please post your actual procedure. In all liklyhood it can be tweeked to be a function. Second the method you described here has a huge amount of potential issues, are you stuck using this methodolgy to validate the answeres (I am referring to te layout and structure), or would you like to discuss possible better alternatives?

---

CREATE PROCEDURE [dbo].CheckRejectability
	@exp varchar(7000), @returnValue int output
AS
BEGIN
	SET NOCOUNT ON;
	declare @s varchar(8000)
	if object_id('tempdb..#risk_rej_check_session') is not null 
		drop table #risk_rej_check_session
	set @returnValue = 0
	begin try
		set @exp = replace(@exp, '[', '(select answervalue from Answers ' + 
			'where QuestionID = ''')
		set @exp = replace(@exp, ']', ''')')
		set @s = 'select case when (' + @exp + ') then 1 else 0 end as returnValue ' 
		create table #risk_rej_check_session (returnValue int)
		insert into #risk_rej_check_session exec(@s)
	end try
	begin catch
	end catch
	-- select @s
	if ((select count(0) from #risk_rej_check_session) > 0) begin
		select @returnValue = returnValue from #risk_rej_check_session
	end
	drop table #risk_rej_check_session
END