Conversion failed when converting the varchar value ’1,2,3′ to data type smallint. I got this error message on SQL Stored Procedure. So what is going on here?

I try to put variable @factory_id ’1,2,3′ into “SELECT * FROM tempTable WHERE F.Factory_id in (@factory_id )“. But system rejected this code. So what is the solution?

By changing the code to be like this should do the work.

EXEC(‘SELECT * FROM tempTable WHERE F.Factory_id in (‘ + @factory_id +’ )’)

Hope this will help.

• 1 Comment

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

• 2 Comments

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result.
For example,

SELECT ‘abc’ + NULL == NULL.

When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string).
For example,

SELECT ‘abc’ + NULL == abc.

• 3 Comments

CREATE INDEX, why do we need to create index at SQL table? By create index in table will help to locate rows more quickly and efficiently. You can create UNIQUE, CLUSTERED and NONCLUSTERED index.

For example,

CREATE INDEX index_name ON table_name (column_name)

Above is the simple way to create an index into a table. By default, NONCLUSTERED INDEX is created. So what does UNIQUE, CLUSTERED and NONCLUSTERED index mean?

UNIQUE
Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique.

CLUSTERED
Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table.

NONCLUSTERED
Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order.

• 0 Comments

What is ISNUMERIC in Transact-SQL? It is used to determine that the value is numeric or not. It will return ’1′ when the value is numeric. Else it will return ’0′.

Example,

DECLARE @temp nvarchar(100);
SELECT @temp = ’100′;
SELECT ISNUMERIC(@temp);

The returned result is ’1′.

• 0 Comments