|
Hi ,
How to use a variable which consists of the sql statement to be used for my cursor . At present the following code gives error
create PROCEDURE spGetAssetRecords
@sSQL varchar(8000)
As
DECLARE c2 CURSOR READ_ONLY
FOR
@sSQL
regards
Vijay
|
|
|
|
|
got it
set @sSQL='declare c2 cursor READ_ONLY for ' + @sSQL
exec(@sSQL)
|
|
|
|
|
hi,
i have a team_standing_table that is ordered by points and wins etc.
i would like to know the row number or position of a particular team.
what is the row number that has man utd? or chealsea?
(How to get the team in 1st,2nd, 12th etc i already know not the issue here)
Thanks
nelsonpaixao@yahoo.com.br
|
|
|
|
|
assuming SQL Server 2005, add the following column
ROW_NUMBER() OVER(ORDER BY Points)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT ROW_NUMBER() OVER (ORDER BY team_name ASC) AS ROWID, * from teams
where team_name like '%man utd%'
ROWID will give u the row number
chilll
remember me in your prayers
take care
FEEAMANALLAH
AHSAN SARFRAZ
UNIVERSITY OF ENGINEERING AND TECHNOLOGY TAXILA PAKISTAN
COMPUTER SYSTEM ENGINEERING DEPARTEMENT
|
|
|
|
|
For each group I wants to set an UniqueId like this.
ID UniqueId
3 1001
3 1001
4 1002
4 1002
5 1003
5 1003
6 1004
6 1004
7 1005
7 1005
How can I.
Thanks in advance.
G Nathan
|
|
|
|
|
I don't understand you!
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
|
Hi all,
I have a table containing a 4-byte int column, the largest value of which is 1354552. If I read that value into a C++ int using Carlos Antollini's ADO library here on CP, it returns -21704. The column started out as a bigint but got cut back, but none of the values in it were too big for an int.
Any ideas please?
Solved - there's already a patch on the project page. Note to self: search first, ask later.
Last modified: 40mins after originally posted --
|
|
|
|
|
Hi,
I am working in an application, where only select statement can be used. This is to create a report.
I need one column, where the data should be calculated according to a formula.
Base salary * no of months.
If the salary is same throughout the year it is base salary * 12.
If it changes in b/w, say 4 months, then it is (base salary * 4) + (new base salary * 8)
Another report has the details of the salary of all the months for that person. I can pull that column in this report. But the final report should have only one row for one person
Please help me with this.
Thanks a lot in advance...
|
|
|
|
|
Try creating a function to return the value. The performance won't be great, but it will do the job.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You can do it as a sub select. Trying to do it the way you describe is the most difficult methos and wuld require the UDF.
(Select Sum(Salary) from Table where Year = 2007) Salary
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am using virtual tables a lot (declare @my_table ...). Sometimes i have columns that accept null so inner join don´t work because doesn´t appear all values that are suposse to appear, and sometimes not either isnull() cleans that, if you know what i mean.
So, other day i got a +200 store procedure by use of virtual table. But it worked fine.
Now i have to copy a part of it to use in a bigger one.
Can anyone help me here
nelsonpaixao@yahoo.com.br
|
|
|
|
|
You want someone to do your coding? Or do you need instruction on copy paste?
I don't understand the 200+ reference (lines?)
Why don't you try breaking the proc down to sub procs. The bit you need to copy, cut it out of the original and make it produce the same results as the current code calling it as a sub proc. Then just include it in the "bigger" proc, you can also do this as a UDF that returns a table used by multiple procs.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You want someone to do your coding?
That's the OP's usual M.O.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
I did not understand the main problem but try using left join or right join to show all values.
|
|
|
|
|
that not what i asked. the sp work. just wanted a comment to know if it happens to other people as well
nelsonpaixao@yahoo.com.br
|
|
|
|
|
nelsonpaixao wrote: to know if it happens to other people as well
All the bloody time - extracting a part of a process from a large proc so it can be reused is a fundemental part of the development cycle. Identifying the bits that can be reused is where the art of development come into play.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
In c# i do this like everbody
routineA(string mystring, int myint);
routineB();
...
There is any equivalent to this in sql server?
I try to trigger some store procedures inside others but i can´t call variables inside.
create procedure C
as
begin
exec procedure B here i create a @table and fill it
...
...here i can´t do select´s or anything else
...
end
go
thanks
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Are you trying to call stored procedure from within another stored procedure?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
YES YES
I can call it but i can get acess to the values!!!!
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Hello there
I've just finished implementing an automatic DB upgrade framework in C#.
It supports finding the optimal DB upgrade path and includes hooks for adding specialized behavior if necessary (most of the time it can run on auto-pilot).
Currently it doesn't support DB schemas that contain views, triggers or fancy DB constraints (like foreign key constraints that are not actually supported in SQLite anyway) since my DB doesn't contain them and I did not have the time to implement a full SQLite DDL syntax parser.
I wondered if it will be worth the effort to publish it on codeproject.
If you are interested I'd like to hear your opinion!
Liron
|
|
|
|
|
Does anyone else get as frustrated as me trying to use Parameters with queries? How much time do you spend trying to fix "Datatype mismatch in criteria expression" or other such errors? I find these errors nearly impossible to debug and fix. There's no way I can find out which parameter is not correct.
Using Parameters is supposed to make creating complex queries neat and easy, but using Parameters has been nothing but a frustration for me.
|
|
|
|
|
TheMandolinMan wrote: How much time do you spend trying to fix "Datatype mismatch in criteria expression
None. I used stored procs and look what the data type is.
Really its just a case of getting to know your system. As a long time contractor, when I go to a new site I tend to keep an Excel doc of the main columns and their types for a quick reference - unless the site has a good current data disctionar - and believe me, not many do
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I agree with Bob, I spend 0 time working out data type, a trode procedure tells you right there at the top of the proc. I then have a code generator (internally built) that writes the stub for use in the business layer.
Use stored procs....
Never underestimate the power of human stupidity
RAH
|
|
|
|