Problem when pass parameters on Query

Question

Problem when pass parameters on Query

luissantos

SSCertifiable

Points: 7385
More actions
September 1, 2014 at 9:51 am

#310510

Hello comunity
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
Someone could give me some help.
Many thanks
Luis Santos

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

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

KS Hall of Fame Points: 3323 More actions · Answer 1

Try the below code. You will need to have the DelimitedSplit8k function created. Please read the article here[/url] to understand how the function works and the code.

DECLARE @myvendedor AS varchar(255)

SET @myvendedor = '87,30'

SELECT top 10 ECOM.COM1,*

from ecom (nolock)

cross apply dbo.DelimitedSplit8K(@myvendedor,',') as s

WHERE ecom.PORVEND=1

AND ECOM.VENDEDOR = s.Item

luissantos SSCertifiable Points: 7385 More actions · Answer 2

Hello Sowbhari

Thanks for your reply, also where i can found this Split function.

Many thanks

Luis Santos

KS Hall of Fame Points: 3323 More actions · Answer 3

You can find the code and the article in the below link

http://qa.sqlservercentral.com/articles/Tally+Table/72993/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

CELKO (9/1/2014)
I have the following code and I want to pass more than one value:
DECLARE @myvendedor VARCHAR (255);
SET @my_vendor = '87,30';
Please read any book on RDBMS. In the first 2-3 chapters, you will "First Normal Form" (1NF) and "scalar values"; this is the foundation of RDBMS. We do not do this crap in SQL. Each column is a scalar value, drawn from a domain set.

The only problem is that a parameter is not a table (it could be, but not necessarily). Having that clear, there's no 1NF violation since each value of the column is a scalar value.

By the way, you lost me on the third paragraph of your first article when you wrote "store procedure".

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183686 More actions · Answer 5

CELKO (9/1/2014)
there's no 1NF violation since each value of the column is a scalar value.
NO! The column itself has to be scalar, not each value in a list, not each elements of an array, not an element of a lattice or variant record, etc. that some noob wants to cram into a string.
Back to basics! Define a theta operator for a array. Makes as much sense as asking "on a scale from 1 to 10, what color is your favorite letter of the alphabet?"

Now you're talking nonsense. A column is a set of values, the intersection of a row and a column is a value.

If you're trying to propose the use of table-valued parameters, then I support it. No need to split strings or get into normalization debates.

If you're just trying to avoid arrays just because RDBMs don't work with arrays, that's an example of your closed mind.

Any splitting function will convert a delimited list into a nice normalized table. What's the problem with that?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TomThomson SSC Guru Points: 104773 More actions · Answer 6

CELKO (9/1/2014)
there's no 1NF violation since each value of the column is a scalar value.
NO! The column itself has to be scalar,

That requirement would mean that a table can hold exactly one row - it couldn't hold 0 rows or 2 or more rows because then its columns would not be scalars.

You should try to avoid making such carelessly idiotic assertions even when you are concentrating on being obnoxious instead of helpful, since that sort of BS doesn't advance your cause at all (unless your aim is to be laughed at).

Tom

luissantos SSCertifiable Points: 7385 More actions · Answer 7

Hello Sowbhari

regarding the Split function is not exactly what i need , i will try to explain

i need to passed several integer values separate with ',' comma

ex: 8,9,10

to run this query

SELECT ECOM.COM1 from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (8,9,10)

how can do that, because i´am sure that i could be possible.

Regarding my query could you help me ?

Many thanks

Luis Santos

luissantos SSCertifiable Points: 7385 More actions · Answer 8

Hello comunity

I solve the problem.

Many thanks

Luis Santos

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

luissantos (9/3/2014)
Hello comunity
I solve the problem.
Many thanks
Luis Santos

Hi Luis

Can you post up the solution you are using? I'm sorry your thread went a little off-track. Luis' suggestion should work just fine for you.

^{“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

luissantos SSCertifiable Points: 7385 More actions · Answer 10

Hello Chris

I solve the problem like this:

1. Create the function

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))

RETURNS @TempTab TABLE

(id int not null)

AS

BEGIN

;-- Ensure input ends with comma

SET @InStr = REPLACE(@InStr + ',', ',,', ',')

DECLARE @sp INT

DECLARE @VALUE VARCHAR(1000)

WHILE PATINDEX('%,%', @INSTR ) <> 0

BEGIN

SELECT @sp = PATINDEX('%,%',@INSTR)

SELECT @VALUE = LEFT(@INSTR , @sp - 1)

SELECT @INSTR = STUFF(@INSTR, 1, @sp, '')

INSERT INTO @TempTab(id) VALUES (@VALUE)

END

RETURN

END

GO

Example to use:

DECLARE @LIST VARCHAR(200)

SET @LIST = '1,3,87,45'

SELECT cm FROM cm3 WHERE cm IN (SELECT * FROM dbo.CSVToTable(@LIST))

The workaround is to use : IN (SELECT * FROM dbo.CSVToTable(@LIST)

and not : IN (@LIST)

Look the article in:

http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S

Best regards

Luis Santos

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

Hi Luis

Spot on - split the comma-delimited list into a table. The function looks horribly slow though, I'd recommend you switch to the SSC function which is linked in the second post of this thread.

^{“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

luissantos SSCertifiable Points: 7385 More actions · Answer 12

Hello Chris

Thanks for your reply.

In my case the query run fast enough.

Also, can you rewrite my select using the suggestion on the 2 post to see what changes to be apply?

Best regards

Luis Santos

Luis Cazares SSC Guru Points: 183686 More actions · Answer 13

Hi Luis,

First of all, I'm sorry for the comments made that didn't help you on your task.

Second, you shouldn't keep something that is fast enough right now. You should think ahead or you might find performance problems in the future.

This time, you just need to create the function and replace it in your code. It would be better if you understand how does it work and that's why we don't just post its definition.

Your code would change from this:

DECLARE @LIST VARCHAR(200)

SET @LIST = '1,3,87,45'

SELECT cm

FROM cm3

WHERE cm IN (SELECT id FROM dbo.CSVToTable(@LIST))

To this:

DECLARE @LIST VARCHAR(200)

SET @LIST = '1,3,87,45'

SELECT cm

FROM cm3

WHERE cm IN (SELECT item FROM dbo.DelimitedSplit8K(@LIST, ','))

I wouldn't recommend using JOIN or APPLY because it could generate duplicates. In this case, I prefer to use IN or EXISTS.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

luissantos SSCertifiable Points: 7385 More actions · Answer 14

Hello Chris and Luis

Thanks for your interest and help.

also i agree with you when you say that we must always thinking to develop a solution that is the most efficient possible now and in the future.

In my case it was not forgotten, just had to find a temporary solution to resolve the issue as soon as possible.

I want to thank sending the script with the changes, and will make use of it to test the performance.

I also read several articles on the topic, using temporary tables, CTEs and cursors which were measured which scripts are more eficiente depending on CPU utilization or I / O.

thanks again for sharing information.

Best regards

Luis Santos