Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Data conversion in proc

Posted on 2011-09-15
2
Medium Priority
?
209 Views
Last Modified: 2012-05-12
I have the proc below.  However it fails when I call it like this:

EXEC      [dbo].[GetTickets]
            @ticket_type = '2, 1',
            @userid = 1


I understand why, because FK_type is numeric but @ticket_type is varchar.  

How do I handle this in the proc?  How do I convert the pass string of '2, 1' to the proper format to placed in the IN() keyword of the WHERE clause?


create       procedure [dbo].[GetTickets]
@ticket_type varchar(20),
@userid integer

as

SELECT * FROM tds_ticket WHERE FK_type IN(@ticket_type) AND FK_review_user = @userid

return
GO
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
akku101 earned 2000 total points
ID: 36544504
Hear is the solution, you can also use join instead of select in if you are extracting data from multiple tables


Create Procedure [dbo].[GetTickets]
      @ticket_type varchar(20),
      @userid integer
As

SELECT @ticket_type = @ticket_type + ','  
   
CREATE TABLE  #TicketType (ticket_type int)  

WHILE (CHARINDEX(',',@ticket_type) >0)
BEGIN
      INSERT INTO #TicketType (ticket_type)
      SELECT SUBSTRING(@ticket_type,1,CHARINDEX(',',@ticket_type)-1)
      SELECT @ticket_type = STUFF(@ticket_type,1,CHARINDEX(',',@ticket_type),'')
END

SELECT * FROM tds_ticket WHERE FK_type IN(select ticke_type from #TicketType ) AND FK_review_user = @userid


Drop Table #TicketType
return
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 36544749
Great!  Thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question