|
Hi,
Thanks for your reply.
I will give an example
Declare @i int
Set @i=1
while @i<=7
Begin
Update set <column> + @i = value
End
In the above example, column name will be dynamic , i.e., column1, column2.
So, I want to use dynamic query for updation. As dynamic query will hit my performance,I need to avoid dynamic query.
Is there any possible way for this?
There is a datatype called table . Similar to that, whether there is any datatype called column?
Senthil
|
|
|
|
|
Certainly if your table doesn't have very much data I would look at doing this in memory. If you are using .net there is a DataTable which you can dynamically create. There are DataColumns which you can dynamically add to the dataTable. Then you can take the devaultview of the datatable and use the rowfilter to query the table's data.
In sql 2000 and 2005 there are table variables, but they only stick around for the duration of the stored procedure so you probably want it to be around a little longer then that.
If there aren't too many rows and columns I would think seriously of keeping the data in an internal table in your program.
Ben
|
|
|
|
|
I am using such type of query in a stored procedure.
My stored procedure is compiled each time when it is executed.
I am in a position to remove those dynamic queries to get my stored procedure execute faster.
I hope u understood. If not I can explain some more.
Senthil
|
|
|
|
|
Perhaps it would be better to explain exactly what you are trying to do. Why do you need a dynamic query? Why do you need to create a table? If you dynamic query returns certain columns you can just return those columns in the result set and your program can read them. The only issue because how your program knows what to do with the columns.
Ben
|
|
|
|
|
Hi, I have a form that I am using (MS Access) and it needs to be able (when clicking a command button) to take a value from a text field, alter an already created table by adding a column with that text field name, but also populate that column with checkboxes. Right now I can add the column, with the Yes/No data type, however in the table, instead of checkboxes I have to give a value of 0 (false) or -1 (true). Is it possible for SQL to do this?
-
reegan41
|
|
|
|
|
I believe the "checkbox columns" you are referring to are a "feature" in MS Access where in the table view yes/no fields are displayed as checkboxes. I imagine Access allows this because it is a user/desktop database and editing table data directly is a desired feature. SQL Server is not a user/desktop database and SQL Server administrators often go to great lengths to prevent users from opening up a table and editing it. Microsoft also recommends not doing it as well. Hence, the checkboxes will not show when you open up a table to view the data. That said, you can write an application (which could be using MS Access as a front-end) that displays that data type as a checkbox.
|
|
|
|
|
If you are making the UI for the data entry yourself, you can use binary type and use values of 0 or 1 as Yes/No.
The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.
|
|
|
|
|
I have workgroup 2005 installed and when i right click on the replication folder a messagebox comes up:
TITLE: New Publication Wizard
------------------------------
Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Management+Studio&ProdVer=9.00.3054.00&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476
////////////////////////////////////
but when i run the setup again it says replication already installed.
any ideas will be much appreciated
|
|
|
|
|
Hello,
I am using a typed dataset and have created a query the same as the one below:
SELECT TaskID, Details<br />
FROM IncidentTask<br />
WHERE Type IN (@typeArray)
I have created the methods to return a data table and fill a data table.
My table adapter is called incidentTask.
I am using the code to get all the types that the user will want to query on, as below.
'Add the support type that have been checked<br />
If (Me.chkITPhoneSupport.Checked) Then<br />
typeList.Add("'IT Phone Support'")<br />
End If<br />
If (Me.chkITRemoteSupport.Checked) Then<br />
typeList.Add("'IT Remote Support'")<br />
End If<br />
If (Me.chkITEmailSupport.Checked) Then<br />
typeList.Add("'IT Email Support'")<br />
End If
I then put all these into an array called typeArray. And attempt to fill the data table. It works ok, if i only have one item, but if i have more than 1 it won't work, as the parameter in the query is only expecting a single value, not an array.
Me.TA_IncidentTask_DSJobSheet1.FillByCritera(Me.DsJobSheet1.IncidentTask, String.Join(",", typeArray))<br />
Is there any way I can send an array to a query,
Many thanks for your suggestions,
Steve
|
|
|
|
|
|
HI
plse help me How to bind data to dataset using Views in sqlserver2005...
I encountered this problem in crystal reports I want to bind to tables and create a report for
that two tables or is there any another way help me...
came out of hardwork
|
|
|
|
|
Hi,
please can anyone let me know the date formats in sql server for this query.
my table structure is"
PDate PMonth PYear
10 05 2007
03 05 2004
12 10 2007
o1 12 2005
now i have to retrieve the records based on some specified dates.
for example, i want the records which matches "12/10/2007"
i know how to write the code for these 3 columns to format (mm/dd/yyyy)using C# & vb.Net but i want to know how to write using Tsql
Thanks,
Suketh
|
|
|
|
|
Hi
Use the date functions like Month(), Day() and Year() to get month,date and year. Based on these functions you can filter records. For example
Month(DateColumn) will display month of the given datecolumn
Hope you got it...
Harini
|
|
|
|
|
use this query
select * from t1 where pdate=day('10/12/2007') and pmonth=month('10/12/2007') and pyear=year('10/12/2007')
Note= you should format the date as 'MM/dd/yyyy'
date function returns day('10/12/2007') as 12
shally
|
|
|
|
|
set dateformat dmy
Go
select * from Table
where
Pdate=select Datepart(Day,'12/10/2007')
and PMonth=Datepart(Month,'12/10/2007')
and PYear=Datepart(Year,'12/10/2007')
|
|
|
|
|
OK, probably it is an easy answer, but I just can't get it working. I'm working on SQL SERVER 2005. Here is stored procedure:
<br />
SELECT TOP (50) *<br />
FROM h_Case<br />
WHERE ((FirstName LIKE @FirstName) AND <br />
(LastName LIKE @LastName) OR<br />
((IsNull(Aliases, '') LIKE @FirstName) AND<br />
(IsNull(Aliases, '') LIKE @LastName))) AND<br />
(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
(IsNull(PHN, '') LIKE @PHN)<br />
<br />
<br />
SELECT * FROM h_Encounter<br />
WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case<br />
WHERE ((FirstName LIKE @FirstName) AND <br />
(LastName LIKE @LastName) OR<br />
((IsNull(Aliases, '') LIKE @FirstName) AND<br />
(IsNull(Aliases, '') LIKE @LastName))) AND<br />
(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
(IsNull(PHN, '') LIKE @PHN))<br />
<br />
SELECT cn.*, u.Username AS Username FROM h_CaseNote cn<br />
INNER JOIN c_User u ON u.UserId = cn.UserId<br />
WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case<br />
WHERE ((FirstName LIKE @FirstName) AND <br />
(LastName LIKE @LastName) OR<br />
((IsNull(Aliases, '') LIKE @FirstName) AND<br />
(IsNull(Aliases, '') LIKE @LastName))) AND<br />
(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
(IsNull(PHN, '') LIKE @PHN))<br />
<br />
<br />
SELECT * FROM h_CasePDR<br />
WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case<br />
WHERE ((FirstName LIKE @FirstName) AND <br />
(LastName LIKE @LastName) OR<br />
((IsNull(Aliases, '') LIKE @FirstName) AND<br />
(IsNull(Aliases, '') LIKE @LastName))) AND<br />
(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
(IsNull(PHN, '') LIKE @PHN))<br />
<br />
<br />
RETURN<br />
So it is obvious what I'm trying to do? I just want to reuse selected cases. I tried with:
<br />
WITH C AS<br />
(<br />
SELECT TOP (50) CaseId FROM h_Case<br />
WHERE ((FirstName LIKE @FirstName) AND <br />
(LastName LIKE @LastName) OR<br />
((IsNull(Aliases, '') LIKE @FirstName) AND<br />
(IsNull(Aliases, '') LIKE @LastName))) AND<br />
(DOB BETWEEN @DobFrom AND @DobTo) AND<br />
(IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND<br />
(IsNull(PHN, '') LIKE @PHN)<br />
<br />
SELECT * FROM C<br />
<br />
<br />
SELECT * FROM h_Encounter<br />
WHERE CaseId IN (SELECT CaseId FROM C)<br />
<br />
SELECT cn.*, u.Username AS Username FROM h_CaseNote cn<br />
INNER JOIN c_User u ON u.UserId = cn.UserId<br />
WHERE CaseId IN (SELECT CaseId FROM C)<br />
<br />
SELECT * FROM h_CasePDR<br />
WHERE CaseId IN (SELECT CaseId FROM C)<br />
But C can be used just one time  So is there a way to do this over some 'table alias', or I'm destined to write this query using temporary table? If I can only do this using temporary table would how faster solution that would be over this multiple select?
Tnx in advance
|
|
|
|
|
The temporary table is most likely your best resort. The table will sit in memory waiting till the end of the procedure to be used. That being said, if you want to be lazy... You could do what I call a "String Procedure" (instead of a stored procedure). Remember that this method is very vulnerable to SQL injection/truncation attacks
DECLARE @FilterSQL AS varchar(8000)
SET @SQL = 'SELECT TOP (50) * FROM h_Case WHERE ((FirstName LIKE ''' + @FirstName + ''') AND (LastName LIKE ''' + @LastName + ''') OR ((IsNull(Aliases, '''') LIKE ''' + @FirstName + ''' AND (IsNull(Aliases, '''') LIKE ''' + @LastName + '''))) AND (DOB BETWEEN + ''' @DobFrom + '''AND ''' + @DobTo + ''') AND (IsNull(GenderId, -1) BETWEEN ' + @GenderFrom + ' AND ' + @GenderTo + ') AND
(IsNull(PHN, '''') LIKE ''' + @PHN + ''')'
Then insert the @SQL variable into your other SQL.
So use the temporary table.
Hogan
|
|
|
|
|
Would it be smarter to use Table variables in this case? I read that they interrupt parallel execution.
Thanks for your previous answer in any case!
|
|
|
|
|
Yes, I agree that table variables are the best solution here.
Hogan
|
|
|
|
|
|
I am using SQL DMO object in vbscript to connect to SQLServer2 ,
All is working well , but I want to trap error in case if connection or login fails
ex:
Dim oSQLServer : set oSQLServer = createObject("SQLDMO.SQLServer2")
oSQLserver.Connect machine,"sa"
Develop2Program & Program2Develop
|
|
|
|
|
|
What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;
i came to knw the answer as 1200 hw?
|
|
|
|
|
If places is positive, number is truncated to places decimal places. If places is negative, number is truncated on the left of the decimal point
so in ur case
the number on the left of the decimal point is 1234
-2 = Number of decimal places used for truncating
so u have to truncate 1234 for two places
that means 1200
sdf
|
|
|
|
|
I believe the following is correct:
SELECT TRUNC(1234.5678, 2) = 1234.5600
SELECT TRUNC(1234.5678, 1) = 1234.5000
SELECT TRUNC(1234.5678, 0) = 1234.0000
SELECT TRUNC(1234.5678, -1) = 1230.0000
SELECT TRUNC(1234.5678, -2) = 1200.0000
Seeing a pattern yet?
topcoderjax - Remember, Google is your friend.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
