|
| | Submitted on: 11/29/2003 1:57:39 AM
By: Thivya Prabakaran
Level: Intermediate User Rating: Unrated Compatibility:SQL Server 2000
Users have accessed this article 1367 times. | |
| | Often we have to group certain values by providing their id's. If the id's are seperated by comma's, this function gives you the simple methodology rather than parsing one by one. | | | Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. | /*
*****************************************************************************
Creator Name : Thivya
Date : Nov-29-2003
Description : Often we have to group certain values by providing their id's. If the id's are seperated by
comma's, this function gives you the simple methodology.
Purpose : To Get the student names concatenated by some delimiter
Input Details : student id's ,output delimiter
Assumption: Input Delimiter is assumed as ","
***************************************************************************
*/
CREATE Procedure GroupStudentNames
@Student_List Varchar(2000),
@Out_Delimiter Varchar(10),
as
Begin
Set NoCount On
Declare @Name_List Varchar(6000)
Declare @SQLString NVarchar(4000)
SET @SQLString = N'Select @Name_OUT = Coalesce(@Name_OUT+@Delimiter,'') +
StudentName From Student
Where
Stud_Id in (' + @Student_List +')'
EXECUTE sp_executesql
@SQLString,
N'@Delimiter Varchar(10),@Name_OUT Varchar(6000) OUTPUT',
@Delimiter = @Out_Delimiter,@Name_OUT = @Name_List OUTPUT
-- The same query can be done by using EXEC
-- sp_executeSQL has advantage than using EXEC
-- In sp_executeSQL, SQL server likely to resuse the Execution Plan generated
Select @Name_OUT As 'StudentName' -- which gives student names seperated by the given delimiter
End
| | Other 2 submission(s) by this author
| | | Report Bad Submission | | | Your Vote! |
See Voting Log | | Other User Comments | There are no comments on this submission. | | Add Your Feedback! | Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here. | | |