Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)

Apr 9, 2008



I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.

However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).

For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).

Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac


How can I accomplish this?

I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.

Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?

View 21 Replies


ADVERTISEMENT

How To Get LIST Of Fields In A Report (designer)

Jun 25, 2007

Hi All,



When I'm building a report in report designer and get error message that says --error like --field textbox25--whats the easiest way to see all my report fields and quickly access them instead of clicking every field in a report?



Ideally a fields map with links to these fields.



thanks



Sonny

View 1 Replies View Related

SQL Comma Seperated List

Jul 26, 2005

Is there a way to return a comma seperated list in a  query?For example if I have this simple querySelect member_name From members It returns all the members in diff rows (lets assume we have 3)So the result will bemember_name--------------name1name2name3Instead of this I need to have the result in one row with values seperated by commas.member_name---------------name1,name2,name3I hope I am clear enough. Any help would be appreciated. Thank you.

View 4 Replies View Related

How Do I Create An In-line Comma Seperated List

Jul 20, 2005

Scenario:Table 1 (a id, b name)Table 2 (a FKid, d value)A standard join on a gives me something like:a1 b1 d1a1 b1 d2What I want is:a1 b1 d1,d2I can easily do this with a function or cursor, but is is somewhatslow, and I need to do this a lot and I don't really want to have tomaintain tons of functions or cursors.Thoughts?

View 3 Replies View Related

How To Select Multiple Rows As Comma Separated List?

Jul 4, 2004

Hi,

I have a table of users, a table of categories, and a many-to-many table linking users to categories.

My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to.

Here is a stripped-down version of the table fields:

tbl_User
UserId, Email

tbl_Category
CatId, CatName

tbl_User_Category
UserId, CatId


I have tried using the coalesce function to build a string, but can only get this to work for one row at a time:

DECLARE @list nvarchar(100)

SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4))
FROM tbl_User_Category
WHERE UserId = @UserId

SELECT @list as List


Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle?????

Any help would be fantastic!

thanks,
Rob

View 4 Replies View Related

Insert Into Multiple Rows Instead Of One Comma-delimited List?

Jul 20, 2005

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

View 2 Replies View Related

Transact SQL :: Get Multiple Rows Based On Comma-separated Ntext List In On Column?

Jun 2, 2015

I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122).  I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.

This obviously works:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')

But this does not:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)

View 5 Replies View Related

Splitting A Comma Seperated Column Into Multiple Rows

Jan 18, 2006

Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie

View 1 Replies View Related

Query Result As Comma-separated List

Jul 23, 2005

Hi,I'n in an environment where I cannot make stored procedures. Now I needto make a query with a subquery in the SELECT part which gives a commaseparated list of results:SELECTp.id,listFunction(SELECT name FROM names WHERE name_parent=p.id) AS'nameList'FROM projects AS pThis query should return something like:1, "john,mike,petra"2, "bob,carl,sandra,peter,etclistFunction is (of course) not (yet) defined. Is this possible withoutthe use of stored procedures?Mike

View 12 Replies View Related

Inner Join On A Comma Delimited List?

Jul 12, 2006

Hi,

I have the following query:


SELECT *
FROM News INNER JOIN
Newsletters ON News.ID = Newsletters.fkNewsID


My problem is that fkNewsID can contain a comma delimited list of various IDs. Is there a way to properly do the join in this case?

View 1 Replies View Related

SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List

Jun 15, 2015

So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function

So the output would look like :

CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]

SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0

GROUP BY
preop.Case_ID

View 8 Replies View Related

Need Sql To Return The Result Of A Query As Comma Seperated Values.

Jun 28, 2005

Hi,   I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario...   Table Name - Customer   Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID    JoinDate1                   04/01/20052                   01/03/20033                   06/02/20044                   01/05/20025                   09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh

View 4 Replies View Related

T-SQL (SS2K8) :: Creating Comma Separated List Of Details From Multiple Columns?

Jun 3, 2010

I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.

This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.

I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?

Create table #tempa
(
customer_Full_Name varchar(100),
Customer_Email varchar(100),
Postcode varchar(100),
AlternateEmail varchar(max)NULL
)
insert into #tempa (customer_full_name,customer_email,postcode)

[code]....

View 9 Replies View Related

Debug Stored Procedure That Uses Comma Delimited List To Insert Multiple Records

Jan 18, 2006

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
 
 

View 4 Replies View Related

Field Contains Multiple Values Seperated By Comma

May 3, 2006

Hi All

I'm really new to SQL and would appreciate a solution to the following problem.

