Wrong datatype for SP?

Question

Wrong datatype for SP?

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
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?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 2

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 4

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

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 5

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 +'.

SrcName Default port Points: 1460 More actions · Answer 6

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'' '

SrcName Default port Points: 1460 More actions · Answer 7

Input format is wrong and this line

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 10

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)

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 11

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)

SrcName Default port Points: 1460 More actions · Answer 12

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

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 13

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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