Retrieve Multiple Roleid's By Passing Csv Strings
Jun 24, 2008
Dear All
I have a table Roles with columns(RoleId,Rolename)
I have another table userrole with columns(userid,roleid)
I need to retrieve the roleids when rolenames(multiple strings in csv format) are passed.
The stored procedure is as follows
create procedure GetRoleIds(@Rolenames varchar(200),@RoleIds int output) as
begin
end
declare @roleidlist int
Exec GetRoleIds 'Admin,Manager,Developer',@roleidlist output
print @roleidlist
The output must be 1,2,3.
Roles table data
Roleid rolename
1 Admin
2 Manager
3 Developer
How to do this.Please help
Thanks
srinivas
View 20 Replies
ADVERTISEMENT
May 8, 2008
I have a Save method in my class file that connects to my sql db to 4 datasets, which I then wrap together. I am currently instantiating 4 different Sql Client connections to do this.
Would it be more efficient to use just one?
View 2 Replies
View Related
Nov 24, 2013
I need retrieve only strings that hold the sub-string in middle. no first no last just middle.
See: the sub-string is "test"
Yes: test test test
Yes: take test test
Yes: test test take
Yes: take test take
No: test test
No: test take take
No: take take test
No: test
I prepare sample data...
declare @sample table(s varchar(50));
insert @sample values
('test test test'),
('test test'),
('test'),
('test take test'),
('test take take'),
('take test test'),
('take test take');
View 4 Replies
View Related
Jun 10, 2007
Well, I managed to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@IDList varchar(500),
@ReorderLevel int,
@ProductName nvarchar(30)
)
AS
BEGIN
SET NOCOUNT ON
EXEC('Update dbo.Products
SET ReorderLevel = (' + @ReorderLevel + ') ,ProductName = (''' + @ProductName + ''')
WHERE ProductID IN (' + @IDList + ')')
END
----------------------
THis works fine inside Sql Server 2005 Query analyser.
But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.
Any help would be appreciated
And can someone convert it to support XML instead of list of strings. thanks.
View 3 Replies
View Related
Apr 4, 2006
I've got a GridView, a datasource, 2 textboxes and a button. If I choose to bind the GridView to the datasource the correct data is shown. But if I modify the datasource and chooses the TextBoxes as Parameter Controls it doesn't work out as I expected.
Can I please have a step-by-step response which will work? And expecially if one if the TextBoxes eguals nothing/null. Because this is very important, too, and that part doesn't work either.
View 1 Replies
View Related
Jul 20, 2005
I have a need to concatenate all Descriptions from a select statementSELECTt_ReviewSection.PeerRevSectionDescriptionFROMt_ReviewSectionWHEREt_ReviewSection.PeerRevID = @lngRevIDORDER BYt_ReviewSection.PeerRevSectionOrderI want to return a single string "section1, section2, section3, section4"based on the multiple rows returned.Any ideas
View 5 Replies
View Related
Jul 20, 2005
I am a little stomped and wandering if someone might have an idea howto go about doing this.following on from this guidehttp://www.4guysfromrolla.com/webtech/031004-1.shtml on matching acomma-delimited string, I would like to expand on this and match twocomma-delimited string in a sproc.In my database, table A have a city field containing a comma delimitedstring ie 'sydney, new york, chicago'. I am passing a similarcomma-delimited string to a sproc and returning matcheing id.so, we have table A:id/city1/sydney, new york, chicago2/new york, san antonio3/beijing, sydney4/london,beijingpassing string 'sydney, new york'need to return: id 1,2,3 (1,2 match new york and 1,3 matching sydney)any ideas?
View 1 Replies
View Related
May 8, 2008
Hey guys,
I am pretty new to reporting software and I was just wondering in reporting services 2005 when working with multiple datasets and data connection string in order to populate text boxes if the data is a number it is prefixed with SUM at the start of the expression and if it is text it is prefixed with FIRST even though it is just for one value.
I was wondering if this is normal or have I messed up somewhere?
Many thanks.
View 1 Replies
View Related
Jul 9, 2007
I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?
Thanks,
Ridium
View 12 Replies
View Related
Jul 10, 2006
Hi There,
Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?
for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?
Is Derived column transformation the best way to do it?
Thanks.
Sam.
View 6 Replies
View Related
Jan 15, 2008
Hi everyone - I'm getting myself into a right muddle and am looking for advice.
How do people deal with connection string matters when taking a dataset defined in one assembly (and by default using the connection strings defined within that assembly in the settings files) and then using that assembly in an app (which also has a requirement to see the same physical database).
I'm not sure I've explained that terribly well but what I'm trying to avoid is duplicate copies of my database which so far seems to be the only way that I've managed to make stuff work. I'm not very experienced with SSCE data access (I'm a serices/sockets/ip person) and this may just be ignorance. Most of the examples seem to assume that the data is in the same place as the app whereas I'm trying to collate a whole series of functions into a helper assembly that I'd like to re-use for other things.
Ideally the dataset designer would provide an easy way of choosing from centralised connection strings - perhaps this is what the Dataset Project implies - but again the docs are mostly focused on SQL Server? Otherwise the best I've been able to do is make the connection properties public and try to update them that way or use a post-build action to copy the database from my datalayer project directory to that of my application |DataDirectory|
I suppose the question might be if you have a dataset containing multiple tableadapters that assumes one connection string, is there any easy way to keep such strings co-ordinated between projects without hardcoding them? With a server resource, the same non-specific connection string resolves to the same server (if that make sense) and this seems to be were I can't make the logical shift.
Does anyone have any thoughts and can they please point this SSCE noob in the right direction?
Cheers
View 2 Replies
View Related
Jun 30, 2007
i am able to store multiple email ids in table (separated by comma).
now the problem is how do i retrieve those email ids from the table so that i can send mails to those email ids??
thanks
Jaimin
View 2 Replies
View Related
Feb 27, 2008
Hi, i m using vwd 2005 express and sql express. i have created following tables table_office,table_customer,table_order in my sql express. i also added one more table named table_final. In my webform i have 2 textbox and submit button plus(controls to take values for table_office,table_customer,table_order).so when user fills the form and clicks the button the data gets inserted into thier respected tables.similarly table_final gets populated with values in this way (id,name,address,table_office_id,table_customer_id,table_order_id) Now when displaying the output in the gridview. i combined values of all these tables together. its fine till here. Now what i want is i want to write a query to retrieve these values back into the webform . Here i want the values from all the tables back into the webform controls on a click of a button.so that i can modify it manually, make updation on it and finally show it into gridview. i hope i m able to make u understand. anyway can somebody help me with simple code(C#) and sql query to achieve this task.? thanks. jack.
View 8 Replies
View Related
Jun 22, 2004
Hi all,
I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:
ie:
SELECT @Hits = COUNT(DISTINCT StatID) FROM Stats
...etc...
The SP is currently called as below:
SqlParameter[] sqlParams = new SqlParameter[]
{
new SqlParameter("@FromDate", Request["FromDate"]),
new SqlParameter("@ToDate", Request["ToDate"]),
};
My question is this: How do I retrieve these variables?
I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.
Thanks in advance,
Pete
View 5 Replies
View Related
Oct 21, 2015
How can we get the result set as TESTING1,TESTING2 from below
DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $
View 6 Replies
View Related
May 7, 2008
I am doing report development against OLAP (Cube). I have a parameter which is a multi-value parameter, and I need to pass this as a parameter into a sub-report (pass all the values selected in this multi-value from the main report to sub report parameter).
Is this even possible? If yes how do I achieve this.
Currenty I sent like Fields!Region.Value(0), but I want to send actually Fields!Region.Value() (meaning all selected).
Any help ASAP is appreciated.
View 3 Replies
View Related
Mar 9, 2014
How do I pass the multiple value in the single parameter? Here is the select simple select statement with two fields (ID and Map_Area):
SELECT *
FROM TestParamOne
Here is the output:
ID Map_Are
12345KK45
657463IIY7
34345FGD
44342DFRE
4646DSAW
424245DSAW
12121DSAW
5753FRDE
575737FRDE
1121FRDE
1121F5FR
646462F5FR
8568F5FR
Here is my simple stored proc. Right know I only can execute with one value at the time but I am trying to run the stored proc with more than one value.
CREATE PROCEDURE TestParam1
@Map varchar(4)
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Map_Area
FROM TestParamOne
WHERE Map_Area=@Map
END
GO
View 7 Replies
View Related
Apr 20, 2007
Hi,
I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.
View 1 Replies
View Related
Feb 19, 2007
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
View 2 Replies
View Related
Sep 30, 2015
I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.
I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,
Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.
Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.
SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model
[Code] ....
View 3 Replies
View Related
Dec 28, 2007
I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?
View 4 Replies
View Related
Oct 9, 2007
Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan
View 2 Replies
View Related
Dec 9, 2007
hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e)
{
try
{foreach (ListItem item in authorsListBox4.Items)
{if (item.Selected)
{
AddConfSqlDataSource.Insert();
}
}saveStatusLabel.Text = "Save Successfull: The above publication has been saved";
}catch (Exception ex)
{saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message;
}
}
View 3 Replies
View Related
Jan 11, 2005
Hi,
I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.
Any suggestions?
Thanks
My plan is to have the same ability as under the 'Optional' section of this page:
http://search1.workopolis.com/jobshome/db/work.search_cri
View 1 Replies
View Related
Apr 20, 2015
I am getting error when I passed multiple rows in less than condition:
create table #t1
( ID int)
INSERT INTO #t1
SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8
CREATE TABLE #t2
(ID int)
INSERT INTO #t2
SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4
SELECT ID FROM #t2
WHERE ID < (SELECT ID FROM #t1)
Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How to pass multiple values in this condition?
View 2 Replies
View Related
Apr 6, 2007
Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.
Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)
paramList.Add(New Microsoft.Reporting.WebForms.ReportParameter("BranchNumber", 1))
ReportViewer1.ServerReport.SetParameters(paramList)
pInfo = ReportViewer1.ServerReport.GetParameters()
Let me know if you have any suggestions!
Thanks.
View 4 Replies
View Related
Dec 20, 2006
Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?
For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:
MyChildPackage(var1=1, var2="foo");
MyChildPackage(var1=2, var2="bar");
MyChildPackage(var1=3, var2="baz");
View 2 Replies
View Related
Oct 16, 2007
Hello,
I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.
Thanks a lot for any feedback!
View 6 Replies
View Related
Aug 10, 2014
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
[code].....
View 1 Replies
View Related
Aug 13, 2015
we are using SSRS 2012.Oracle 9i as back end database. Select A,B,C from view where A in (Param1) in above query when I am passing 1 value getting output in 30 sec. when I passed two values getting time out error in SSRS. how to pass multiple values in Oracle 9i in SSRS report.
View 4 Replies
View Related
Jul 21, 2015
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Now I have to pass multiple param values to it. I was trying this but didnt get any success
exec
[SP_test]'LOC','LOP'
View 10 Replies
View Related
May 21, 2014
Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?
View 1 Replies
View Related
