I need to ALTER the data types of several columns in a table. For a single column, the following works fine: ALTER TABLE tblcommodityOHLC ALTER COLUMN CC_CommodityContractID NUMERIC(18,0) But how do I alter multiple columns in one statement? The following does not work: ALTER TABLE tblcommodityOHLC ALTER COLUMN CC_CommodityContractID NUMERIC(18,0), CM_CommodityID NUMERIC(18,0) 13 Answers 13
I have a user-defined table type. I want to check it’s existence before editing in a patch using OBJECT_ID(name, type) function. What type from the enumeration should be passed for user-defined table types? N’U’ like for user defined table doesn’t work, i.e. IF OBJECT_ID(N’MyType’, N’U’) IS NOT NULL 5 Answers 5
I am creating a stored procedure to do a search through a table. I have many different search fields, all of which are optional. Is there a way to create a stored procedure that will handle this? Let’s say I have a table with four fields: ID, FirstName, LastName and Title. I could do something … Read more
I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable. I know I can use sp_executesql but can’t find clear examples around about how to do this. 10 Answers 10
In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Code and an example value is ABCD123Stu1231. I want to remove first 4 chars from my table for all records Please guide me 12 Answers 12
I am trying to execute this query: declare @tablename varchar(50) set @tablename=”test” select * from @tablename This produces the following error: Msg 1087, Level 16, State 1, Line 5 Must declare the table variable “@tablename”. What’s the right way to have the table name populated dynamically? 10 Answers 10
I have a table and one of the columns is “Date” of type datetime. We decided to add a default constraint to that column Alter table TableName alter column dbo.TableName.Date default getutcdate() but this gives me error: Incorrect syntax near ‘.’ Does anyone see anything obviously wrong here, which I am missing (other than having … Read more
This came up at the office today. I have no plans of doing such a thing, but theoretically could you write a compiler in SQL? At first glance it appears to me to be turing complete, though extremely cumbersome for many classes of problems. If it is not turing complete, what would it require to … Read more
I have a case where using a JOIN or an IN will give me the correct results… Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using MSSQL) 6 Answers 6
A few examples to show, just incase: Inline Table Valued CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO Multi Statement Table Valued CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder … Read more
