Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
safzal1212
Starting Member
11 Posts |
 Posted - 2010-05-13 : 13:04:47
|
| I have a page with list box and user can select multiple values from that list box. Now what I want to do is when user submits the page I want to generate a comma separated string that contains the ID's of values selected and pass this string to stored procedure. String would look like something like that '2,5,9'Now my question is how can I split these ids in my stored procedure and get the records based on these ID's. In want to pass this array to Source column in the following Stored procedure and get the data. Right now following stored procedure is working on the basis of single source id passed to it. @intCategoryID AS INT, @intStateID AS INT, @intNoOfItems AS INT, @intSpecialityID AS INT, @decRevisionNum AS VARCHAR(4), @KeyWord AS VARCHAR(200), @TimeFrame AS int, @Source AS int SELECT DISTINCT A.ContentID, A.DocumentNo, A.RevisionNo, A.ContentTypeID, A.ContentTitle, A.ApprovedDate, A.ExpirationDate, tblContentCategories.CategoryId, A.PostedDate, A.Source FROM tblContents AS A WITH (NOLOCK) INNER JOIN tblContentCategories WITH (NOLOCK) ON A.ContentID = tblContentCategories.ContentID INNER JOIN tblContentRegions WITH (NOLOCK) ON A.ContentID=tblContentRegions.ContentID WHERE (A.ContentTypeID = 2) AND (@KeyWord IS NULL OR (ContentTitle LIKE ('%' + @KeyWord + '%') OR PATINDEX('%'+ @KeyWord +'%', Content)>0)) AND (A.Source=(CASE WHEN @SOURCE=0 THEN A.Source ELSE @Source END)) AND (PostedDate>= DateAdd(dd,-@TimeFrame,GetDate())) AND tblContentCategories.CategoryId=@intCategoryID AND (tblContentRegions.StateID=@intStateID) AND (tblContentRegions.CategoryId=@intCategoryID) AND A.RevisionNo = (SELECT MAX(B.RevisionNo) FROM tblContents AS B WITH (NOLOCK) WHERE A.DocumentNo = B.DocumentNo AND B.StatusID IN (3,4)) AND A.StatusID IN (3,4) AND (CONVERT(VARCHAR(10),ExpirationDate,111) >= CONVERT(VARCHAR(10),GETDATE(),111)) ORDER BY ExpirationDate DESC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-13 : 13:13:31
|
| http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Pass an array to Stored procedure