Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Question about syntax for creating a stored procedure

Posted on 2006-11-02
12
Medium Priority
?
357 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 29

Accepted Solution

by:
Nightman earned 2000 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

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.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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