1

I want to invoke a dynamic query inside a Scalar function. I tried using EXEC and sp_executesql, but it is not found success. Then I went for OPENQUERY, But it do not accept dynamic parameters.

Here is my SQL code

DECLARE @query varchar(max) = 'SELECT  COUNT('+@FromCol+') from '+@FromTable+' WHERE '+@FromCol+' IN (SELECT  '+@ToCol+' FROM '+@ToTable+' WHERE userId = 0)'

INSERT INTO @TempResult([rowCount]) 
   SELECT * 
   FROM OPENQUERY([GREEN\SQLEXPRESS], 'Exec [MyDB].[dbo].[testSP] '+[@FromCol]) as [OpenQuery]

Here, if possible can I execute dynamic query ie, @query or pass parameter to stored procedure testSP?

Shahul mechery
  • 11
  • 5
  • Not quite what you are looking for, but has some examples that may help? http://www.sommarskog.se/share_data.html – Richard Hansell Mar 06 '15 at 10:53
  • What is the issue? Calling Dynamic query in Scalar function or Open query dynamic parameter? – SelvaS Mar 06 '15 at 11:04
  • @SelvaTS: I cannot call Dynamic Query neither pass dynamic parameter to Open query. – Shahul mechery Mar 06 '15 at 11:13
  • It's not possible: neither dynamic queries neither calling stored proc from functions – Giorgi Nakeuri Mar 06 '15 at 11:19
  • @GiorgiNakeuri: Thanks. Is there any other options as I want to know all tables where a particular value exists, before deleting a row value – Shahul mechery Mar 06 '15 at 11:24
  • 1
    I can not really understand what you are asking, but may be you are searching for foreign key constraints – Giorgi Nakeuri Mar 06 '15 at 11:26
  • @GiorgiNakeuri: I think It is possible to call a stored proc from a function, I made it working, But dynamic parameters cannot be passed to that stored proc. there I got stuck :( – Shahul mechery Mar 06 '15 at 11:27
  • foreign Key is already set, But here I want to know is there any reference to a particular value in any other tables as set in foreign key constraints before executing delete query – Shahul mechery Mar 06 '15 at 11:30
  • @Shahulmechery its not possible to execute dynamic queries inside the funtion. It will throw the following error "Only functions and some extended stored procedures can be executed from within a function.". So why you need function? You can do the same by SP. – SelvaS Mar 06 '15 at 11:30
  • @Shahulmechery In this case, you can go with SP not by function. – SelvaS Mar 06 '15 at 11:31
  • To make it more clear, I am trying WPF MVVM Model, Here I need to get all data in a table with a extra column [IsRefered] which shows any reference exists in multiple tables with the row value. For that I feel better to use a function – Shahul mechery Mar 06 '15 at 11:35
  • @Shahulmechery you can't execute dynamic sql in function because function is consider as deterministic while dynamic sql consider as undeterministic http://blog.sqlauthority.com/2007/05/29/sql-server-user-defined-functions-udf-limitations/ – Khurram Ali Mar 06 '15 at 11:59
  • Why do you declare query variable, and then not use it? Your code that you posted doesn't make any sense. Also, see here: http://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function – Tab Alleman Mar 06 '15 at 14:39
  • @TabAlleman: I just showed you the 2 methods i tried in function. In function we cant execute a dynamic query. so executing variable query is not possible. I guess so – Shahul mechery Mar 09 '15 at 05:10

0 Answers0