I have a table listing people along with their date of birth (currently a nvarchar(25)) How can I convert that to a date, and then calculate their age in years? My data looks as follows I would like to see: Answer There are issues with leap year/days and the following method, see the update below: try this: O…
Tag: tsql
Determine a table’s primary key using TSQL
I’d like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?
Selecting COUNT(*) with DISTINCT
In SQL Server 2005 I have a table cm_production that lists all the code that’s been put into production. The table has a ticket_number, program_type, program_name and push_number along with some other columns. GOAL: Count all the DISTINCT program names by program type and push number. What I have so far…
SQL Server: Optional variable in a stored procedure
I would like to know if there is anyway I can set one of my stored procedure parameter as optional. Answer Providing a default value to the stored procedure parameter will make it optional. EDIT: CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ]
How to implement a do-while loop in tsql
I’m trying to figure how to implement this in TSQL The only iterative control flow sentence provided by Transact-SQL is while (condition) sentences that first evaluates the condition and if that condition is true then execute the sentence. I’m thinking in a scenario like execute a UPDATE statement…
T-SQL – Aliasing using “=” versus “as” [closed]
Closed. This question is opinion-based. It is not currently accepting answers. Want to improve this question? Update the question so it can be answered with facts and citations by editing this post. Closed 3 years ago. This post was edited and submitted for review last month and failed to reopen the post: Ori…
How to add a Try/Catch to SQL Stored Procedure
In this proc how can I handle try catch for exception? Answer See TRY…CATCH (Transact-SQL)
Counting DISTINCT over multiple columns
Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of …
Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
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…
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…