• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

TSQL: ScalarFunction::

Here is the question, team!

I need to be able to call the EXEC(@MyStatement)  in my SCALAR function. This is normally not doable and been told that must use the extended version..
In another word, I will have to be able to construct a "select" string statement and run that in a scalar function.  I don't care how that needs to be accomplished but needs to be done this way.
Question:  Is this true, and if you have experienced that then would share some thought on that with me.
thx

here is the error:
<code>
Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.
</code>

and the sample code:
<code>
      declare @Result sql_variant
      set @Result = 0
      
      declare @MyKey int
      declare @ExecString nvarchar(1000)

      set @mykey = (select keytypenum from MyDB.dbo.keytypetable where ltrim(Rtrim(keytype)) = @MyKeyword)

      set @execstring = 'select @result = keyvaluecurr from MyDB.dbo.keyitem'+ convert(nvarchar(32), @mykey) +' where itemnum = ' + convert(nvarchar(32), @myinput)

      exec sp_executesql @execstring

      -- Return the result of the function
      RETURN  (@result)
</code>

JohnE
0
John Esraelo
Asked:
John Esraelo
  • 12
  • 6
  • 2
  • +1
1 Solution
 
ZberteocCommented:
You CANNOT do dynamic query in a function. You can do it in a stored procedure. Create a stored procedure that will either return your value or you can use an output parameter.
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Perhaps I have neglected to add the needed information.  I need to pass 2 parameters from a select statement in another stored procedure and I would need this pass / return a single value.
Can you call a stored procedure from within a select statement or another stored procedure?
That's what I am up against now and that is the reason why that I would need to get a table value or scalar value returned.

thx

JohnE
0
 
ZberteocCommented:
If you want to use the function in your SELECT list so that it will be executed for every row then yes, only function can do that. SO if you want to do:

SELECT col1, col2, function(col3, col4) ASresult, ... FROM sometable

only a function will work that way, not stored procedure but as I said, you CANNOT have dynamic query executed within function and for a good reason.

Here is a discussion about the exact same situation with some suggestions to workaround but it boils down to a stored procedure, same as I suggested:

http://stackoverflow.com/questions/150552/executing-dynamic-sql-in-a-sqlserver-2005-function

Another thing you could do is to build a stored procedure where you can build dynamically the select statement you intended to use the function in and work it that way. Maybe if you explain a little what are your conditions and what you are trying to achieve and I am sure we can find something. From what I can deduce from your code you have some info in a table that can be used to build the name for other tables. Am I right?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
As I mentioned, it is a simple process.  I have a select statement in a stored procedure that needs to go get a value... where is the value coming from?  from the so called wanna be a function that cannot be at this time..
therefore.. that leaves me with a select statement having bunch of fields, and a procedure call (since I cannot have that function)..
So, I can create a function to get me that value that I want, no problem, can you show me how to make the procedure call in a select statement?
thx

JohnE
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
I believe I have found my answer.   I will be using a function, in that function I will be using an open query to run my select statement (the concatenated pieces) string and I believe I am done..
I will actually try that and post the results.. don't want to celebrate too soon ..

thx

JohnE
0
 
Anthony PerkinsCommented:
Just a word of caution, using this approach on large tables will be a dog:  Performance will go down the toilet.
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Actually it is not that big at all.. If I ever get to build it ..  ;)
Sounds like you know what I am looking for; perhaps you can shed some light on this.
thx

JohnE
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
The OPENQUERY.. well, unfortunately it seems like working on LINKED servers only and cannot point to the same server.
Well, I am back on SQ1..
0
 
Scott PletcherSenior DBACommented:
A server can be a "linked server" to itself.

That is, say you are running the code on "MyServer".  On "MyServer" you can do this:


EXEC sp_addlinkedserver 'MyServer', 'SQL Server'
0
 
Scott PletcherSenior DBACommented:
Btw, you can run exec a stored proc from within a stored proc, including getting back a result from that proc:

CREATE PROC proc1
   ...
AS
SET NOCOUNT ON
...
EXEC proc2 @param1, @param2, @output1 OUTPUT, @output2 OUTPUT
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
How about:

select field1, field2, field3, (exec Mydb.dbo.USP_MyProc para1, para2) from ATable
0
 
Scott PletcherSenior DBACommented:
Nope, that's not allowed :-(.
0
 
Scott PletcherSenior DBACommented:
Why have all separate key value tables?  Why not just have one consolidated table with the keytypenum as the first key column?
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
Very good question.
This system, DB, has hundreds of tables with 2 part naming.
The second part of the name is a numeric value.
This numeric value is a primary key to keywords that are created by users / admins..
so, therefore, to get a value stored for a particular keyword, one has to get the PK of the keyword in a table then use it as a the suffix of the table name and then go get the value that is needed..
I can do this in a USP instead of a UDF.. but the problem is that, like you said, cannot call that USP in a select statement..
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
How about, having a function that deals with another USP that creates the exec string, gets the value, passes that to a function, then the function returning it to my calling USP..
it sounds dumb, but, what do I have to loose..
The results is quick and short.. that's why I am not worry about the performance.
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
no, I don't think I can do that either..
I can only use
only functions and some extended stored procedures in a function

perhaps I should be looking into the table value returns with table data types being return and / or passed from one fund/proc to another..
I am feeling so desperate ;)
0
 
Scott PletcherSenior DBACommented:
>> This system, DB, has hundreds of tables with 2 part naming. <<

Ouch, hundreds??

That rules out just testing for the key and then reading the appropriate table ... or you have to your own quasi-boolean search to find the right table to read.

I can't think of a way around the dynamic SQL restrictions right now ... and even if we did, SQL might "catch on" and invalidate it later, so I'd be nervous about doing it that way anyway.
0
 
Scott PletcherSenior DBACommented:
I'd still love to get to just one table with an additional key column at the front ... that's easy to do from a technical standpoint, but

The problem is the query won't have the keytypenum at lookup time for existing lookups.
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
I am almost thinking of creating a zero length field value or a 0.00 in my USP ONE and then, write the records to a #TempTable, then, use the temp table's PK values to create another #TempTable2, and then "UPDATE" the blank field in tempONE with them in the same USP.. and forget about external USP or UDF.. just do them in one shot..
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
single USP, couple of # , @ Tables, under the same roof.. done..
thx guys



JohnE
0
 
Anthony PerkinsCommented:
forget about external USP or UDF.. just do them in one shot..
...
single USP, couple of # , @ Tables, under the same roof.. done..

Right.  That is exactly what you were told in the very first comment:
You CANNOT do dynamic query in a function. You can do it in a stored procedure. Create a stored procedure that will either return your value or you can use an output parameter.
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
found my own solution, by having housing and incorporation multiple procedure into one
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now