|
Hi folks,
I got a question concerning stored procedures and I didn't find anything helpful on the comment search or via google:
I need to query a single column in a stored procedure but I want to identify this column with a VarChar-parameter, so that I can use one stored procedure for all columns in a table. Maybe there's also a way to design such a stored procedure even with whole tables. What I'm looking for is a stored procedure like this:
<br />
CREATE PROCEDURE [dbo].[GetValueFromMyTable]<br />
( @Column varchar )<br />
AS<br />
BEGIN<br />
SELECT @Column FROM MyTable <br />
END<br />
The code seems to be correct, but the statement doesn't work the way I want it to work. Does anyone have an idea about my problem?
Thanks in advance, Tobias
|
|
|
|
|
As @Column is an input parameter and you have taken varchar as data type you have to specifies the length of input parameter.
for example:-
@Column varchar(20)
I hope this will help you.
Puneet Srivastava
|
|
|
|
|
Thank you for your answer, but it still doesn't work. I only get the column name as a result-set. I think the statement awaits a column-object to identify the right column, doesn't it?! Someone got an idea?
|
|
|
|
|
try building dynamic query with the variable name
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
I know the possiblity of dynamic queries, but the problem is, that I've got to use one stored procedure per table. So I simply have to get the mapping between the name and the column. Is there a fast way to achieve this?
|
|
|
|
|
Ok, finally I made it work - here's the simplified procedure's code that's working fine:
<br />
ALTER PROCEDURE [dbo].[GetData](<br />
@Column varchar(100),<br />
@Begin datetime,<br />
@End datetime<br />
)<br />
AS<br />
BEGIN<br />
DECLARE @query nvarchar(1000)<br />
SET @query = N'SELECT ' + quotename(@Column) + ' FROM MyTable'<br />
<br />
EXEC sp_executesql @query <br />
END<br />
Thanks for your hint vimal_yet!
|
|
|
|
|
CREATE PROCEDURE [dbo].[GetValueFromMyTable]
( @ColID INT)
AS
BEGIN
SELECT ColName = CASE @ColID
WHEN @ColID=1 THEN Col1
WHEN @ColID=2 THEN Col2
ELSE col3
END,
FROM tableName
Regards,
Arun Kumar.A
|
|
|
|
|
hi,
i need a solution for a scenario.When server goes down i need to continue my work using temporary values available.how dis can be acheived does dataset hold values even wen the server goes down? if so how it works?
thnx in advance,
zari
|
|
|
|
|
You can put whatever data you like into a dataset, whether its from a database or any other source. It holds the data in memory.
This means it can be used as a cache so that if the connection to the server is lost you can carry on working. But if you have a large database it's not practical to hold the entire thing in memory. You'll also come across concurrency problems if there are multiple people using datasets and then they try to put the data back into the database when its been down.
Why would the server go down? The database should have redundancy so that it doesn't go down because its a vital part of the system.
|
|
|
|
|
After getting much help from Michael and Andy I managed to tweak this thing to meet the requirements....However, I need to group by an addition column..since I have never done it this way I have floundered for a few days before asking for help with this.
I can make it work in a different query just not this one.
I need to group by an addditonal field named:
problem_type_name in the Problem_type table which is tied to the job_ticket.problem_type_id
This worked in the other query I used and then added in the "group by"
INNER JOIN
problem_type p
ON (p.problem_type_id = j.problem_type_id)
Here is the working query that I cant add p.problem_type_name to "group by"
I have the inner join there and it will execute fine....I just need to know how to get it into the group by with location_name...I have been trying with no success...
Thanks to all who have helped thus far; I am learning and hopefully I will be able to contribute to the forum @ some point.
SELECT
Total ,
[Total Remain Open],
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id
AND
report_date between @startdate and @enddate) AS [Total Opened],
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id
AND status_type_id like '[3,4,5]'
AND
report_date between @startdate and @enddate) AS [Total Closed],
Client,
[Average days open]
FROM
(SELECT
l.location_id,
l.location_name AS 'Client',
COUNT(*) AS 'Total',
SUM(CASE
WHEN status_type_id LIKE '[1,2,6,7]' AND
report_date between @startdate and @enddate THEN 1
ELSE 0
END) AS 'Total Remain Open',
AVG(datediff(d, report_date, close_date)) AS [Average Days Open]
FROM
job_ticket j
INNER JOIN
location l
ON (l.location_id = j.location_id)
INNER JOIN
problem_type p
ON (p.problem_type_id = j.problem_type_id)
WHERE
report_date BETWEEN @startdate AND @enddate
GROUP BY
l.location_id,
l.location_name) AS Source
Regards,
Hulicat
|
|
|
|
|
I would appreciate it very much if anyone can provide me with links to websites and/or articles that can explain to me if it is better to use MySQL or SQL Server 2005 to develop my database application in. I know that SQL Server 2005 is closely integrated with the Visual Studio environment. Is MySQL closely integrated to any particular IDE? I prefer to use to SQL Server 2005, but our customers prefers MySQL and I don't know enough about MySQL to conclude which one is better and easier to develop with.
|
|
|
|
|
No, MySQL is not integrated with anything, unless the database explorer in VS2005 can find/use it.
You customers are probably victims of FUD. here are the facts:
MySQL is well behind the curve, it didn't even have stored procs 18 months or so ago
MySQL is NOT free for commercial use. Sql Server Express, is.
MySQL apparently has better clustering behaviour, so I am told.
http://www.databasejournal.com/features/mssql/article.php/3087841[^]
This looks out of date, I'm sure they support procs now. Not sure about views, triggers, etc.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
My two cents worth, I have used MySQL since SQL 2005 Express came into the limelight, and I haven't really looked back at MySQL since.
"I've seen more information on a frickin' sticky note!" - Dave Kreskowiak
|
|
|
|
|
Sql Server has a huge number of tools and features that goes with it. It brings huge ammounts of value beyond simple database functionality.
With 2005 it now also supports .Net stored procedures, triggers and datatypes, although these are for use where the traditional TSQL falls short rthaer than as a replacement.
TSQL is very nice to use and also has a mass of powerful features.
I've never personally use MySQL but I've never really heard anything good about it, all I've ever heard is developers complaining about it being broken or lacking. But then again I don't hang around in very pro-MySQL places 
I think most people use it because it'll run on Linux and isn't made by the evil empire. Personally I think the evil empire is so big because it makes some great products, especially Sql Server :P
|
|
|
|
|
Hi,
I have a column of datatype DateTime. I need to retrieve data from last 14
days old.
I am doing like this but it doesn't work.
Select reveiveddate from table1
where receiveddate = GetDate()-day(14)
seema
|
|
|
|
|
Use DATEADD
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Is there a way to configure\create a datatable (strongly typed or untyped)
that can have a row accessed in constant time if you are searching on the
primary key?
If there isn't a way, then what is the quickest way to access a row when
using datatables?
Thanks,
Ryan
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Ryan Andrus wrote: have a row accessed in constant time
What is "constant time"?
Additional: To the person that voted this a 1. Why is this not a valid question? When I read the OP's request I wondered if he meant "real time" or something else. So I asked for clarification. What is so wrong with that?!
-- modified at 19:49 Tuesday 26th June, 2007
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
O(1) in Big O notation which basically means that the amount of time to find one row in the datatable is independent of the number of rows. In other words, it will always take the same amount of time to access a row in the datatable. The same way that getting an element in an array in C++ is constant time because it is done through pointer arithmetic and not by iterating through the elements.
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Please have a look at Indexes and Clusters.
I think it will reduce the time from O(n) to O(log n).
Regards,
Arun Kumar.A
|
|
|
|
|
Are you refering to index's on the Database table? That is not what I am asking about. I am talking about System.Data.DataTable. If there is a way to index a System.Data.DataTable can someone please post up a how to?
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
I am asking about System.Data.DataTable not a table in the database. Do System.Data.DataTable's support index's?
"I'm a mushroom cloud layin motherf*cker, motherf*cker!"
- Marsellus Wallace from Pulp Fiction
|
|
|
|
|
Hi
i have 2 questions in SQL 2005
First:
How to upload a picture in the database as i want each user to put his pic
second:
How to make the search CaseSentive
so it can diffrentiate bet
bahaa & BaHaa
Thanks a lot
|
|
|
|
|
Bahaa Hany wrote: How to upload a picture in the database as i want each user to put his pic
Use a varbinary(max) column to store this. When communicating between the database and your application use parameters. The data must be sent as a byte array (byte[] in C#)
Bahaa Hany wrote: How to make the search CaseSentive
Change the collation sequence to a case sensitive (CS) one. This is one of the options at install and is the best time to make that desision because it is a PITA to change it afterwards. Also, you will have great difficulty making comparisons between columns in different collation sequences so it is best pick one and apply it across the whole server.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Have a little patience. Reposting the same question, "spamming", within 24 hours will likely get you ignored in the future.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
