PROCEDURE ListFilteredEvents @FilterList varchar(200) -- contains ‘3,5’ AS SELECT EventID FROM Events WHERE (any value in Categories) IN @FilterList
Result:
EventID ---------- 2
How can I select all records where any value in the Categories column matches a value in @FilterList. In this example, record 2 would be selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when selecting records where a column value is in the parameter list, but I can’t see how to make this work when the column itself also contains a comma delimited list.
I have a complex query where each row in the final dataset is a product. However each product has a number of authors associated with it. What I would like to do is have a query/subroutine join the authors to the product, as a string:
ProductID Title Authors 1 The Sacred and the Profane John Rieggle, George Alexi 2 Dancing in the Dark Dan Brown, Peter Kay, Paul Dwebinski
Hello any MS SQL experts out there! please help if you can. i'm trying to run a subquery within a query to keep myself from having to loop over the original query on display and then run additional queries to get the further info. here's the setup. i have two tables:
persons table column: name (varchar) column: vehicleids (varchar)
vehicles table column: id (int pk) column: vehiclename (varchar)
- The persons table is a list of peoples' names and what kind of vehicle/s they own. - The persons.vehicleids field is a comma-delimited list of one or more integers which correspond to the vehicles.id field. - The vehicles table contains a list of vehicles like car, bicycle, motorcycle, etc, distinguished by the vehicles.id field.
The result i want returned by the query is:
NAME - VEHICLES Joe Somebody - car,bicycle Sheila Johnson - van,pogostick,motorcycle John Nobody - skateboard,car
The query i'm trying to run to get this result is:
Code Snippet
SELECT pe.name, ( SELECT ve.vehiclename FROM vehicles ve WHERE CAST(ve.id AS VARCHAR) IN (pe.vehicleids) ) AS vehicles FROM persons pe ORDER BY pe.name
It returns the persons names in the first column, but only returns vehicle names in the second column when there's a single id in the persons.vehicleids field. if there's more than one integer in the field, it returns an empty string.
Can anyone tell me what I'm doing wrong? I do have the option of table restructuring if its necessary, but I'm not looking for a stored procedure solution or a temp table solution. Any takers? I would be in the kharmic debt of anyone providing a workable avenue.
I have a table that has a list of skills, ie "HP One", "HP Two"
I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below.
DECLARE @SkillSet NVarChar (200) SET @SkillSet = '-HP One-,-HP Two-' SET @SkillSet = Replace(@SkillSet, '-', '''') SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet)
The following sp does not return any results, but when i set the last line to ..
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two')
It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two'
Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is.
user_id -> unique value list -> comma-delimited list of user_ids notes -> random varchar data
Second one is MyProfile
user_id -> unique value name address email phone
I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @user_id for MyList then return all this data.
The format of the comma-delimited data is as such (all values are 10-digit alphanumerics):
d25ef46bp3,s46ji25tn9,p53fy76nc9
The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned).
I am trying to pass a comma delimited llist of names to a stored procedure. I am getting a syntax error and I can't seem to figure out why. When i plug the names by hand into my select statement in query analyzer it all works fine. Just a little background so i don't have to put all the code in... the list of name is built dynamically. Below are all the code snippets. Thanks for your help in advance. The is the list of names and the call to the stored procedure:
SELECT ww.ORACLE_USER_NAME, ww.LAST_NAME + ', ' + ww.FIRST_NAME as employeeName, ww1.DIVISION + ' - ' + ww1.COST_CENTER + ' - ' + ww1.COST_CENTER_DESC as department FROM WHOS_WHO.dbo.HR_PEOPLE_V ww LEFT JOIN WHOS_WHO.dbo.HR_DEPARTMENTS_V ww1 ON ww.DEPARTMENT_ID = ww1.ORG_ID WHERE ww.SEARCHABLE = 1 AND ww.ORACLE_USER_NAME IS NOT NULL AND ww.PERSON_TYPE = 'Employee' AND (ww.ORACLE_USER_NAME IN (@EMPLOYEE_LIST) OR ww.DEPARTMENT_ID IN (SELECT ud.department_id FROM USER_DEPARTMENT ud WHERE ud.nt_id = @CURRENT_USER)) ORDER BY ww.LAST_NAME, ww.FIRST_NAME
I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list. Example: name1 = value1, value2, value3, etc. name2 = value1, value2, value3, etc. name3 = value1, value2, value3, etc.
The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.
Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
I have list (in an input file) where each row is about 20K in size (so it can't be stored in a sql table). I want to convert the list into a table as shown below:
I was planning to have SSIS pull in the "before" data, run a custom C# program in SSIS against it to massage the data to vertical (3-column format), then export the massaged data to a new text file. The new text file would later be imported into a sql table using SSIS.
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc: exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8' Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs) CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150) ASBEGIN DECLARE @FileID INT SET NOCOUNT ON /*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate) Select @FileID=@@Identity /*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000) SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
ID Description ParentID Level B.01 Parent 1 H B.01.01 Parent 1.1 B.01 H B.01.01.01 Parent 1.1.1 B.01.01 H B.01.01.01.01 Detail 1 B.01.01.01 D B.01.01.01.02 Detail 2 B.01.01.01 D
[Code] .....
That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null @Decision varchar(1) Null Select emplid,name, Semester Decision1=(select * from tbldecision where reader=1) Decision2=(select * from tbldecision where reader=2) Where Semester=@Sem And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
Ok, I want to write a stored procedure / query that says the following: Code: If any of the items in list 'A' also appear in list 'B' --return false If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF (SELECT values FROM tableA) IN(SELECT values FROM tableB) Return False ELSE Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:
SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'
Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.
insert into #sample values ('customerid','15339119') insert into #sample values ('Title','15339119') insert into #sample values ('firstname','15339119') insert into #sample values ('prevcr','2146822710') insert into #sample values ('currcr','2146822710') insert into #sample values ('brandcode','2146822710')
I've got a query that returns the data I need. I want to put the query in a stored procedure such that, when the SP runs I get a pipe delimited text file on disk. I don't really want to mess with SSIS, etc. Is there a Q&D way to do this?
I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it
declare @start int, @testEmail nvarchar(2000)
set @start = 1 set @testEmail = NULL while @start <= (Select count(PADM_Email) from PADM_Emails) Begin --Print @start set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';' set @start = @start + 1 End
I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this: