Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121

    Unanswered: Passing Parameters to a Stored Procedure

    Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
    I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :

    CREATE PROCEDURE ExplodeDilutions
    @MyLSN AS VarChar(8)

    AS

    declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
    FROM TSample
    WHERE (TSample.ISmpShortCode = @MyLsn)

    The value I'm passing (@MyLSN) is 24/12359 (one LS Number)

    The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:

    CREATE PROCEDURE ExplodeDilutions
    @MyLSN AS VarChar(50)

    AS

    declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
    FROM TSample
    WHERE (TSample.ISmpShortCode IN (@MyLsn))

    The value I'm passing (@MyLSN) is '24/12359','24/12360'

    This is where the problem begins because the stored procedure now doesn't return a recordset.

    If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
    Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.

    So if someone could point me in the right direction then that would be much appreiciated.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    492

    Re: Passing Parameters to a Stored Procedure

    There's two difficulties here;
    The first is that the value '24/12359' and '24/12360' may not get passed as a whole. The second is that, once the two/three/ are passed as a whole, the IN will try to operate on the whole, instead of the parts. What you'll have to do is figure out a way to pass it as a whole then pull it apart again.

  3. #3
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Call me stupid (most peeps do) but I'm not sure what your trying to say

  4. #4
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    I think what Kaiowas is saying is that the IN operator works on multiple items while you are providing only one.

    Instead of two 8-byte values for the operator to use ('24/12359' and '24/12360') , you are supplying it with a single 17 (?) byte value ('24/12359,24/12360').

  5. #5
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Thats correct. I need (if it's possible) to just pass one parameter to the Stored Proc, as there could be several LSnumbers (I'm just using 2 as an example) up to twenty. But I won't know this until runtime and it could be different every time

  6. #6
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    this is what u want have to pass:
    ('''24/12359'',''24/12360''')

  7. #7
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    That accepts the syntax but it doesn't return a recordset

  8. #8
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    can u run the select statement for the cursor independently and check if the records are been selected? and what processing is done after selection of records? i m sure there is something after that cursor statement.
    if u could post the whole sp it would be helpful.
    harshal.

  9. #9
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Worked it out!! Found 2 ways of doing it (there be more but I stopped thinking about it now!).
    1) Forgot about trying to pass the string ('24/12359,24/12360) to the stored proc and indeed remove that part of the 'WHERE' clause from the TSQL statement. Instead populate a table with the list of LS Numbers and link in this table to the SQL statement, then call the Stored Proc which will now display the correct recordset.

    or

    2) Create a user defined function called udf_Txt_SplitTAB which contains the following:

    CREATE FUNCTION dbo.udf_Txt_SplitTAB (

    @sInputList varchar(8000) -- List of delimited items
    , @Delimiter char(1) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (Item varchar(8000))
    WITH SCHEMABINDING
    /*
    * Returns a table of strings that have been split by a delimiter.
    * Similar to the Visual Basic (or VBA) SPLIT function. The
    * strings are trimmed before being returned. Null items are not
    * returned so if there are multiple separators between items,
    * only the non-null items are returned.
    * Space is not a valid delimiter.
    *
    * Example:
    select * FROM dbo.udf_Txt_SplitTAB('abcd,123, 456, efh,,hi', ',')
    *
    * Test:
    DECLARE @Count int, @Delim char(10), @Input varchar(128)
    SELECT @Count = Count(*)
    FROM dbo.udf_Txt_SplitTAB('abcd,123, 456', ',')
    PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3
    THEN 'Worked' ELSE 'ERROR' END
    SELECT @DELIM=CHAR(10)
    , @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
    SELECT @Count = Count(*)
    FROM dbo.udf_Txt_SplitTAB(@Input, @Delim)
    PRINT 'TEST 2 LF :' + CASE WHEN @Count=2
    THEN 'Worked' ELSE 'ERROR' END
    *
    * Copyright 2003 Andrew Novick http://www.NovickSoftware.com
    * You may use this function in any of your SQL Server databases
    * including databases that you sell, so long as they contain
    * other unrelated database objects. You may not publish this
    * UDF either in print or electronically.
    * Published in T-SQL UDF of the Week Newsletter Vol 1 #29
    http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
    ************************************************** *************/
    AS BEGIN

    DECLARE @Item Varchar(8000)
    DECLARE @Pos int -- Current Starting Position
    , @NextPos int -- position of next delimiter
    , @LenInput int -- length of input
    , @LenNext int -- length of next item
    , @DelimLen int -- length of the delimiter

    SELECT @Pos = 1
    , @DelimLen = LEN(@Delimiter) -- usually 1
    , @LenInput = LEN(@sInputList)
    , @NextPos = CharIndex(@Delimiter, @sInputList, 1)

    -- Doesn't work for space as a delimiter
    IF @Delimiter = ' ' BEGIN
    INSERT INTO @List
    SELECT 'ERROR: Blank is not a valid delimiter'
    RETURN
    END


    -- loop over the input, until the last delimiter.
    While @Pos <= @LenInput and @NextPos > 0 BEGIN

    IF @NextPos > @Pos BEGIN -- another delimiter found
    SET @LenNext = @NextPos - @Pos
    Set @Item = LTrim(RTrim(
    substring(@sInputList
    , @Pos
    , @LenNext)
    )
    )
    IF LEN(@Item) > 0
    Insert Into @List Select @Item
    -- ENDIF

    END -- IF

    -- Position over the next item
    SELECT @Pos = @NextPos + @DelimLen
    , @NextPos = CharIndex(@Delimiter
    , @sInputList
    , @Pos)
    END

    -- Now there might be one more item left
    SET @Item = LTrim(RTrim(
    SUBSTRING(@sInputList
    , @Pos
    , @LenInput-@Pos + 1)
    )
    )

    IF Len(@Item) > 0 -- Put the last item in, if found
    INSERT INTO @List SELECT @Item

    RETURN
    END


    Then in the syntax of the SQL statement change the IN SELECT statement so it now reads:

    WHERE (TSample.ISmpShortCode IN (SELECT * FROM dbo.udf_Txt_SplitTAB(@MYLSN,',')))

    Now when the stored procedure is called the container @MyLSN should contain this (including the first and last apostrophe's) string '24/12359,24/12360'

    And all works tickety boo. The first answer seems like a bit of a hack so I've gone for the second.

    Thanks for your time and effort.

    And doubtless you'll hear from me again soon!!

  10. #10
    Join Date
    Mar 2004
    Posts
    45
    Use the front end to put the list of LSN values into a temporary table. Join to that temporary table in the stored procedure. No need for UDF. Also probably no need for cursor, but you did not post that code.

    Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •