Skip to main content
B
balumohan
Inspiring
March 19, 2009
Question

Smallint Issue

  • March 19, 2009
  • 2 replies
  • 905 views
I have a list useridlist with values (1,2,3,4,5). This is a list of userids in the user table. This userid is of type smallint.
I am passing the list 'useridlist' with varchar as type to a SP. In sp consider I have written like

select * from [user] where userid in (@useridlist)

The @useridlist is declared in SP as varchar. The query will come up to error as it is expecting of type smallint. So what can we do over in such a situation. Please Help!! Thank You!!
This topic has been closed for replies.

2 replies

-
-__cfSearching__-
Inspiring
March 19, 2009
> So what can we do over in such a situation.

Data type issues aside, the database is assuming the @variable represents a single value. If you want to use it to represent multiple values the options for _stored procedures_ are:

A) Use dynamic sql. But that has it is disadvantages. One of which is sql injection risks -OR-
B) Create a function that splits the values and inserts them into a temp table. Then join to the temp table:

SELECT u.ColumnA, u.ColumnB
FROM User u INNER JOIN @TheTempTable tmp ON u.UserID = tmp.TempValue
....

B
balumohanAuthor
Inspiring
March 20, 2009
Thank you very much -==cfSearching==-
J
JR__Bob__Dobbs-qSBHQ2
Inspiring
March 19, 2009
You cannot pass a string to a stored procedure and expect it to interpret this a list, at least not with the databases I am familiar with. It will treat your input as: select * from [user] where userid in ( '1,2,3,4,5' ).

The CFQUERYPARAM tag has a list attribute you could use to pass a delimited list to a query, not a stored procedure or user defined function. See attached sample.

CFQUERYPARAM:
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices