Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25

    Unanswered: Using char paramaters with procedure

    Hi,
    I've to make some procedures that will get char parameters to build a query, here is a simple exemple of what i want to do:

    CREATE PROCEDURE SimpleQuery (@MyStatement char(255))
    as
    @MyStatement
    GO

    Then I want to call my proc like this:
    SimpleQuery ('SELECT * FROM Table1')

    the problem is that T-SQL didn't interpret my char like a statement, is there a way to do this

    (the exemple is very useless but I want to use parameters to add some restrictions on a WHERE clause, so if I made my exemple work, my problem will be solved)

    Many thanks for any idea!

    Gauthier

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You need to add the EXECUTE statement within your stored procedure.

    Example:

    CREATE PROCEDURE SimpleQuery (@MyStatement char(255))
    AS
    EXECUTE (@MyStatement)
    GO

    SimpleQuery "select * from sysobjects"

    Also look at the MS SQL Server stored procedure sp_executesql

    Example: (pubs database)

    sp_executesql
    N'select * from pubs.dbo.employee where job_lvl=@level',
    N'@level tinyint',
    @level=35

  3. #3
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25
    Hi,

    thanks for your reply, and sorry cause I've not tested yet.
    I will test this solution as soon as I plan to work on the project which need some parametric statements.

    Thanks again

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    should use single quotes as string delimitters.

    exec SimpleQuery 'select * from sysobjects'

  5. #5
    Join Date
    Dec 2001
    Location
    Seattle, WA; USA
    Posts
    9

    Cool Dynamic SQL

    You also might want to read these articles on Dynamic SQL...

    http://www.sqlteam.com/item.asp?ItemID=4599

    http://www.sqlteam.com/item.asp?ItemID=4619

  6. #6
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25
    Hi,

    these articles seems to be very compliant answer

    thanks!

  7. #7
    Join Date
    Oct 2001
    Location
    FRANCE
    Posts
    25
    Hi,

    I've solved my issu using a user defined function, so I can use it and add as criteria as I want.

    Thanks again

Posting Permissions

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