Solved

Question about syntax for creating a stored procedure

Posted on 2006-11-02
12
344 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
Comment Utility
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
Comment Utility
>>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:npbaker1
Comment Utility
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
Comment Utility
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
Comment Utility
More importantly, the unpack ID's function can be reused from any other stored procedure that you write.
0
 

Author Comment

by:npbaker1
Comment Utility
Ok cool thanks
0
 
LVL 3

Expert Comment

by:RickBeebe
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

13 Experts available now in Live!

Get 1:1 Help Now