I have a table that contains 1 record. in that record there is a column called CODES which contains multiple values that are seperated by a comma.

C200,C201,C202,C204,C200,C203,C200,C202,C203,C205,C200,C202,C203,C204,C205,C206,C200,C201,C202

I need a solution to select all values from CODES that are distinct so I end up with the following.

C200,C201,C202,C203,C204,C205,C206

Any help would be really appreciated. Please bear in mind I'm a complet novice.

View 4 Replies View Related

Extracting Multiple Values As Comma Seperated

Jul 17, 2007

Hi all,



This is my table :

WorkstationNo UserID

101 a1

102 a2

103 a3

101 a2



and there are many other fields too. The output I need is something like this



WorsktationNo OccupiedBy

101 a1,a2

102 a2

103 a3



In the similar fashion I would also require to retrieve the values based on the UserID something like this

UserID Workstations

a1 101

a2 101,102

a3 103



Could someone tell me how to write the query for this.



View 9 Replies View Related

SELECT WHERE (any Value In Comma Delimited List) IN (comma Delimited List)

Jul 2, 2005

I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.

Here is an approach I am trying:

TABLE Events(EventID int, Categories varchar(200))

EventID Catetories
--------------------------
1           ‘6,8,9’
2           ‘2,3’

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.

Can someone suggest an approach?

Any examples would be greatly appreciated!
Gary

View 3 Replies View Related

Filtering Sharepoint List Data Using Report Parameters

Mar 27, 2008



We are using xml to pull data from a custom sharepoint list into sql 2005. We have set a parameter that allows the user to filter the data by surname, however when the user tries to filter the list the drop down box brings up a list of every record, so there are duplicate entries for each surname.

Is there a way of filtering this so that there is only one instance of the users surname instead of it showing all the records?

Any help would be much appreciated.

View 2 Replies View Related

DB Engine :: How To Solve Multiple Rows Result From Inner Join Query

Dec 3, 2015

I have 3 tables:
 
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,

[Code] ....

I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )

But am getting a multiple result if the order purchase has more than 1 item :

SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3

View 5 Replies View Related

Retrieve Fields List From A Published Report Data Model Published On The Reporting Server.

Jul 17, 2007

Can someone please tell me how to retrieve/query the list of fields from an entity of a report data model that has been published on the reporting server programmatically ?

I am trying to upload a report data model to the reporting server and planning to use that model as the data source and consume it through our existing web application?

Thank you ,

Rashid A. Khan

View 1 Replies View Related

Comma Delimited List Of Views

Aug 13, 2007

Is it possible to get a comma delimited list of the views in a DB?

View 7 Replies View Related

UDF To Split A Comma Separated List

Feb 26, 2008

Hello. I need to write a UDF that would split a comma separated list and return 4 values. I need to return the first 4 values and ignore the commas after that. If there are no commas in the string that's passed then just return the table with empty strings. The UDF should accept 2 inputs. The ntext and a position and return a value based on the position.For example: 1,2,3,textshould createPosition | Value-------------------------1|12|23|34|textand return a value based on the position.  If there are more than 3 commas for example1,2,3,This string, though short, contains a commashould createPosition | Value-------------------------1|12|23|34|This string, though short, contains a commaand return a value based on the position. And if there are are less than 3 commas in the string passedFor example: 1,2 or NULL or 2:3.5 or This is a string with no commasshould createPosition | Value
-------------------------
1| (empty string)
2| (empty string)
3| (empty string)
4| (empty string)and return a value based on the position.This is what I wrote so far. CREATE  function GetValueFromPosition  (@Input nvarchar(4000), @position int)Returns nvarchar(4000)AsBegin    -- Declare the return Variable    Declare @ReturnValue nvarchar(4000)        Select @ReturnValue = LTRIM(RTRIM(member_id)) From dbo.SplitString(@Input, ',') Where position = @position     Return @ReturnValueEnd CREATE Function SplitString(@text varchar(8000), @delimiter varchar(1) = ',')-- This function splits a string of CSV values and creates a table variable with the values.-- Returns the table variable that it createsRETURNS @Strings TABLE(    position int IDENTITY PRIMARY KEY,    member_id varchar(8000))ASBEGIN    Declare @index int        Set @index = -1             WHILE (LEN(@text) > 0)           BEGIN        SET @index = CHARINDEX(@delimiter , @text)         IF (@index = 0) AND (LEN(@text) > 0)                BEGIN                 INSERT INTO @Strings VALUES (@text)            BREAK           END             IF (@index > 1)                 BEGIN              INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))              SET @text = RIGHT(@text, (LEN(@text) - @index))            END            ELSE                SET @text = RIGHT(@text, (LEN(@text) - @index))           END        RETURNEND I am trying to modify these according to what I need but its not working. Please help. Thank you.   

