Solved

Question about syntax for creating a stored procedure

Posted on 2006-11-02
12
347 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
  • 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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now