I have a rather complicated query on my PostgreSQL database spanning 4 tables via a series of nested subqueries. However, despite the slightly tricky looking appearance and setup, ultimately it will return two columns (from the same table, if that helps the situation) based on that matching of two external parameters (two strings need to match with fields in different
Tag: stored-procedures
Add WHERE clauses to SQL dynamically / programmatically
How can I add search condition to SQL Stored Procedure programmatically? In my application(C#) I’m using stored procedure (SQL Server 2008R2) ALTER PROCEDURE [dbo].[PROC001] @userID varchar(20), @…
sp_MSforeachtable – parsing of dynamic sql
I recently found an issue whereby I wanted to use the sp_MSforeachtable stored proc to select all tables with the word Transcode in the table name, and to run some SQL on those tables. I managed to write some code which worked, but not perfectly – for those tables which I’d hoped it would gracefully skip over (i.e. those which
script issue Transact-SQL
I Want to return All table names from a use data base but this just return a char the output is s s s s s s Answer You need to define the length of @tableName, by default it is set to 1 character.
Stored Procedures vs Code in Database Query
What are the performance differences between accessing a database to query using ASP.NET Code behind against using SQL Stored Procedure For ease of use, coding the query is easier, especially when …
Update same table as selected with calculated values using resultset
I’m new to sql (scripting). Single CRUD commands are no problem, but I’m trying to create a stored procedure that wil update a table with a calculated value using the results from the same selected table. In the table there is a value with the total vacation-hours for each employee and a value of the remainder of the vaction-hours of
Best practices of structuring stored procedures
As a developer mainly writing c# I have adopted some good practices when writing c# code. When I sometimes write stored procedures I have trouble applying those practices to the stored procedure code. …
How to find which columns don’t have any data (all values are NULL)?
I have several tables in a database. I would like to find which columns (in which tables) don’t have any values (all NULL in a column). I the example below, the result should be I don’t have any idea how to create this kind of query. Your help is most appreciated! Answer For a single column, count(ColumnName) returns the number
Inserting into Oracle and retrieving the generated sequence ID
I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… The question is: What’s the best way to do that for an Oracle database? Can this be done on Oracle through standard SQL or do I have to
passing parameters to a stored procedure in C#
How can i send three parameters a to stored procedure in sql? Here is my error: Procedure or function GetIslemIdleri has too many arguments specified. This is my stored procedure: CREATE PROCEDURE …