Exec String with Return - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Exec String with Return

Author

Topic

dehseth
Starting Member

18 Posts

Posted - 2006-12-23 : 03:37:07

Hi ppl,
i need to create a function which uses exec method which i couldn't.
code:

create function f () RETURNS int AS
BEGIN
EXEC('if (1) return ')
RETURN 1
END

gives me this error: "Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function."

is there any way to use EXEC in a function?

my actual problem is to create a function or smt else which works like IIF function like:

variable = IIF (condition, true_part, false_part) works as exactly as
variable = condition ? true_part : false_part

I cannot use ?: syntax in t-sql so what could i do to accomplish that?

as an example:
executing
int x = IIF(5 > 4, 3 , 6)
sets x as 3

executing
int x = IIF(5 <= 4, 3 , 6)
sets x as 6

condition part may have AND OR + * - / operators like we use in IF sentence.

thank you everyone.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-23 : 04:04:51

>>is there any way to use EXEC in a function?

exec Your_Stored_procedure @variables_go_here

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-23 : 08:09:52

IIF is suppored in Access and you have to use CASE WHEN in sql server

int x = IIF(5 > 4, 3 , 6)
sets x as 3

Can be written as

Declare @x int
Select @x= CASE WHEN 5 > 4 THEN 3 ELSE 6 END
Print @x

Also, you cant use EXEC in a Function

Madhivanan

Failing to plan is Planning to fail

dehseth
Starting Member

18 Posts

Posted - 2006-12-25 : 07:00:46

thank you for ur reply man...

i solved my problem calling a SP inside of my Function. seems like working.

quote:
Originally posted by madhivanan

IIF is suppored in Access and you have to use CASE WHEN in sql server

int x = IIF(5 > 4, 3 , 6)
sets x as 3

Can be written as

Declare @x int
Select @x= CASE WHEN 5 > 4 THEN 3 ELSE 6 END
Print @x

Also, you cant use EXEC in a Function

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 07:28:42

quote:
i solved my problem calling a SP inside of my Function. seems like working.

This is somthing new, how did you called SP in the UDF?? can you post the code which satisfied your purpose?

Chirag

http://chirikworld.blogspot.com/

dehseth
Starting Member

18 Posts

Posted - 2006-12-26 : 05:40:45

sorry it didn't work. i also tried a c# dll as function and use exec in this function. also ddin't work. seems like there's noway to use exec | execute command in function related functions | stored procs..

quote:
Originally posted by chiragkhabaria

quote:
i solved my problem calling a SP inside of my Function. seems like working.

This is somthing new, how did you called SP in the UDF?? can you post the code which satisfied your purpose?

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-26 : 05:55:57

post the code which you have tried...???

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-26 : 09:10:22

You cant use Dynamic SQL in Function
Post the full code you used
If you cant avoid Dynamic SQL, you should use stored procedure in place of Functionh

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources