Function as an inbound parameter for SP????

[tomMuc]

Hi there,

is it possible to call a procedure with a function as an inbound parameter like following:

exec sp_test dbo.fn_read_value()

this results in an error!
is there a way to pass the return value from the function to the inbound parameter of the stored procedure?

thanx in advance

 

[nigelrivett]

You have to set a variable from thye function and call the SP using that.

declare @s varchar(1000)
select @s = dbo.fn_read_value()
exec sp_test @s

Note that it is not a good idea to name SPs sp_. I usually use s_.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[cheyney]

quoted from

http://www.databasejournal.com/features/mssql/print.php/1565961

5. Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.

The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend using the prefix "sp_" in user-created stored procedure names as SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, followed by the stored procedure using dbo as the owner (if one is not specified).

When you have the stored procedure with the prefix "sp_" in a database other than master, the master database is always checked first. If the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

 

[nigelrivett]

Not quite the who;e story.
If the SP in master is a system SP version in master will be executed. For user SPs the version in the current database. In both cases master will be checked to find if it is a system SP so wasting resources.
(At least last time I checked it was like that).

Commonly SPs accidentally get added to master which won't cause a problem until an SP is deleted from the database or a user connects to the wrong database then interesting things can happen.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tomMuc]

@nigelrivett: thanks for that. But in my environment i can not use this solution, because we are using a dynamic TSQL generator and there we can not add this kind of "work around". So, it seems that there's no way to pass a function as a parameter to a sp?

thanks as well for the hint with the prefix sp_ . That was just to make it clear that i'm talking about stored procedure. In 'normal' life i'm using a prefix different to sp_. ;-)