Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Question about syntax for creating a stored procedure

I have 2 tables in my DB.  I would like to create a stored procedure which will take multiple values that are primary keys of Table 1 as parameters and then perform a search on Table 2 based on those values to pull the records that relate to the certain items in Table 1 that I am looking at.  I gather I cannot pass an array but if someone could give me the syntax for something similar I would appreciate it.  Note the number of values to be passed to the procedure is not fixed.
0
npbaker1
Asked:
npbaker1
  • 6
  • 3
  • 2
  • +1
1 Solution
 
NightmanCTOCommented:
no, you cannot pass in an array, but there are other ways of handling this.

Perhaps if you post a bit more of the logic we can assist.
0
 
akshah123Commented:
>>>I gather I cannot pass an array but if someone could give me the syntax for something similar I would appreciate it.  Note the number of values to be passed to the procedure is not fixed.

You cannot pass in the array.  However, you can pass in the texfield with comma separated values of the ids.  If all you need to pass it a bunch of numeric ids.  You can simply provide them as a string.  Then, in your stored procedure you can manipulate the string to perform intended queries.
0
 
npbaker1Author Commented:
In the select statement for the WHERE clause should it just be
WHERE FK=id1 OR FK =id2
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NightmanCTOCommented:
An example would be to write a function that returns a populated table variable, and join to it.

e.g.


CREATE function dbo.UnpackIDs
(
  @idList  varchar(8000)
) returns @ids table (liID int) as
begin
  declare @start int set @start = 1
  declare @end   int set @end   = 0
  declare @cId   char(20)
  declare @id    int

  select @end = charindex(',', @idList, @start)
  if @end = 0
    set @end = len(@idList) + 1
  while @end > 0
  begin
    set @cId = substring(@idList, @start, @end-@start)
    if isnumeric(@cId) = 1
    begin
      set @id = cast(@cId as int)
      insert into @ids select @id where not exists (select * from @ids where liID = @id)
    end
    set @start = @end + 1
    select @end = charindex(',', @idList, @start)
    if @end = 0 and @start <= len(@idList)
    begin
      select @end = len(@idList) + 1
    end
  end

  if @start < len(@idList)
  begin
    set @cId = substring(@idList, @start, len(@idList)-@start+1)
    if isnumeric (@cId) = 1
    begin
      set @id = cast(@cId as int)
      insert into @ids select @id where not exists (select * from @ids where liID = @id)
    end
  end
  return
end

then your select statement
SELECT j.* FROM Jobs j INNER JOIN dbo.UnpackIDs(@CommaDelimitedList) up ON j.JobNo = up.liID
0
 
NightmanCTOCommented:
Don't use the OR option, especially with lost of values - you are ASKING for a performance nightmare.
0
 
RickBeebeCommented:
I understand the need to join table1 to table2 in your procedure:

SELECT TABLE2.*
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.KEY1 = TABLE2.KEY2
AND TABLE1.KEY2 = TABLE2.KEY2
...

What is the FILTER criteria (ARRAY) you need to pass to the procedure?
0
 
npbaker1Author Commented:
I am looking to fill a table with only the related data so if I am lookin at say id numbers 1 and 2 I want to create a stored procedure that will return all rows that have 1 or 2 as there foreign key.
0
 
NightmanCTOCommented:
Create the function that I gave you on the database.

Then

Create Procedure MyProcedure
(
 @IDList varchar(8000)
)
SELECT t1.* FROM MyTable t INNER JOIN dbo.UnpackIDs(@IDList ) up ON t.FKColumn= up.liID


This is extensible up to hundreds of objects. (1000 3 character numbers - e.g. 123,143,145)
0
 
NightmanCTOCommented:
More importantly, the unpack ID's function can be reused from any other stored procedure that you write.
0
 
npbaker1Author Commented:
Ok cool thanks
0
 
RickBeebeCommented:
I typically use XML when I need an array of values.  (Then convert the XML to a temp table in the proc and join to this table)

I can send you an example if you are interested.

Thanks,

-Rick
0
 
NightmanCTOCommented:
XML CAN work as well. However if you are going to use it, do it in a function to make it extensible to other procs later on.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now