Stored Procedure Parameter


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Stored Procedure Parameter

  1. #1
    John Hadzima Guest

    Stored Procedure Parameter


    Is it possible to pass a parameter to a stored procedure containing a text
    string that you can use as a where clause in the stored procedure? I know
    you can pass individual parameters for the where clause but the number of
    items in my where clause will be variable so I would like to put them all
    in a single variable.

    For example, in the stored procedure the variable '@skillstr' would contain
    the where clause:

    Alter Procedure Skills @empid INT, @skillstr CHAR(100)
    As
    SELECT skill_id, skill_name FROM skills WHERE @skillstr ORDER BY
    skill_name

    When calling the stored procedure you would pass the where clause as a text
    string which would have a different number of items each time:

    EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28"

    OR

    EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28 OR skill_id=30"


    The syntax on this is not correct but is this possible written another way?
    Any help would be appreciated.





  2. #2
    Colin McGuigan Guest

    Re: Stored Procedure Parameter

    John Hadzima wrote in message <395a0509@news.devx.com>...
    >
    >Is it possible to pass a parameter to a stored procedure containing a text
    >string that you can use as a where clause in the stored procedure? I know
    >you can pass individual parameters for the where clause but the number of
    >items in my where clause will be variable so I would like to put them all
    >in a single variable.
    >
    >For example, in the stored procedure the variable '@skillstr' would contain
    >the where clause:

    <snip>

    Create the SQL string inside the stored procedure, and then EXEC it.

    DECLARE @SQL varchar(255)

    SET @SQL = 'SELECT skill_id, skill_name FROM skills WHERE ' + @skillstr + '
    ORDER BY skill_name'

    EXEC (@SQL)


    --
    Colin McGuigan




  3. #3
    Narayana Pakala Guest

    Re: Stored Procedure Parameter



    I assume that you are using Microsoft SQL Server.

    Try sp_executesql.

    For this you got to put your sql statement in a string and pass it as a parameter
    to sp_executesql.

    How to: Create a nvarchar string in your stored procedure
    assign your sql statement to that string
    Concateate your params to your sql statement
    pass this final string to sp_executesql


    "John Hadzima" <jhadzima@alphas.com> wrote:
    >
    >Is it possible to pass a parameter to a stored procedure containing a text
    >string that you can use as a where clause in the stored procedure? I know
    >you can pass individual parameters for the where clause but the number of
    >items in my where clause will be variable so I would like to put them all
    >in a single variable.
    >
    >For example, in the stored procedure the variable '@skillstr' would contain
    >the where clause:
    >
    >Alter Procedure Skills @empid INT, @skillstr CHAR(100)
    >As
    > SELECT skill_id, skill_name FROM skills WHERE @skillstr ORDER BY
    >skill_name
    >
    >When calling the stored procedure you would pass the where clause as a text
    >string which would have a different number of items each time:
    >
    >EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28"
    >
    >OR
    >
    >EXEC BuildExp @empid=1, @skillstr="skill_id=27 OR skill_id=28 OR skill_id=30"
    >
    >
    >The syntax on this is not correct but is this possible written another way?
    >Any help would be appreciated.
    >
    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center