locked
How to call sql function RRS feed

  • Question

  • User-1499457942 posted

    Hi 

      I have below code and want to create a function in c# and then call it using c#

    Declare @Var nvarchar(MAX)
    Set @Var ='188,189,190,191,192,193,194'
    DECLARE @XML AS XML
    DECLARE @Delimiter AS CHAR(1) =','
    SET @XML = CAST(('<X>'+REPLACE(@Var,@Delimiter ,'</X><X>')+'</X>') AS XML)
    DECLARE @temp TABLE (ID INT)
    INSERT INTO @temp
    SELECT N.value('.', 'INT') AS ID FROM @XML.nodes('X') AS T(N)
    SELECT * FROM @temp

    Thanks

    Thursday, September 20, 2018 9:49 AM

Answers

  • User475983607 posted

    Can u pls guide me how to use string.split and then use as IN in select statement

    This is a new question and you should start a new thread. 

    Plus I already explained what to do above.  Create a stored procedure, View, or string built SQL in C#.

    string sqlCommand = "SELECT * FROM SomeTable WHERE SomeTable.Column IN (dbo.SplitCSVs(@CSVString))"

    IMHO, you've been on the forums long enough to read SQL reference documentation and put together something resembling an attempt at a solution.  Up to this point it seems you just copy code without understanding how the code works.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 20, 2018 3:41 PM

All replies

  • User475983607 posted

    The SQL splits a string into a result set. 

    The C# string.Split() method does the same.  Consider looking through your previous posts you've asked this question several times.

    https://docs.microsoft.com/en-us/dotnet/csharp/how-to/parse-strings-using-split

    Thursday, September 20, 2018 11:00 AM
  • User-1499457942 posted

    Hi

      I have data in a field like this 'India,Pakistan,China,Russia) i.e comma separated values. I want to convert this in to sql In Statement

    Thanks

    Thursday, September 20, 2018 11:20 AM
  • User475983607 posted

    I have data in a field like this 'India,Pakistan,China,Russia) i.e comma separated values. I want to convert this in to sql In Statement

    Confused... your original post is SQL and you asked to convert the SQL to C#.  I have no idea what you are trying to do.  If you are trying to call the SQL from C# then I'm still confused because you could just use string.Split().  

    Most importantly, you should never have comma separated values in a table column.  It means you have a schema design bug and need to create a JOIN and a new table.  This has also been explained several time in your other threads. 

    Thursday, September 20, 2018 11:37 AM
  • User-1499457942 posted

    Hi mgebhard

      I have created below function and i want to call it using c# and retreive data. How it can be done . Secondly i want to know that @SeparatedValues is a temp table .

    CREATE FUNCTION dbo.SplitCSVs (@CSVString varchar(1000))
    RETURNS @SeparatedValues TABLE (Value VARCHAR(100))
    AS
    BEGIN
    DECLARE @CommaPosition INT
    WHILE (CHARINDEX(',', @CSVString, 0) > 0)
    BEGIN
    SET @CommaPosition = CHARINDEX(',', @CSVString, 0)
    INSERT INTO @SeparatedValues (Value)
    SELECT SUBSTRING(@CSVString, 0, @CommaPosition)
    SET @CSVString = STUFF(@CSVString, 1, @CommaPosition, '')
    END
    INSERT INTO @SeparatedValues (Value)
    SELECT @CSVString
    RETURN
    END

    Thanks

    Thursday, September 20, 2018 2:08 PM
  • User475983607 posted

    It is not possible to call a SQL function directly form C# code.  Create a procedure or view that calls the function or write string built SQL in C# and submit the SQL string to SQL server to run as your currently do (see your many other threads) with ADO.NET

    string sqlCommand = "SELECT dbo.SplitCSVs(@CSVString)"

    However, your approach does not make sense.  If you have the string in C# then simply use string.Split() as recommended several times not just in this thread but many of your others threads with the same problem.

    The root issue you are facing is a schema design bug.  As pointed out in another one of your threads, this approach causes complex hard to maintain code which is what you are know facing.  Basically, you are strong a table as a string in a table column.  This makes querying the column difficult and any query requires at least one table scan.  

    Thursday, September 20, 2018 2:57 PM
  • User-1499457942 posted

    Hi mgebhard

        Can u pls guide me how to use string.split and then use as IN in select statement

    Thanks

    Thursday, September 20, 2018 3:24 PM
  • User475983607 posted

    Can u pls guide me how to use string.split and then use as IN in select statement

    This is a new question and you should start a new thread. 

    Plus I already explained what to do above.  Create a stored procedure, View, or string built SQL in C#.

    string sqlCommand = "SELECT * FROM SomeTable WHERE SomeTable.Column IN (dbo.SplitCSVs(@CSVString))"

    IMHO, you've been on the forums long enough to read SQL reference documentation and put together something resembling an attempt at a solution.  Up to this point it seems you just copy code without understanding how the code works.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 20, 2018 3:41 PM
  • User-1716253493 posted

    To use 'abc,def,xyz' string IN statement

    @query = '....IN (' + @string + ')'
    EXEC @query

    Friday, September 21, 2018 1:11 AM