Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL WHile statement?

Posted on 2013-06-11
2
Medium Priority
?
219 Views
Last Modified: 2013-06-12
In the code below I can set the id and it runs fine

What I would like to do is feed in a comma seperated string and run the same code setting the @htid each time


DECLARE @htid INT
SET @htid = 71888


DECLARE @tsid INT
SET @tsid = (SELECT ID FROM dbo.TimeSheetFiles WHERE hoursTrackingID = @htid AND verified = 1)

--select
;with c as 
(
	select *, dup = ROW_NUMBER() over ( PARTITION by hoursTrackingID, timesheetID, hsid order by id  ) 
	FROM dbo.HoursTracking_DetailHours WHERE hoursTrackingID = @htid AND timeSheetID = @tsid
)

DELETE from c where dup > 1

Open in new window

0
Comment
Question by:lrbrister
2 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 total points
ID: 39237554
hi,

one way is by creating attached function and call your code like this

DECLARE @htid varchar(500)
SET @htid = '71888,452,5630,4102,344'


DECLARE @tsid INT
SET @tsid = (SELECT ID FROM dbo.TimeSheetFiles WHERE hoursTrackingID IN (SELECT items FROM dbo.FN_GENERATETABLE(@htid,',') AND verified = 1)

--select
;with c as 
(
	select *, dup = ROW_NUMBER() over ( PARTITION by hoursTrackingID, timesheetID, hsid order by id  ) 
	FROM dbo.HoursTracking_DetailHours WHERE hoursTrackingID IN (SELECT items FROM dbo.FN_GENERATETABLE(@htid,',') AND timeSheetID = @tsid
)

DELETE from c where dup > 1

Open in new window

SQL-ROWTOCOLUMN.sql
0
 

Author Closing Comment

by:lrbrister
ID: 39242340
Sorry for late getback.
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

927 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