The essence of the problem. There is a function, at the entrance of which there must be id objects of a specific table.

So far I have found 3 solutions, but I don’t really like them:

  1. Pass a string and parse. I do not like it because it will hit on performance. In addition, at the transmitting end, you will first need to form this line from the SELECT id FROM table1 WHERE <condition> query SELECT id FROM table1 WHERE <condition>
  2. Use a temporary table. That is, to create it where I will call my function and already use its values ​​in the function. Uncomfortable, because the use of the function will not be obvious.
  3. Send the SQL query as a string, and in the function already execute it. I just don't like it so much :)

Are there other options that will not complicate the readability of the code and will not slow down?

    2 answers 2

    1. Not such a crazy option.
    2. The function cannot use a temporary table. But if you meant the stored procedure, then it is quite possible.
    3. Yes, this is a bad option. SQL injection ... In addition, dynamic SQL cannot be executed in a function.

    Starting with 2016 server, you can send a list of IDs in JSON format.

    In 2005, in turn, can be sent as XML.

    UPD : There is another interesting way. You can create a user-defined data type table, fill it with content and pass it as a function parameter:

     --Чистим Π·Π° собой (Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΠΏΠΎΠ²Ρ‚ΠΎΡ€Π½Ρ‹ΠΉ запуск Π½Π΅ ΠΏΡ€ΠΈΠ²ΠΎΠ΄ΠΈΠ» ΠΊ ошибкС) IF OBJECT_ID('FINT', 'IF') IS NOT NULL DROP FUNCTION FINT; IF TYPE_ID('TINT') IS NOT NULL DROP TYPE TINT; GO --Π‘ΠΎΠ·Π΄Π°Ρ‘ΠΌ ΠΏΠΎΠ»ΡŒΠ·Ρ€ΠΎΠ²Π°Ρ‚Π΅Π»ΡŒΡΠΊΠΈΠΉ Ρ‚ΠΈΠΏ пСрСмСнная-Ρ‚Π°Π±Π»ΠΈΡ†Π° CREATE TYPE TINT AS TABLE ( id INT ); GO --Π‘ΠΎΠ·Π΄Π°Ρ‘ΠΌ Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ CREATE FUNCTION FINT ( @tint TINT READONLY ) RETURNS TABLE AS RETURN( SELECT id, 1000+id [1000+id] FROM @tint ) GO --Π˜Π»Π΄Π»ΡŽΡΡ‚Ρ€ΠΈΡ€ΡƒΠ΅ΠΌ Π²Ρ‹Π·ΠΎΠ² Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠΈ с ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€ΠΎΠΌ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ DECLARE @tint TINT INSERT @tint SELECT 1 INSERT @tint SELECT 2 INSERT @tint SELECT 3 SELECT * FROM dbo.FINT(@tint) --Π Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚: /* id 1000+id 1 1001 2 1002 3 1003 */

    If such a task is to transfer the list of INTs, it often gets up, then it is quite an elegant solution.

    • 1. The option is not crazy, but it creates inconvenience. To pass a string, you must first create it. and as for xml, then it is generally a costly operation. If you look at the plan for such parsing xml.query('for $id in /id....') , then it will be seen that the xml representation in the table data is a very expensive operation. - iRumba
    • Why in function it is impossible to execute EXEC sp_sqlexecute ??? I have not tried it, but take your word for it? where does this limit come from? - iRumba
    • Creating a string from a query is very simple, for example, so SELECT ',' + CAST (Id AS VARCHAR (11)) FROM table1 WHERE FOR XML PATH ('') - pegoopik
    • one
      There are quite a few limitations in the function. And they are all related to the fact that functions can be used within a request, in contrast to stored procedures, where there are no such restrictions - pegoopik
    • And how then the aggregate functions are arranged? - iRumba

    Even if you don't like it (p.3), but you still want to transmit the request, you can create a CLR. :)

     namespace CLR_Functions { public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction( IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)] // -- ALTER DATE: 29.09.2014 09:41 d-ivanov; 25.09.2014 16:22 d-ivanov; public static SqlString SFs_C_Name_ID_CD_Parameter_Values(SqlGuid F_Parameter_Values) { var result = ""; // ΠŸΠΎΠ»ΡƒΡ‡Π΅Π½ΠΈΠ΅ ΠΈΠΌΠ΅Π½ΠΈ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹, Ρ€Π°Π·Π΄Π΅Π»Π° ΠΈ допполя. using (var conn = new SqlConnection("context connection=true")) { conn.Open(); var cmd = new SqlCommand( @" SELECT @F_Division = cpv.F_Division, -- Π”ΠΈΠ²ΠΈΠΆΠ½ записи. @F_Custom_Table_LINK = cpv.F_Custom_Table_LINK, -- Числовой Π»ΠΈΠ½ΠΊ. @F_Custom_Table_LINKG = cpv.F_Custom_Table_LINKG, -- Гуидовский Π»ΠΈΠ½ΠΊ. @C_CCF_Display_Name = ccf.C_Display_Name, -- Имя допполя. @C_CCF_System_Name = ccf.C_System_Name, -- Имя допполя систСмноС. @C_CFS_Name = ccfs.C_Name, -- Имя Ρ€Π°Π·Π΄Π΅Π»Π°. @C_Table_Name_Real = ccft.C_Table_Name_Real -- Имя ΠΎΠ±Π΅ΠΊΡ‚Π°, Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ ΠΈΠ»ΠΈ прСдставлСния. FROM dbo.CD_Parameter_Values cpv INNER JOIN dbo.CS_Custom_Fields ccf ON cpv.F_Custom_Field = ccf.LINK INNER JOIN dbo.CS_Custom_Field_Sections ccfs ON ccf.F_Custom_Field_Sections = ccfs.LINK INNER JOIN dbo.CS_Custom_Fileld_Tables ccft ON ccfs.F_Custom_Fileld_Tables = ccft.LINK WHERE cpv.LINK = @F_Parameter_Values ", conn); // Π’Ρ…ΠΎΠ΄Π½Ρ‹Π΅ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€Ρ‹. cmd.Parameters.Add("@F_Parameter_Values", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Input; // Π’Ρ‹Ρ…ΠΎΠ΄Π½Ρ‹Π΅ ΠΏΠ°Ρ€Π°ΠΌΠ΅Ρ‚Ρ€Ρ‹. cmd.Parameters.Add("@F_Division", SqlDbType.TinyInt).Direction = ParameterDirection.Output; cmd.Parameters.Add("@F_Custom_Table_LINK", SqlDbType.Int).Direction = ParameterDirection.Output; cmd.Parameters.Add("@F_Custom_Table_LINKG", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output; cmd.Parameters.Add("@C_CCF_Display_Name", SqlDbType.NVarChar, 200).Direction = ParameterDirection.Output; cmd.Parameters.Add("@C_CCF_System_Name", SqlDbType.NVarChar, 200).Direction = ParameterDirection.Output; cmd.Parameters.Add("@C_CFS_Name", SqlDbType.NVarChar, 200).Direction = ParameterDirection.Output; cmd.Parameters.Add("@C_Table_Name_Real", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output; // ΠŸΡƒΡˆΠΈΠΌ Π»ΠΈΠ½ΠΊ допполя. Push me, and then just touch... (",) cmd.Parameters["@F_Parameter_Values"].Value = F_Parameter_Values; // ВыполняСм запрос. cmd.ExecuteNonQuery(); } } } }

    Then you can read from the query (well, up to the closing curly brackets, of course):

     var C_CCF_Display_Name = cmd.Parameters["@C_CCF_Display_Name"].Value.ToString();

    This is just an example of how to write. We pass our request to the function. :)

    I apologize in advance that I did not write a separate abstract example, but simply threw my code. A lot of work. Glimpsed glimpse. All beaver. :)