CODEWITHSUNDEEP
sqlsql-serverms-access
David Watson
David Watson

Reputation: 17

Constructing SQL Server stored procedure for array Input

I am struggling with this. I have looked at Table Level Variables but I am thinking this is way beyond my simple understanding at this stage of SQL.

The issue I have created is I have an array of ID values I am generating inside MS Access as a result of some other tasks in there. I am wanting to send these over to SQL Server to grab the jobs with the ID number that matches.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[get_Job] 
    @jobID VARCHAR,
    @JobIDs id_List READONLY 
AS
BEGIN
    SELECT @JobID AS JobID; 

    SELECT * 
    FROM Job
END;

Is my current stored procedure, however whilst I have been able to get it to return the JobID variable any list I added generates an error. If I insert only 1 ID into JobIDs, this doesn't generate a result either.

As I said I think I am punching well above my weight and am getting a bit lost in all this. Perhaps I can be directed to a better training resource or a site that explains this in baby steps or a book I can purchase to help me understand this? I would appreciate help with fixing the errors above but a fish teaching is probably better.

Thanks in advance

Upvotes: 1

Views: 8457

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 49049

The issue comes down to much is how long is the list of ID's you going to pass to t-sql is the issue?

You could take the passed list (assume it is a string), say like this from Access at a PT query

exec GetHotels '1,2,3,4,5,6,7,10,20,30'

So, the above is the PT query you can/could send to sql server from Access.

So, in above, we want to return records based on above?

The T-SQL would thus become:

CREATE PROCEDURE GetHotels
    @IdList nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @MySQL nvarchar(max)

    set @MySQL = 'select * from tblHotels where ID in (' + @IdList + ')'
    EXECUTE sp_executesql  @mysql

END
GO

Now, in Access, say you have that array of "ID" ? You code will look like this:

Sub MyListQuery(MyList() As String)

  ' above assumes a array of id
  ' take array - convert to a string list
  
  Dim strMyList As String
  strMyList = "'" & Join(MyList, ",") & "'"
  
  Dim rst     As DAO.Recordset
  
  With CurrentDb.QueryDefs("qryPassR")
     .SQL = "GetHotels " & strMyList
     Set rst = .OpenRecordset
  End With
  rst.MoveLast
  
  Debug.Print rst.RecordCount
  
End Sub

Unfortunately, creating t-sql on the fly is a "less" then ideal approach. In most cases, because the table is not known at runtime, you have to specific add EXEC permissions to the user. eg:

GRANT EXECUTE ON dbo.GetHotels TO USERTEST3

You find that such users can execute + run + use "most" store procedures, but in this case, you have to add specific rights with above grant due to the "table" not being known or resolved until runtime.

So, the above is a way to send a "given" array that you have, but from a general permissions point of view, and that of creating t-sql on the fly - I can't recommend this approach unless you are stuck, and have no other choice.

Edit

Here is a solution that works the same as above, but we don't have to create a SQL statement as a string.

    CREATE PROCEDURE [dbo].[GetHotels2]
        @IdList nvarchar(max)
    AS
    BEGIN
        SET NOCOUNT ON;

        -- create a table from the passed list

        declare @List table (ID int)
            while charindex(',',@IdList) > 0
            begin
             insert into @List (ID) values(left(@IDList,charindex(',',@IdList)-1))
             set @Idlist = right(@IdList,len(@IdList)-charindex(',',@IdList))
            end
             insert into @List (ID) values(@IdList)

        select * from tblHotels where ID in (select ID from @list)
    END

Upvotes: 1

marc_s
marc_s

Reputation: 754538

You didn't show us what that table-valued parameter looks like - but assuming id_List contains a column called Id, then you need to join this TVP to your base table something like this:

ALTER PROCEDURE [dbo].[get_Job] 
    @jobID VARCHAR,
    @JobIDs id_List READONLY 
AS
BEGIN
    SELECT (list of columns) 
    FROM Job j
    INNER JOIN id_List l ON j.JobId = l.Id;
END;

Seems pretty easy to me - and not really all that difficult to handle! Agree?

Also, check out Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use. Otherwise, as in your case - that @jobID VARCHAR parameter will be exactly ONE character long - and this is typically not what you expect / want ....

Upvotes: 1

Related Questions