Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2008
    Posts
    9

    Unanswered: Parameter in select statement

    Hi,

    I need to pass a parameter into a stored procedure which values are dynamic always. Executing a variable which having the Select statement.


    Following query working fine.

    BEGIN
    DECLARE @strQuery NVARCHAR(MAX)
    DECLARE @CountryId INT
    DECLARE @CityId INT

    SET @CountryId = 2
    SET @CityId = ' AND CityId IN (23,45,85,86)'
    SELECT @strQuery= 'SELECT VendorId From Vendors
    WHERE CountryId = '+@CountryId+' '+@CityId+'
    ORDERBY CreatedDate'

    EXEC(@strQuery)
    END

    I need to execute above in an open select statement instead of executing @strQuery.

    Getting error as When trying through following script as "Conversion failed when converting the varchar value ' AND CityId (23,45,85,86)' to data type int"

    SELECT VendorId From Vendors
    WHERE CountryId = @CountryId + ' ' +@CityId
    ORDERBY CreatedDate

    Please help me to get it working through above select statement.

    Thanks

    Sharma

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    that's because "AND CityId (23,45,85,86)" is not an integer. Your variables can ONLY include the value you want to compare. They may not contain SQL to be executed literally. If that were possible, it would represent a massive security risk.

    What version of SQL server are you using? It's possible you could solve this with a table valued parameter.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2008
    Posts
    9
    Im using a SP as follows:

    CREATE PROCEDURE spGetVendor
    @CountryId INT,
    @CityId NVARCHAR(50)
    AS

    SET NOCOUNT ON;
    SELECT VendorId From Vendors
    WHERE CountryId = @CountryId + ' ' +@CityId
    ORDER BY CreatedDate
    GO

  4. #4
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    So CountryId is a combination of a country ID and a city ID (if so, that can be confusing at some point, especially to a newcomer). But anyway, is country_id on the vendorId table truly int? What value is being passed in for @CityId (I suspect this is where your problem lies)?

    --Oops should have read Teddy's reply before adding my own.
    -- You can't be late until you show up.

  5. #5
    Join Date
    Mar 2008
    Posts
    9
    No its separate. Adding ' AND CityId IN (23,45,67)' in 2nd parameter.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I didn't ask if you were using a stored procedure. I asked what version of SQL Server you were using so we can tell you if table valued parameters are an option.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2008
    Posts
    9
    Im using v2008. Also tried with table valued parameter. Problem is the 2nd parameter here for example im using for CityId but the param may contain condition for other columns to as follows:

    @param = ' AND CityId IN (23,27,45,67) AND Grade IN (1,4,7,9) '

    There are some other conditions which manipulate if web form and pass through @param to proc.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can create a table-valued parameter that is merely TABLE (IntegerValue INT), then populate the parameter with a DataTable containing a single int column (assuming you're using .NET, consult your language documentation for dealing with structured parameter types) and set the parameter's type to Structured. If you need to pass two sets of integers, you may use two separate parameters.

    Once you have passed in table-valued parameters, you can then alias them and work with them as if they were "real" tables.

    check out table valued parameters in BOL for syntax and such. This can solve your problem.
    Last edited by Teddy; 10-06-10 at 13:40.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The problem is that you were mixing numbers with strings. This should work:
    Code:
    BEGIN
        DECLARE @strQuery NVARCHAR(MAX)
        DECLARE @CountryId INT
        DECLARE @CityId NVARCHAR(MAX)
    
        SET @CountryId = 2
        
        SET @CityId = ' AND CityId IN (23,45,85,86)'
        SELECT @strQuery= 'SELECT VendorId From Vendors
        WHERE CountryId = ' + LTRIM(str(@CountryId)) + ' ' + @CityId + '
        ORDER BY CreatedDate'
    
        PRINT @strQuery
    END
    Never do an EXEC(@strQuery) before you know what you are sending to the DBMS. Use PRINT first until you are convinced your code generates the correct SQL scripts. It's also the easiest way to find out what MSSQL is giving error messages about.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Also be aware that there is a very good chance I can completely destroy your database and the server it's on if you use Wim's approach on a "web form" and fail to thwart rudimentary SQL injection techniques.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Teddy View Post
    ... if you use Wim's approach on a "web form" and fail to thwart rudimentary SQL injection techniques.
    Agree.

    I gave a solution to the error message in post#1, nothing else.
    Getting error as When trying through following script as "Conversion failed when converting the varchar value ' AND CityId (23,45,85,86)' to data type int"
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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