View 1 Replies View Related

How Can I Get A Comma Delimited List Of The Views In My Db

Mar 18, 2008

How can I get a comma delimited list of the views in my db?
("view1","view2","view3",etc...)

View 3 Replies View Related

Creating A Comma Delimited List

Jul 11, 2005

Hi,

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

Products
Table
==============
ProductID
Title

Authors
Table
=============
AuthorID
Name

Product Authors
Table
=====================
AuthorID
ProductID

Is this at all
possible?

Thanks

jr.

View 3 Replies View Related

Matching Comma Separated List

Jun 7, 2008

I have the following SQL Query:

SELECT user_profiles.userFirstName, user_profiles.userInitial, user_profiles.userLastName, user_types.userTypeDesc, user_profiles.userOfficeIDs, user_profiles.userEmail
FROM user_profiles
INNER JOIN user_types ON user_types.userTypeID = user_types.userTypeID

The field userOfficeID contains a comma separated list of values such as "1,2" to identify that the user is in both the NJ and NY office.


Table office_locations
OfficeID officeState
1 NJ
2 NY
3 CT

I would like the output to be something similar to:

Name Office userOfficeIDs value
John Smith NJ 1
Mary Smith NJ/NY 1,2
Jim Smith NJ/CT 1,3
Mike Smith NY 2

Any direction would be appreciated.

Thanks

View 19 Replies View Related

Select List Split By Comma

Oct 29, 2014

i have a list of data as below:

Company Email
A AAA@abc.com
A BBB@abc.com
B xxx@def.com

i would like to write a query to list out as below where select the company A
AAA@abc.com, BBB@abc.com, ...

View 2 Replies View Related

How To Use Comma Separated Value List In The Where Clause?

Jun 28, 2006

How to use comma separated value list in the where clause?

I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list).

update tbl_Voters
set voted = true
where EmpID in @empIdsCsv

Where, @empIdsCsv = €™12,23,345,€™ (IDs of the employees)

Since the above is not possible I have done the following dynamic query:

-- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}€¦
set @empIdsCsv = 'EmpID=' + substring(@empIdsCsv , 0, len(@empIdsCsv )) -- Remove trailing comma
set @empIdsCsv = replace(@empIdsCsv , ',', ' or EmpID=')

declare @markVoters varchar(8000)
set @markVoters = '
update tbl_Voters
set voted = true
where €™ + @empIdsCsv

--Execute the dinamic query
exec (@markVoters)

The above code generates the following dynamic query:
update tbl_Voters
set voted = true
where
EmpID= 12 or EmpID=23 or EmpID=345

The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars).

Can someone suggest a better solution with the ability to use comma seperated values in the where clause?

View 3 Replies View Related

Help With Subquery: Comma-delimited Id List To Value

Aug 17, 2007

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.

Thank you,
Tyler

View 4 Replies View Related

Comma Delimited List Of IDs To A Recordset From 2 Tables...

Jan 20, 2006

Have 2 tables in SQL Server 05 DB:

First one is MyList

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).

Thank you!

View 5 Replies View Related

Comma Delimited Values List In Where Command

Jan 14, 2014

Trying to run a simple query below

Update CustomerID
set CustomerLevelID='5'
where CustomerID='97000,57700,560046,462334,454453,522444,539743'

When I try this I get

Conversion failed when converting the varchar value

Is it possible to use comma delimited values in a where command?

View 3 Replies View Related

Transact SQL :: Replace With A Comma Separated List

Aug 5, 2015

I have a replace statement like the following:

select  (replace('FMG','FMG','FM'))

So this is straightforward as it will replace the word 'FMG' with 'FM'

However, SSRS is passing a comma separated list like this:

select  (replace('FMG','AFM','FMG','FM'))

And what I need to do is replace the comma separated list of 'FMG','AFM' only.  I tried this:

select  (replace('''FMG','AFM''','FMG','FM'))

But it still complaining about syntax errors. How do I get the comma separated list to be seen by the replace function?

View 7 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Pass Comma Delimited List To Stored Procedure

May 2, 2008

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:

Code:


employeeList = 'name1','name2',name3','name4','name5'
SQL="sp_REVIEW @ACTION='lde', @CURRENT_USER='" & currentUser & "', " &_
"@EMPLOYEE_LIST='" & employeeList & "'"


Here is the stored procedure

Code:


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

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved