Post reply

Wrong datatype for SP?

  • August 28, 2013 at 7:56 am

    #298262

    When I try to execute a SP as shown below:

    EXEC eusp_e5_eSM_AS01_MaterialItemContainerlabelReport 'MC-00000002-13-0001','MC-00000002-13-0002'

    The following error message is shown:

    Msg 8144, Level 16, State 2, Procedure eusp_e5_eSM_AS01_MaterialItemContainerlabelReport, Line 0

    Procedure or function eusp_e5_eSM_AS01_MaterialItemContainerlabelReport has too many arguments specified.

    I guess the error is due to wrong dataype of SP parameter. The code is below:

    ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialItemContainerlabelReport]

    @containerCodes nvarchar(MAX)

    What should this nvarchar(MAX) be changed to accommodate the comma seprated values?

  • August 28, 2013 at 8:05 am

    #1645593

    It's not data type problems. It's that the procedure has only one parameter and you're passing two parameters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 28, 2013 at 8:14 am

    #1645598

    Thanks GilaMonster, You are correct.

    But my requirement, is to pass comma separated containerCodes as parameters. That should be supported.

    i.e. Single containerCodes and multiple containerCodes should be supported.

    i.e. EXEC eusp_e5_eSM_AS01_MaterialItemContainerlabelReport 'MC-00000002-13-0001,MC-00000002-13-0002'

    How can I achieve it ? By modifying the datatype or any other way..??

    This is my code:

    ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialItemContainerlabelReport]

    @containerCodes nvarchar(MAX)

    AS

    SELECT

    MC.MaterialItemContainerCode,

    MC.ReceptionDate,

    C.clientName ,

    MI.materialItemName

    FROM

    MaterialsItemsContainers MC

    INNER JOIN MaterialsItems MI WITH(NOLOCK)

    ON (MC.materialItemIncId = MI.materialItemIncId AND MC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)

    INNER JOIN Clients AS C WITH(NOLOCK)

    ON (C.clientSqlId=MI.clientSqlId AND C.clientIncId=MI.clientIncId AND C.isDeleted=0x0)

    WHERE MC.isDeleted=0x0 AND MC.materialItemContainerCode IN (@containerCodes)

  • August 28, 2013 at 8:17 am

    #1645600

    Well, your query won't work with comma-delimited values, so there's not much point in passing them.

    IN (@Variable) is equivalent to = @Variable, so if you pass a comma-delimited list that query will only match rows where the row has an actual comma-delimited value.

    Maybe try a different approach, a table-type parameter should work.

    p.s. Ditch that damn nolock. Do the users know that their reports can be incorrect (missing rows, duplicate rows) because you decided to add that hint? Are they OK with the fact that their reports are no more than an approximation?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 28, 2013 at 11:23 pm

    #1645829

    I need a help to modify my query to give comma-separated values as SP parameter. Any help is appreciated. Thanks in advance.

  • August 29, 2013 at 12:45 am

    #1645842

    I got a error executing the following code. Please help.

    ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialItemContainerlabelReport_COPY]

    @containerCodes varchar(MAX)

    AS

    declare @sql varchar(MAX)

    select @sql = 'SELECT

    MC.MaterialItemContainerCode,

    MC.ReceptionDate,

    C.clientName ,

    MI.materialItemName

    FROM

    MaterialsItemsContainers MC

    INNER JOIN MaterialsItems MI

    ON (MC.materialItemIncId = MI.materialItemIncId AND MC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)

    INNER JOIN Clients AS C

    ON (C.clientSqlId=MI.clientSqlId AND C.clientIncId=MI.clientIncId AND C.isDeleted=0x0)

    WHERE MC.isDeleted=0x0 AND MC.materialItemContainerCode IN (" + @containerCodes +")'

    exec(@sql)

    I executed the procedure this way:

    EXEC eusp_e5_eSM_AS01_MaterialItemContainerlabelReport_COPY 'MC-00000002-13-0001,MC-00000002-13-0002'

    AND this way:

    EXEC eusp_e5_eSM_AS01_MaterialItemContainerlabelReport_COPY 'MC-00000002-13-0001'

    The error message is:

    Msg 207, Level 16, State 1, Line 12

    Invalid column name ' + @containerCodes +'.

  • August 29, 2013 at 12:51 am

    #1645844

    Hi Junglee_George,

    make your query dynamic.

    declare @sqlText varchar(max)

    set @sqlText = 'SELECT

    MC.MaterialItemContainerCode,

    MC.ReceptionDate,

    C.clientName ,

    MI.materialItemName

    FROM

    MaterialsItemsContainers MC

    INNER JOIN MaterialsItems MI WITH(NOLOCK)

    ON (MC.materialItemIncId = MI.materialItemIncId AND MC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)

    INNER JOIN Clients AS C WITH(NOLOCK)

    ON (C.clientSqlId=MI.clientSqlId AND C.clientIncId=MI.clientIncId AND C.isDeleted=0x0)

    WHERE MC.isDeleted=0x0 AND MC.materialItemContainerCode IN ('+@containerCodes+') '

    And be careful with parameter. Because @containerCodes is varchar you set input value in this format

    ' ''MC-00000002-13-0001'',''MC-00000002-13-0002'' '

  • August 29, 2013 at 12:52 am

    #1645845

    Input format is wrong and this line

    MC.materialItemContainerCode IN (" + @containerCodes +")'

  • August 29, 2013 at 1:52 am

    #1645867

    Junglee_George (8/28/2013)

    I need a help to modify my query to give comma-separated values as SP parameter. Any help is appreciated. Thanks in advance.

    -- change your procedure

    ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialItemContainerlabelReport]

    @containerCodes nvarchar(MAX)

    AS

    SELECT

    MC.MaterialItemContainerCode,

    MC.ReceptionDate,

    C.clientName ,

    MI.materialItemName

    FROM MaterialsItemsContainers MC

    INNER JOIN MaterialsItems MI --WITH(NOLOCK)

    ON MC.materialItemIncId = MI.materialItemIncId

    AND MC.materialItemSqlId = MI.materialItemSqlId

    AND MI.isDeleted = 0x0

    INNER JOIN Clients AS C --WITH(NOLOCK)

    ON C.clientSqlId = MI.clientSqlId

    AND C.clientIncId = MI.clientIncId

    AND C.isDeleted = 0x0

    WHERE MC.isDeleted = 0x0

    AND MC.materialItemContainerCode IN (SELECT Item FROM dbo.DelimitedSplit8K(@containerCodes,','))

    RETURN

    GO

    -- change your usage of procedure

    EXEC eusp_e5_eSM_AS01_MaterialItemContainerlabelReport 'MC-00000002-13-0001,MC-00000002-13-0002'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 29, 2013 at 2:52 am

    #1645888

    Junglee_George (8/28/2013)

    I need a help to modify my query to give comma-separated values as SP parameter. Any help is appreciated. Thanks in advance.

    Try a table-valued parameter.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 29, 2013 at 9:45 pm

    #1646221

    Junglee_George (8/28/2013)

    What should this nvarchar(MAX) be changed to accommodate the comma seprated values?

    How many comma separated values can you have?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 29, 2013 at 9:46 pm

    #1646222

    SrcName (8/29/2013)

    Hi Junglee_George,

    make your query dynamic.

    declare @sqlText varchar(max)

    set @sqlText = 'SELECT

    MC.MaterialItemContainerCode,

    MC.ReceptionDate,

    C.clientName ,

    MI.materialItemName

    FROM

    MaterialsItemsContainers MC

    INNER JOIN MaterialsItems MI WITH(NOLOCK)

    ON (MC.materialItemIncId = MI.materialItemIncId AND MC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)

    INNER JOIN Clients AS C WITH(NOLOCK)

    ON (C.clientSqlId=MI.clientSqlId AND C.clientIncId=MI.clientIncId AND C.isDeleted=0x0)

    WHERE MC.isDeleted=0x0 AND MC.materialItemContainerCode IN ('+@containerCodes+') '

    And be careful with parameter. Because @containerCodes is varchar you set input value in this format

    ' ''MC-00000002-13-0001'',''MC-00000002-13-0002'' '

    Oh, be careful now... there's nothing in that code to prevent the inevitable SQL Injection attack. I strongly recommend NOT doing it that way.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 30, 2013 at 2:18 am

    #1646243

    Hi, SSC-Dedicated

    Based on your experience,

    i would be appreciate if you explain in detail how SQL Injection will pass this.

    P.S.

    maybe in this case it's no so important, this select statement is not of crucial importance

  • August 30, 2013 at 3:04 am

    #1646253

    Hi

    I achieved it through this way. The code below is working.

    If any flaws are there in my approach, replies are welcome.

    ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialItemContainerlabelReport]

    @containerCodes varchar(MAX)

    AS

    SELECT

    MC.MaterialItemContainerCode,

    MC.ReceptionDate,

    C.clientName ,

    MI.materialItemName

    FROM

    MaterialsItemsContainers MC

    INNER JOIN MaterialsItems MI

    ON (MC.materialItemIncId = MI.materialItemIncId AND MC.materialItemSqlId = MI.materialItemSqlId AND MI.isDeleted=0x0)

    LEFT JOIN Clients AS C

    ON (C.clientSqlId=MI.clientSqlId AND C.clientIncId=MI.clientIncId AND C.isDeleted=0x0)

    WHERE MC.isDeleted=0x0 AND charindex(MC.materialItemContainerCode,@containerCodes)<>0

  • August 30, 2013 at 3:14 am

    #1646255

    I hope that good performance is not a requirement.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply