Hello

I'm relatively new to SQL Server/T-SQL and find myself stuck with this
problem:

I need to do something like this:

Declare @someValue NVarchar(100)
Declare @someFunction Varchar(100)
    --
    --  Assign value to @someValue from a Cursor
    --
    --
    --  Assign name of the function to @someFunction from a Cursor
    --
Declare @ret NVarchar(100)
    Execute 'Select @ret = ' + @someFunc + '(''' + @someValue + ''')'

I expect the last Execute statement to leave the return value from the
function in @ret.
What I get is
     Must declare the variable '@ret'.

I have also tried
     Execute sp_ExecuteSQL 'Select @ret = ' + @someFunc + '(''' +
@someValue + ''')'
with the same result.

Any help with making this work or other ways of doing this will be very
much appreciated!

TIA.
Vamsi.

Vamsi,

Use sp_executesql.

Declare @ret NVarchar(100)
declare @sql nvarchar(4000)

set @sql = N'Select @ret = ' + @someFunc + '(''' + @someValue + ''')'

exec sp_executesql @sql, N'@ret NVarchar(100) output', @ret output

print @ret
go


The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html


AMB

Splendid!!
I still can't understand that one line of code, but, it worked
perfectly :-)
Thanks very much, AMB.

V.

tvamsidhar (tvamsid***@gmail.com) writes:
For more details on sp_executesql and dynamic SQL in general, see
an article on my web site: http://www.sommarskog.se/dynamic_sql.html.

By the way, if you are new to T-SQL, dynamic SQL is probably not where
you should start.

Thanks Erland, I'll study the article! Its already in my Google
bookmarks :)

As for starting with dynamic SQL, I really don't have a choice :-) I
need to be able to run validations and formatting on type-less text
data that should be "interpreted" to be of datatypes defined in a
metadata repository (SQL Server tables) and satisfying validations
(reg. expressions, TSQL functions/SPs, etc) specified in the same
repository.

Running these on the app. server turned out to be way too inefficient
and cumbursome; hence the dynamic SQL. And although I'm more
experienced with PL/SQL, the powers that be insist on using SQL Server

I thank you for the input.

V.

Delete 'Non-Unique' Rows Question (with DDL)
Is this index redundant?
Different between ##table and #table regarding performance
Problems while migrating from MSSQL 2000 to 2005 - select... into fails in a trigger - please help.
Crazy Row Numbering Poblem