| Author |
Topic |
|
issam
Starting Member
30 Posts |
 Posted - 2008-06-02 : 09:22:18
|
| Hi,how to use a Dynamic variable on a function, to explan my self more here is a sample, we use this on SP but the function not allow executing.DECLARE @SQL nvarchar(1000);set @sql=''DECLARE @RESULT nVARCHAR(1000);SET @RESULT=''DECLARE @mpq int;SET @mpq=0DECLARE @FILENAME VARCHAR(40);SET @FILENAME='parm'SELECT @RESULT =SCHEMA_NAME((SELECT SCHEMA_ID FROM SYS.TABLES WHERE NAME=@FILENAME))+'.'SET @SQL=N'SELECT @mpq = CASE WHEN MPQ=1 THEN 10 WHEN MPQ=2 THEN 100 WHEN MPQ=3 THEN 1000 END FROM '+@RESULT+'PARM'EXEC SP_EXECUTESQL @SQL,N'@mpq INT OUTPUT',@mpq OUTPUT |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-02 : 09:31:47
|
Dynamic SQL is not allowed in User Defined Function.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-02 : 09:33:36
|
quote:
Originally posted by issam
Hi,how to use a Dynamic variable on a function, to explan my self more here is a sample, we use this on SP but the function not allow executing.DECLARE @SQL nvarchar(1000);set @sql=''DECLARE @RESULT nVARCHAR(1000);SET @RESULT=''DECLARE @mpq int;SET @mpq=0DECLARE @FILENAME VARCHAR(40);SET @FILENAME='parm'SELECT @RESULT =SCHEMA_NAME((SELECT SCHEMA_ID FROM SYS.TABLES WHERE NAME=@FILENAME))+'.'SET @SQL=N'SELECT @mpq = CASE WHEN MPQ=1 THEN 10 WHEN MPQ=2 THEN 100 WHEN MPQ=3 THEN 1000 END FROM '+@RESULT+'PARM'EXEC SP_EXECUTESQL @SQL,N'@mpq INT OUTPUT',@mpq OUTPUT
Why are you passing the object name as a parameter? can i ask what you're trying to do here? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-02 : 09:35:26
|
Instead of sys.tables, you should use INFORMATION_SCHEMA.TABLES view instead.There you get the schema directly.But the question still remains, which schema to return for a table name used twice?dbo.ParmHR.Parm
E 12°55'05.25"N 56°04'39.16" |
|
|
|
issam
Starting Member
30 Posts |
Posted - 2008-06-02 : 09:41:25
|
| I have a function do a calculation, I am calling this function by select statement, on the ‘parm’ table some parameters needed for the calculations. This table schema could be change so i am try to read the file schema before to use the table |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-02 : 09:43:40
|
quote:
Originally posted by issam
I have a function do a calculation, I am calling this function by select statement, on the ‘parm’ table some parameters needed for the calculations. This table schema could be change so i am try to read the file schema before to use the table
Didnt get that. Why does schema change? |
|
|
|
issam
Starting Member
30 Posts |
Posted - 2008-06-02 : 09:52:33
|
| the table name on the database only one table but the schema can change from database to anther, that's why checking the schama before use. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-02 : 09:54:26
|
quote:
Originally posted by issam
the table name on the database only one table but the schema can change from database to anther, that's why checking the schama before use.
So you are going to perform the operation on every db on your server? |
|
|
|
issam
Starting Member
30 Posts |
Posted - 2008-06-02 : 09:54:52
|
| the table name on the database only one table but the schema can change from database to anthor, that's why checking the schama before use. |
|
|
|
issam
Starting Member
30 Posts |
Posted - 2008-06-02 : 12:38:35
|
| Ya |
|
|
|
|
Dynamic variable with a funct