Solved

Data conversion in proc

Posted on 2011-09-15
2
203 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
2 Comments
 
LVL 2

Accepted Solution

by:
akku101 earned 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

786 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