• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Data conversion in proc

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
HLRosenberger
Asked:
HLRosenberger
1 Solution
 
akku101Commented:
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
 
HLRosenbergerAuthor Commented:
Great!  Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now