Solved

Data conversion in proc

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 45
Write a function 5 26
Restrict result set 1 0
SQL Query 3 0
     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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

11 Experts available now in Live!

Get 1:1 Help Now