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

Answer 1

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.

Answer 2

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)

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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/

Answer 7

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

Answer 8

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

Answer 9

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.