Posts

Showing posts with the label Sql Server

Can I Loop Through A Table Variable In T-SQL?

Answer : Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT. Try this: DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectCol1 int DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @RowsToProcess=@@ROWCOUNT SET @CurrentRow=0 WHILE @CurrentRow<@RowsToProcess BEGIN SET @CurrentRow=@CurrentRow+1 SELECT @SelectCol1=col1 FROM @table1 WHERE RowID=@CurrentRow --do your thing here-- END DECLARE @table1 TABLE ( idx int identity(1,1), col1 int ) DECLARE @counter int SET @counter = 1 WHILE(@counter < SELECT MAX(idx) FROM @table1) BEGIN DECLARE @colVar INT SELECT @colVar = col1 FROM @table1 WHERE idx = @counter -- Do your work here SET @counter = @counter + 1 END Believe it or not, this is actually more efficient and performant than using a cursor. ...
Post a Comment
Read more

Connect PHP To MSSQL Via PDO ODBC

Answer : There are several configuration files you need to have set up. /etc/odbc.ini , /etc/odbcinst.ini and /etc/freetds/freetds.conf (these locations are valid for Ubuntu 12.04 and probably correct for most *nixes). You'll need to install unixodbc and freetds (not sure what the package names are on CentOS). In Ubuntu this would be apt-get install unixodbc tdsodbc . For help installing these, look at this question Can't Install FreeTDS via Yum Package Manager /etc/odbc.ini (this file may be empty) # Define a connection to a Microsoft SQL server # The Description can be whatever we want it to be. # The Driver value must match what we have defined in /etc/odbcinst.ini # The Database name must be the name of the database this connection will connect to. # The ServerName is the name we defined in /etc/freetds/freetds.conf # The TDS_Version should match what we defined in /etc/freetds/freetds.conf [mssql] Description = MSSQL Server Driver = free...
Post a Comment
Read more

CREATE VIEW Must Be The Only Statement In The Batch

Answer : Just as the error says, the CREATE VIEW statement needs to be the only statement in the query batch. You have two option in this scenario, depending on the functionality you want to achieve: Place the CREATE VIEW query at the beginning CREATE VIEW showing as select tradename, unitprice, GenericFlag from Medicine; with ExpAndCheapMedicine(MostMoney, MinMoney) as ( select max(unitprice), min(unitprice) from Medicine ) , findmostexpensive(nameOfExpensive) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MostMoney ) , findCheapest(nameOfCheapest) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MinMoney ) Use GO after the CTE and before the CREATE VIEW query -- Option #2 with ExpAndCheapMedicine(MostMoney, MinMoney) as ( select max(unitprice), min(unitprice) from Medicine ) , findmostexpensive(nameOfExpensive) as ( select tradename from Medicine, ExpAndCheapMedicine ...
Post a Comment
Read more

Create Function Must Be The Only Statement In The Batch

Answer : The function needs to be either the only function in the query window OR the only statement in the batch. If there are more statements in the query window, you can make it the only one "in the batch" by surrounding it with GO's. e.g. GO CREATE FUNCTION getLavel(@id int ,@lavel char) RETURNS date BEGIN DECLARE @date date select @date = (select authorization_date from Authorized WHERE diver_number = @id and @lavel =level_name) return @date END GO Turn this into an inline table valued function. This will perform better than the scalar function. Also, you should NOT use the default sizes for character datatypes. Do you know what the default length for a char is? Did you know that it can vary based on usage? CREATE FUNCTION getLavel ( @id int , @lavel char --You need to define the length instead of the default length ) RETURNS table return select authorization_date from Authorized WHERE diver_number = @id and @lavel = level_...
Post a Comment
Read more

Add Default Value Of Datetime Field In SQL Server To A Timestamp

Image
Answer : For modifying an existing column in an existing table: ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn This can also be done through the SSMS GUI. Put your table in design view (Right click on table in object explorer-> Design ) Add a column to the table (or click on the column you want to update if it already exists) In Column Properties, enter (getdate()) in Default Value or Binding field as pictured below In that table in SQL Server, specify the default value of that column to be CURRENT_TIMESTAMP . The datatype of that column may be datetime or datetime2 . e.g. Create Table Student ( Name varchar(50), DateOfAddmission datetime default CURRENT_TIMESTAMP );
Post a Comment
Read more

CROSS JOIN Vs INNER JOIN In SQL

Image
Answer : Here is the best example of Cross Join and Inner Join. Consider the following tables TABLE : Teacher x------------------------x | TchrId | TeacherName | x----------|-------------x | T1 | Mary | | T2 | Jim | x------------------------x TABLE : Student x--------------------------------------x | StudId | TchrId | StudentName | x----------|-------------|-------------x | S1 | T1 | Vineeth | | S2 | T1 | Unni | x--------------------------------------x 1. INNER JOIN Inner join selects the rows that satisfies both the table . Consider we need to find the teachers who are class teachers and their corresponding students. In that condition, we need to apply JOIN or INNER JOIN and will Query SELECT T.TchrId,T.TeacherName,S.StudentName FROM #Teacher T INNER JOIN #Student S ON T.TchrId = S.TchrId SQL FIDDLE Result x--------------------------------------x | TchrId | TeacherName | StudentName | x----...
Post a Comment
Read more

Creating An Index On A Table Variable

Answer : The question is tagged SQL Server 2000 but for the benefit of people developing on the latest version I'll address that first. SQL Server 2014 In addition to the methods of adding constraint based indexes discussed below SQL Server 2014 also allows non unique indexes to be specified directly with inline syntax on table variable declarations. Example syntax for that is below. /*SQL Server 2014+ compatible inline index syntax*/ DECLARE @T TABLE ( C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/ C2 INT INDEX IX2 NONCLUSTERED, INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/ ); Filtered indexes and indexes with included columns can not currently be declared with this syntax however SQL Server 2016 relaxes this a bit further. From CTP 3.1 it is now possible to declare filtered indexes for table variables. By RTM it may be the case that included columns are also allowed but the current position is that they "will ...
Post a Comment
Read more