Solved

Data conversion in proc

Posted on 2011-09-15
2
201 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 45
GRANT, REVOKE, DENY 4 38
Joining Two Tables In SQL and combining records 6 52
Managing Columnstore Indexes 2 18
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now