|
You can if you're using multiple Active Result Sets[^] on an SQLServer, and on Oracle you can anyway[^].
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
One of my Stored Procedures was using ۀ in its name and when i am getting script it was giving error!
|
|
|
|
|
I wrote a Stored Procedure that retrieves parameters from my dropdown list and a txtbox that I use for my where like % clause
I got everything to work except for the 'Where like clause' It looks like it should work but obviously it doesn't. Any help would be appreciated.
My Code:
ALTER PROCEDURE [dbo].[SearchEmpRecords_Sp2]
@SearchBy varchar(50) = '',
@SearchVal varchar(50) = ''
AS
BEGIN
DECLARE <a href="/Members/value">@Value</a> varchar(50)
SET @SearchBy = @SearchBy + ','
IF REPLACE (@SearchBy, ',', 'ALL') <> ''
BEGIN
SET <a href="/Members/value">@Value</a> = (@SearchBy)
IF <a href="/Members/value">@Value</a> <> ''
BEGIN
SELECT EmployeeID,Username, State, AreaNumber FROM Employees
WHERE
*** The line below is what I'm trying to get to work.
*** Whenever I ran this line I get my custom error saying:
Records Not Found
-- <a href="/Members/value">@Value</a> LIKE '%' + @SearchVal + '%' ----
*** These lines work but I would like to replace them with the line above ***
Username LIKE '%' + @SearchVal + '%'
OR State LIKE '%' + @SearchVal + '%'
OR AreaNumber LIKE '%' + @SearchVal + '%'
END
END
END
|
|
|
|
|
Should we assume SQL Server?
That sort of thing requires a string variable and EXECUTE :
DECLARE @sql VARCHAR(MAX)
SET @sql = blah blah blah
EXECUTE ( @sql )
(Don't leave off the parentheses.)
|
|
|
|
|
Thanks for your help but, I don't know what to put in blah blah blah. I'm very new at this. The only thing in my code that doesn't work is the 'Where Like clause' If you could give me more details I would appreciate it. Thanks
|
|
|
|
|
Commish13 wrote: but, I don't know what to put in blah blah blah.
Construct your statement, manually on a piece of paper, as it would look like if your proc was called and it ran. So for example
select column1 from mytable where name like '%acme%'
Then in the above in your stored proc you want to create that as a string. So you must concatenate the values together. (Following is an EXAMPLE and not what you need specifically.)
set @var = 'select column1 from mytable where name like ''%' +@likeValue + '%''';
The multiple ticks in the above is intentional as it tells the database that you actually want a tick in your string.
|
|
|
|
|
Ok. I'm trying to replace the Username or State or AreaNumber with 1 of those values when selected from web form. I got the ''%' +likeValue + '%''' part to work. But I would like to do the same thing for the three options via: Username,State or AreaNumber. In my code above I'm using @Value because I think it's holding the value when selected from web form. But when I run my program I get my custom message that 'No records Found' Please refer to my example code above for reference.
Thanks for your help
|
|
|
|
|
I don't understand your comment.
If you have 3 options for the where clause then you must construct three concatenation expressions.
If you are doing that and get no results then the data that you passed doesn't match anything.
|
|
|
|
|
Create a variable to place your whole statement in - when I say whole I mean whole as in everything that is going to be executed.
Stick a print statement in there to show the contents of the variable just before the query is run.
Run the SP and look at what is returned in the print statement.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Commish13 wrote: SET @SearchBy = @SearchBy + ','
IF REPLACE (@SearchBy, ',', 'ALL') <> ''
what is the purpose of above statement and a condition?
|
|
|
|
|
For What the constraints Folder present above triggers folder in every table in sql server??
What kind of data will it store??
|
|
|
|
|
Do you know anything about SQL server? Those things are not folders and they do not store anything!
Please read a basic introduction to databases!
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
I told you, Didn't I?[^]
You should start learning before asking questions like these. Save your time, our time too.
Here more than couple of ways to learn anything in Internet. Education Needed[^]
|
|
|
|
|
Why we are using triggers in sql server?? when we want to use triggers??
thanks,
vignesh
|
|
|
|
|
|
Is it possible create/configure MySQL for functionality like SQL Server's Linked Server?
I'm using MySQl v5.5 and I need to link postgreSQL Database to access some data.
If yes, would you please tell me how?
|
|
|
|
|
Arun Sylvester J wrote: If yes, would you please tell me how?
Following is what I used in google. It would suggest the specific answer is no.
mysql stored procedure access remote database
|
|
|
|
|
I was taught to model one-to-many relations using an intermediate 'link' table to maintain 1NF.
However I'm increasingly seeing one-to-many relationships modelled simply by concatenating the 'many' into a single string and storing that directly in the table.
Consider a table of TASKS, and a table of CATEGORYs, where each task may have multiple categories.
I was taught to model the relationships between the tasks and categories using a third table, eg TASK_CATEGORIES, which comprised a one-to-one mapping.
TASK Table
----------
Task_ID Name Allocated_To etc
1 Fix XYZ Bug Dan ...
2 Add ABC Feature Dan ...
CATEGORY Table
--------------
Cat_ID Name Description etc
1 Bug This is a bug ...
2 Feature This is a feature ...
3 Work This is a work item ...
TASK_CATEGORY Table
-------------------
Task_Cat_ID Task_ID Cat_ID
1 1 1
2 1 3
3 2 2
4 2 3
ie. The task: (Task_ID == 1) has categories: (Bug, Work), etc
However now I'm seeing:
TASK Table
----------
Task_ID Name Categories Allocated_To etc
1 Fix XYZ Bug 1;3 ...
2 Add ABC Feature 2;3 ...
CATEGORY Table (as before)
Clearly (IMO) the first design is more correct, but the second is often easier to work with.
The question for you is: What are the downsides of design #2, and have you seen it in practice?
|
|
|
|
|
Well for a start your foreign key potential is out the window, it makes your use of category basically useless from a data structure POV.
I have seen it once, a project built by Reuters, one of the worst performing applications ever inflicted on us. The only benefit was to make the structure so obscure it was unusable by anyone without the ER diagram.
[edit] I now remember one of the new devs proposing that a while back, offered to terminate him on the spot unless he conformed to a correct data structure [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
1 is preferable in nearly all cases. 2 is only appropriate for reporting, but remember that whoever gets the report may simply turn around and try to import it.
If you have only a few categories, and they are unlikely to change, then you can make a bitmap:
Cat
1 Bug
2 Feature
4 Work
Bug + Work = 5
|
|
|
|
|
#2 is obvious violation of 1nf. Whenever there is a need to add or remove a category in #2, we will have to read and write the whole table. That is a serious design flaw.
In #1, we can use task id and categorg id as composite primary key for the intermediate table.
|
|
|
|
|
Peter Leow wrote: we will have to read and write the whole table
Excellent point.
|
|
|
|
|
hi friends actually i am working on a mlm project in which members are added in a tree pattern, and get the payment accordingly.
My table structure is as follow:
Id ParentId IsLeft IsRight
1 Null Null Null
2 1 1 Null
3 1 Null 1
4 2 1 Null
5 2 Null 1
6 3 1 Null
7 3 Null 1
8 4 1 Null
9 4 Null 1
10 5 1 Null
the problem is that initially 1500$ are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500$ for each pair.
My problem is to find the query which can return the total income of any given node.
enter image description here
According to figure node 1 must get 2500$ (1500+500+500) first 500$ is for node 4 and second 500$ is for node 3.
According to figure node 2 must get 1500$ because it has two nodes to its left and one node to its right this means a ratio of (2:1). and has no pairs
According to figure node 3 must get 0$ because it does not have any nodes in ratio(2:1)
one thing has to be kept in mind that 1500$ will be the first payment and then only the other pairs will be counted, and 1500$ will be given when node has ratio 2:1(two nodes on left and one on right) but no money when ratio is 1:2(one node on left and two on right)
I have found the query which will count all the pairs below a particular node and give receiving amount according to 500$, but the query has not been able to consider the first condition that is the 2:1 condition
declare @ParentId as int
set @ParentId=1
create table #temp_table_name
(
ParentId varchar(30) null,
)
;with Child as
(
select id,ParentId from tblTestingTree where id=@ParentId
union all
Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree
inner join Child
on tblTestingTree.ParentId=Child.Id
)
insert into #temp_table_name
select c.ParentId from tblTestingTree T join Child c
on c.Id=t.Id
WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
group by c.ParentId
having COUNT(c.ParentId)>1
select COUNT(*)*500 as totalmoney from #temp_table_name
drop table #temp_table_name
|
|
|
|
|
This will give you the ration values you are after, then it is just a matter of calculation
SELECT ParentID, SUM(ISNULL(IsLeft,0)) L,SUM(ISNULL(IsRight,0)) R
from TableName
Group By ParentID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear RAH where we place this code in my function so i can get my solution which provided by you
Quote: SELECT ParentID, SUM(ISNULL(IsLeft,0)) L,SUM(ISNULL(IsRight,0)) R
from TableName
Group By ParentID
please suggest us
thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
