Solved

Question about syntax for creating a stored procedure

Posted on 2006-11-02
12
354 Views
Last Modified: 2012-08-13
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
Comment
Question by:npbaker1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17859329
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
 
LVL 17

Expert Comment

by:akshah123
ID: 17859425
>>>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
 

Author Comment

by:npbaker1
ID: 17859443
In the select statement for the WHERE clause should it just be
WHERE FK=id1 OR FK =id2
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17859454
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17859462
Don't use the OR option, especially with lost of values - you are ASKING for a performance nightmare.
0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 17859527
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
 

Author Comment

by:npbaker1
ID: 17859596
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17859670
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17859681
More importantly, the unpack ID's function can be reused from any other stored procedure that you write.
0
 

Author Comment

by:npbaker1
ID: 17859695
Ok cool thanks
0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 17859899
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17859920
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question