none
What would be the code for a stored procedure when checking for variables in one table and then seeing returning an item in another table if any of them exist

    Question

  • I have two tables and I want to feed a variable into a stored procedure, find the records in the first table that have a value in a column that matches the value of the fed variable, put the founds records in an arraylist, and then see if any of the values in the arraylist exist in a  column of another table.

    What is the most efficient way of doing that?

    Thanks


    DCSSR

    Monday, January 21, 2013 5:25 AM

All replies

  • Can you provide your table structures and desired putput with some sample data? This would help us to help you better.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, January 21, 2013 5:31 AM
  • try this

    --declare a table variable in the procedure with structure similar to fetched column..

    declare @table_name TABLE(column_name datatype )

    --then insert the fetched values into this table as

    insert into @table_name(column_name)

    select fetched_column from org_table_1 where fetched_column_value(s)=@parameter1

    --and then query the table u jst declared like  a normal table to find whether these values are there in table2 using    IN

    select * from org_table_2 where column_to_match in (select * from table_name)


    • Proposed as answer by Rahul17D Monday, January 21, 2013 6:31 AM
    • Unproposed as answer by Rahul17D Monday, January 21, 2013 6:32 AM
    • Edited by Rahul17D Monday, January 21, 2013 6:42 AM
    Monday, January 21, 2013 6:30 AM
  • In this case I think you need to create inline table valued UDF .

    Sorry untested

    CREATE FUNCTION dbo.udf
    (
      @id INT
    )
    RETURNS TABLE
    AS

    RETURN SELECT
             *
           FROM
             Orders
           WHERE
             OrderID = @id
    GO

    SELECT * FROM Tbl2 CROSS APPLY  dbo.udf (colname) AS t


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 21, 2013 6:34 AM
  • Create function worked perfectly in SQL and got the results I was looking for. How do I call the function from vb codebehind?

    Thanks


    DCSSR

    Monday, January 21, 2013 12:53 PM
  • Same as SELECT statement

    http://www.devasp.net/net/articles/display/1385.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 21, 2013 1:06 PM
  • Hi

    insert into to a global temp table or table type variable and feed it to sp after that  compare it with another one table's data.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, January 21, 2013 1:16 PM
  • I might need a little spoon feeding…for some reason it just isn’t seeping in this morning.

    So when I call a stored procedure I do this.

    Dim strSQL a string = "anSqlStoredProcedureName"

      Try

          Using objConnection As New SqlConnection(strConnection)

               Using objCommand As New SqlCommand(strSQL, objConnection)

                    objCommand.CommandType = CommandType.StoredProcedure

                        objConnection.Open() 'Open the Connection.

                           

    I tried replacing strSQL as the name of the function and then changing the objCommand.CommandType to a commandType.function, but type "function" doesn’t exist.

    I probably need a little spoon feeding.

    Thanks


    DCSSR

    Monday, January 21, 2013 1:20 PM
  • Can you post your current SQL code and your VB code? If you need to select from the function, then your command will be

    strSQL = "select * from dbo.MyUDF()"

    and your obj.CommandType = CommandType.Text



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, January 22, 2013 3:05 AM
  • if u r using a function then u need to change your command string to

    => strSQL="select * from function_name(parameter(s))"

    and change your command type to

    =>  objCommand.CommandType=CommandType.Text

    or else u can use a stored procedure and declare a local table variable to store values..as i mentioned in my previous post.

    Tuesday, January 22, 2013 5:01 AM