Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure But the error occurs Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. What are my options? Is ther…
Tag: sql-server
Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
After going thru some tutorials on SQL Server 2008’s new feature “SPARSE COLUMN”, I have found that it doesn’t take any space if the column value is 0 or NULL but when there is a value, it takes 4 …
How do I ‘subtract’ sql tables?
Its not really a subtraction I’m looking for. And I know its not a union or intersection… I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the…
How can I declare a Boolean parameter in SQL statement?
How can I declare a Boolean parameter in SQL statement? Answer The same way you declare any other variable, just use the bit type: Note this is semantically different from a true boolean. The 1/0 values won’t always just map to true/false the way you might expect.
SQL, How to Concatenate results?
I currently have a SQL query that returns a number of fields. I need one f the fields to be effectively a sub query sub that. The Problem in detail: If I have a table X with two columns, ModuleID and say ModuleValue, how can I write a SQL query to take the results and Concatenate it into one field:
How to Change All Sql Columns of One DataType into Another
I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing …
How can I do an UPDATE statement with JOIN in SQL Server?
I need to update this table in SQL Server with data from its ‘parent’ table, see below: Table: sale Table: ud sale.assid contains the correct value to update ud.assid. What query will do this? I’m thinking of a join but I’m not sure if it’s possible. Answer Syntax strictly depend…
SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a ‘running total’ into a third temp table which is created using CREATE. Then this r…
SQL Server table creation date query
How can I get the table creation date of a MS SQL table using a SQL query? I could not see any table physically but I can query that particular table. Answer For 2005 up, you can use I think for 2000, you need to have enabled auditing.
How do I calculate a running total in SQL without using a cursor?
I’m leaving out all the cursor setup and the SELECT from the temp table for brevity. Basically, this code computes a running balance for all transactions per transaction. Inspired by this code from an answer to another question, I was wondering if SQL had the ability to sum numbers in the same way it…